Technique 2: Query Based Dynamic Grouping
In this report, there are two levels of grouping that users can select so the matrix behaves in similar fashion to a Pivot
Table in Excel. Users can group their analysis by Color
, or SalesPerson
, vice versa, or by any combination of fields you allow. To provide this functionality you can simply create a list of field values in a new parameter. For this example however, I’ll illustrate using a query. It’s partly because I am lazy and don’t want to enter the same value list twice and partly to illustrate that the dynamic grouping can be served up from a query or stored procedure.
OK, it’s mostly because I’m lazy. Like any developer I’d rather code than copy and paste. Anyway, I’ve used a simple UNION
query (Listing 3
) to list the fields I want to provide, but you can build on this example by storing the values in a table for a truly scalable solution.
Two parameters are then created and appropriately called Grouper1 and Grouper2 with this dataset (named what else? …Groupers) as their source for valid values.
On the Layout tab, add a new matrix component and tie it to the AW_Sales dataset. Access the Matrix Properties dialog
and you’ll see that a Row Group and Column Group were created by default. Rename the default RowGroup to
matrix1_Grouper1, set the Group On Expression to =Fields(Parameters!Grouper1.Value.Value.
Then click the Sorting tab and use the same expression.
Create a new RowGroup, called matrix1_Grouper2 and set the Group On Expression and Sorting to
Technique 3: Dynamic Column Names
For the default ColumnGroup, set the Group On Expression and Sorting to
Add a new ColumnGroup
with the following values for Group On Expression and Sorting:
Figure 2. A Simple Matrix:
The Report Layout tab displays a simple matrix.|
Back on the Report Layout tab you’ll see a fairly simple matrix that should look like
In the highlighted cell in Figure 2
, change the name to GROUPER1
Then enter the expression
so the grouping value is shown.
Then right click each cell other than “Data
” and select Subtotal.
Right click the Data cell and select ‘Add Column.’ Above the cell with the Year() calculation shown, add the following
expression: =MonthName(Month(Fields(Parameters!TimeSlicer.Value).Value)), which displays a
user friendly name for Month rather than the number.
For the cells directly below the Year() calculation you want to show the quantity sold as well as the amount (LineTotal). For the individual months, totals will suffice, but on the summary columns you want to show the variance. You’ll need to dynamically change the column head so users understand the data displayed beneath. In the cell on the left use the expression:
=IIF((Inscope("matrix1_SLICER") AND InScope("matrix1_ColumnGroup1")),"Order Qty","Qty Variance")
and for the cell on the right use
=IIF((Inscope("matrix1_SLICER") AND InScope("matrix1_ColumnGroup1")),"Line Total","Line Variance").
The SSRS function InScope()
checks for the relative positioning of the data as it is rendered. You should use standard column names like “Order Qty” or “Line Total” unless the summary groups are not in scope, meaning this is an aggregate column being rendered. In this case you’ll display the verbiage “Variance.”