atrix components are great for visualizing data that you might normally export to Excel, or in a Pivot Table (year-over-year analysis is a good example). But if an analyst wants to view?for example?sales of a Month year-over-year, then some custom sorting has to be added so the months line up as expected. In this article I will cover the question that drives us, Neo. It?s the question that brought you here. You know the question, just as I did. Specifically, how do you get custom aggregates on a matrix report? That, and a few other advanced reporting techniques are covered so you won?t be so quick to grab for that blue pill and escape from working with a matrix component.

I remember the first time I used a Matrix component in SQL Server Reporting Services (SSRS). I right-clicked the detail row, selected Subtotal and was amazed that RS ?knew? that I wanted to sum the values of the rows. Without any need to specify a formula, I had aggregates with a simple mouse click. That amazement wore off soon after I realized just how difficult it might be to generate aggregates based on values other than a standard SUM.

Covered here are six advanced techniques for building more robust Matrix-style reports in SQL Server Reporting Services, including:

• Dynamic Dataset Creation
• Query Based Dynamic Grouping
• Dynamic Column Names
• Cells in Cells
• Custom Matrix Aggregates
• Custom Graph Colors

All of the necessary steps to build a fully functional report are included, which illustrates:

• Key concepts for dynamic dataset creation
• Custom matrix aggregates
• Custom chart coloring
• Dynamic query-based grouping with workarounds explained with the ?textbox-inside-a rectangle inside-a-textbox? technique.

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?

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

=Month(Fields(Parameters!TimeSlicer.Value).Value)

Add a new ColumnGroup called matrix1_SLICER with the following values for Group On Expression and Sorting:

=Year(Fields(Parameters!TimeSlicer.Value).Value)

	?
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.?

Technique 4: Cells in Cells
The Cells in Cells technique allows you to display more summary columns than detail. Specifically, there is one column each for quantity and dollar amount sold. But on the aggregate columns you want to show a column for the difference and the percentage respectively. To do this, you?ll add a textbox inside each of the two data level textboxes and conditionally display no data in them.

Select your first data cell, then drop a Rectangle control into it. You?ll notice the background change from solid white to the transparent grid pattern. Next, select a Textbox control and drop this into the rectangle. You have to get it perfect and sometimes it?s a bit annoying when you don?t land exactly on the control. But if you do this correctly, you should have a display similar to Figure 3.

	?
Figure 3. Cells in Cells Technique:This figure shows how your display appears when you perform the cells-in-cells technique correctly.

Next, drop in another textbox so it rests directly beside your first one. Repeat this process for the second data cell and it appears as though you have four data cells with which to work. Be aware that when you want to set properties for these cells, your familiar point, click, edit routine leaves you within the context of the Rectangle surrounding your cells. You will need to click twice to access the textboxes within the Rectangle. You may need to play with the sizing a bit depending upon the width of data contained. I named the left textbox in the first rectangle QTY_COL and the left textbox in the second rectangle LINETOT_COL so I can access their values later for percentage calculation.

Technique 5: Custom Matrix Aggregates
From left to right each of the cells should have the following expressions listed in order in Listing 4. Using the InScope() function you conditionally display either a simple sum or a custom calculation. In the first textbox you?ll notice the custom Sum that includes a conditional IIF() statement.

 Sum(IIF(Year(Fields(Parameters!TimeSlicer.Value).Value)=Parameters!Year.Value, CDbl(Fields!OrderQty.Value) ,CDbl(Fields!OrderQty.Value) * -1 )))

Rather than a blind total of all values in the row, you want to compare the Year. If it is the Year selected the value aggregates with a positive value. If however the value is from the set that belongs to the prior year (Year-1), you aggregate with a negative by multiplying by -1. Our end result is a summary that is the net effect of the two figures. Similar logic is used for the variance supplied as a percentage difference in the cells to the right of the field totals.

Since there is no variance to offer for an individual month, you simply supply a double quote ?? blank value when the column is not an aggregate.

To add some visual indication to the report I have added conditional color formatting in the aggregates with

 =IIF((Inscope("matrix1_SLICER") AND InScope("matrix1_ColumnGroup1")),"Black" ,IIF(Me.Value< 0,"Red","Green"))

On a standard column the font is black, but you?ll want to display positive movement in green and a decrease in business with red. You can do the same with fonts, styles and shading. After some additional formatting, my matrix looks like Figure 4.

Figure 4. Formatting Your Report: You can format your report by selecting fonts, styles, and shading.

?

Figure 5. Visibility Tab: You need to set the Initial Visibility to hidden and allow the toggle based upon GROUPER1.

You?ve just added a little interactivity to the display of the matrix, which is important when real estate on the page is at a premium.

Technique 6: Custom Graph Colors
To add some additional splash, you can add a chart to display the variance of sales year-over-year. Adding this chart requires the same grouping that you used on the matrix component. Add the chart above the matrix and set it to Simple Column with the same dataset as our matrix (AW_Sales).

On the Data tab, create a new Value. Then, on the Values tab use the following expression =Sum(Fields!LineTotal.Value) to display the total dollar amount of sales. Use the same expression on the Point Labels tab in the Data Label field. Be sure to check ?Show Point Labels? and format for currency (C0) so you can see the actual totals on the chart. On the Appearance tab, click the Fill tab. Set the first color to CornSilk (where do they get these color names?) and for the second color (since I am a gradient fiend) use the conditional expression:

 =IIF(Year(Fields(Parameters!TimeSlicer.Value).Value)=Parameters!Year.Value, "Gold", "Blue")

	?
Figure 6. Fill Tab: You can use the Fill tab to select distinct colors, which makes the bars much easier to read.

Using the Fill tab provides distinct colors on the report for each year, which makes the bars much easier to differentiate (Figure 6).

Next, create two Category Groups: one for the Month and the second for the Year. The Month has the Group On and Sort Expression =Month(Fields(Parameters!TimeSlicer.Value).Value), though our label wraps that expression in MonthName(), which is more user-friendly.

The Category Group for Year also uses the same expressions you used earlier in the matrix component grouping. The Group On, Sort and Label expression is set to =Year(Fields(Parameters!TimeSlicer.Value).Value).

Final Output
When you Preview the report, the parameters allow for a great deal of flexibility.

Figure 7 shows the various groupings you can select, along with multiple months and a dynamic date criteria.

Figure 8 shows a summarized view of variances only and Figure 9 illustrates what the detail looks like when exposed.

The end result is a report that behaves very much like a Pivot Table in Excel. You can extend this with additional conditional formatting or provide additional value to reports that use Analysis Services as the source for a more cube-like browsing experience in SSRS. Hopefully, this example has armed you with some new skills so you can grab the red pill and tackle those tougher matrix reporting challenges in the future.

Figure 7. Previewing the Report: You can see the various groupings selected.

?

Figure 8. Previewing the Report: You can view a summarized view of variances.

?

Figure 9. Previewing the Report: You can view what the details look like as shown in this figure.