Technique 1: Dynamic Dataset Creation
Start with a new report based on AdventureWorks that will show sales after some flexible user inputs. For the new dataset
, I’ve joined several tables using the graphical query builder in SSRS. In this example the user is allowed to select a date filter based on any of the following fields: DueDate
. I’ve called this input parameter TimeSlicer
, and the RDL fragment is shown in Listing 1
In addition to selecting the WHERE
clause field for our filter, there are two additional parameters for Month
for user selection. Month
has static values (1 for January, 2 for February, etc.) but allows for multiple selections.
is a list of the years 2002, 2003, and 2004.
Figure 1. Unformatted SQL Query:
This figure shows the SQL Query in all its unformatted glory.|
Microsoft's TechNet cautions on the use of dynamic queries. The salient points are to create your query first without
the dynamic components using the query designer in SSRS, then refresh your field list, switch to the Generic Query Designer and modify your SQL as needed. Any carriage returns or formatting created by the query designer must then be removed so the SQL statement appears as one continuous string
(see Figure 1
). Finally, do not
refresh your field
list for this dataset! SSRS will not be able to parse your query and you will lose the field definitions created
previously. If this happens to you, you’ll either have to manually create the field list again or revert back to your standard query to refresh those fields. You’ve been warned Neo!
The dynamic dataset used is found in Listing 2, but the relevant
portion is the WHERE clause:
WHERE Month(" & Parameters!TimeSlicer.Value & ") IN (" & Join(Parameters!Month.Value,",") & ")" & " AND
Year(" & Parameters!TimeSlicer.Value & ") IN (" & Parameters!Year.Value & "," & Parameters!Year.Value -1 & ")"
For the TimeSlicer
selected by the user (ShipDate
for example), investigate the Month
. Records are only returned if they are in the multiselect parameter for Month
selection and in the Year
chosen, or in the year prior. This is a comparison report, remember?