RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Advanced Matrix Reporting Techniques : Page 3

Learn how to build robust Matrix-style reports in SQL Server Reporting Services. These advanced reporting techniques make it easier to build a fully-functional report.

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 then Size, or SalesPerson and State, 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 =Fields(Parameters!Grouper2.Value.Value.

Technique 3: Dynamic Column Names
For the default ColumnGroup, set the Group On Expression and Sorting to

Add a new ColumnGroup called matrix1_SLICER 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 Figure 2. In the highlighted cell in Figure 2, change the name to GROUPER1. Then enter the expression =Fields(Parameters!Grouper1.Value).Value) 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.”

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date