Menu
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.
815
In Design view, click the text box on the design surface to which you want to add an expression. For a simple expression, type the display text for the expression in the text box. For example, for the dataset field Sales, type [Sales] . For a complex expression, right-click the text box, and select Expression.
To view the SQL statement for a reportSelect the report for which you want to view the SQL statement and then, in the Home tab, in the Report Group group, select Edit. The Report Builder window opens. In the Report Data pane, expand Datasets to view the data sets for the report.
To provide available values for a parameter from a dataset In the Report Data pane, right-click the parameter @StoreID, then click Parameter Properties. Click Available Values, and then click Get values from a query.
To create an embedded dataset, select the source of data and specify a query. After you create the dataset, use the Report Data pane to view the field collection. You can display data from a dataset in a data region like a table or chart.
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.
102
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
