Displaying Dataset Query and Report Expressions in SSRS Report

Question

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.

Instance

Answer 1

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:

I have a simple report based on this:

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

The expression is showing an error but this works anyway:

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.

Finn

Thank you. This did work, but only when I inserted the textbox and expression into the Header. Is it possible to display this inside the report body, after a tablix item to show the query on the last page of the report? – Instance

Hmm, you can see it worked for me - I've added some suggestions about why this might happen and a suggested workaround. – Finn