Advanced Matrix Reporting Techniques : Page 2

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 named AW_Sales, 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, OrderDate or ShipDate. 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 and Year for user selection. Month has static values (1 for January, 2 for February, etc.) but allows for multiple selections. Year 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 and Year. 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?

Previous Page

Next Page