Search code examples
reporting-servicescrystal-reports-2010

Displaying Dataset Query and Report Expressions in SSRS Report

I am switching from Crystal Reports to SSRS Reporting. In Crystal Reports, there was a way to display the Selection Criteria at the end of each report. This displayed all of the data manipulations made by the developer to the report data. Example:(this would be displayed at the end of each report in Crystal)

Selection Criteria: usp.Members.MemberID <>2 and usp.Members.Active = "Y" and if(usp.Members.Location = "M1" then "Admission Location" else " ")

In SSRS I am able to manipulate the data in a number of places; Query Builder in the Dataset Properties, Textbox/Field Expressions, and via Filters.

Is there any any that I can display all the report expressions I have made, or display the query used to generate the dataset, at the end of the report to show the user the logic behind the data?

I understand what I am asking could be difficult to understand. Any help is appreciated.

Solution

  • You can view the Dataset query using an expression like:

    =DataSets!DataSet1.CommandText

    Where DataSet1 is the name of your Dataset.

    To give an example, say I have a report with the query:

    enter image description here

    I have a simple report based on this:

    enter image description here

    You can see the textbox expression is the same as above:

    enter image description here

    The expression is showing an error but this works anyway:

    enter image description here

    See What DataSet Query Text Is Executed for more information.

    Not sure on extracting all expressions from the report, but hopefully this will be enough.

    Edit after comment

    OP mentions that it is only working for him in the report header, but in my example this is working in the body.

    From Using Built-in Collections in Expressions it seems like there are some further restrictions here:

    Represents the collection of datasets referenced from the body of a report definition. Does not include data sources used only in page headers or page footers. Not available in local preview.

    Which explains why it was working for me - it was also being used in a table.

    As a simple workaround you could simply add a hidden textbox somewhere on the report body that references the required Dataset; this should allow .CommandText to work in the body.