BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 06 August 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
SSRS Reports using Dynamic Data Queries
How cool would this be?
When you run the report, you can either show a list of film directors of a list of film studios.
You get different reports according to which table you select:
|
|
| If you choose to show directors | If you choose to show studios |
It turns out that this sort of one-report-serving-all effect is remarkably easy to achieve. There are 3 stages: creating the drop-down parameter, creating the dataset for the report and creating the table.
Step 1 - Creating the Dropdown Parameter
The first thing to do is to make sure that the dropdown parameter appears at the top of your report:
First add your parameter (this assumes you're using SSRS 2008 or 2012).
It's a text parameter, which we've called ReportTable (so far, so straightforward):
Give your parameter a name, and a sensible prompt.
For the list of available values to comprise the drop list, make the visible label something easy to read, but use the underlying table name for the value:
Add one possible available value for each table a user can choose.
Step 2 - Creating the Dataset for your Report
The trick to get our report working is to make sure that whether we choose directors or studios, we still have the same field names. First create a dataset:
When creating the dataset, click on the expression builder as shown.
In the expression builder, type in an expression which will evaluate to a valid SQL statement:
This will always return two fields, called IdField and NameField.
Suppose that the user chooses to show Studios. Then the dataset expression will evaluate to:
SELECT
StudioId AS IdField,
StudioName AS NameField
FROM
tblStudio
This gives the following dataset:
It doesn't matter what parameter value the user chooses, the fields in the dataset will always be the same.
Step 3 - Creating the Table for the Report
We can now create a table for the report:
A standard table, displaying two columns.
The expression for the title of the report could be something like:
The title changes according to whether directors or studios are being reported.
An Alternative Approach
Although the above is relatively easy to implement, it may run slowly (dynamic SQL can't be optimised). Perhaps a better idea would have been to create two stored procedures, and dynamically allocate these to the report dataset:
You can use an expression for the stored procedure, which would depend on the parameter value chosen.
Just a thought to finish up on!