Matrix reports in Reporting Services 2005

Matrix reports are extremely useful when you have data organized in rows that you want to transpose to columns (similar to creating a Pivot Table in Excel). Here’s an example of how to use a Matrix report and how to hack your way through some additional features you’re likely to need.

The Matrix
Say you have a dataset of user transactions, with {Username, TransactionDate, Amount}. Instead of seeing this information as a long table, you want to see one row per user, with the total amount spent per day in columns. This would let you quickly compare who spent what by day.

Doing this as a Matrix report is easy. Select “Add New Report” from Visual Studio 2005, enter your query or sproc, and pick ‘Matrix’ report type. The next page is where all the magic happens; using our example, put {Username} into ‘Rows’, {TransactionDate} into ‘Columns’, and {Adjustment} into ‘Details’. Click ‘Finish’, and you’ll see that Visual Studio has created the table, assuming you wanted to SUM the Amount for each user. In this case that is exactly what we want, but if we needed a different aggregate operation we’d just edit the details Textbox and change ‘Sum()’ as desired.

The first thing you’ll notice when you preview the report is that this isn’t exactly what we want, since the TransactionDate column is using the full DateTime value, which creates a column for each distinct DateTime. Instead we just want a single column per unique day. So let’s add a calculated field by going to the ‘Data’ tab, clicking the ‘…’ button, and adding a new calculated field TransactionDateFormatted with value =Format(Fields!TransactionDate.Value, "g"). We then have to change the report to use this new field in two places:

1. In In the textbox displaying the date. Right-click on the column header textbox and change the Value from =Fields!TransactionDateFormatted.Value to =Fields!TransactionDateFormatted.Value.
   


2. In the grouping definition. Right-click on the table, select ‘Properties’, go to the ‘Groups’ tab, edit the column grouping definition that was automatically created, and change the expression to =Fields!TransactionDateFormatted.Value.
   

Viola! All is good.

Unplug from the Matrix
Well, almost. And here’s the part you’ve been waiting for, where we take off the gloves, agree that Reporting Services 2005 does do lots of things well, and that for what it is, we like it. Thing is, sometimes to get the job done you have to get a little dirty.

Additional Columns
Let’s say we have the same dataset, but this time we also have UserId, and we want to show both the Username and the UserId in each row. Sounds straightforward enough. But you’ll notice that this isn’t so simple, since you can’t add a ‘Column’, only a ‘ColumnGroup’, which would add a column for each unique UserId (the exact opposite of what we want). You have to tilt your head to the side a bit and think like the Matrix (report). What we want here is actually another ‘RowGroup’, since the UserId value will appear in a row, but we want to group by a constant value so as not to create a second group for each user. So right-click on the table, choose ‘Add Row Group’, and enter =1 as the grouping expression. Then on the report, change the newly created Textbox’s value from =1 to =Fields!UserId.Value. Taking a look in Preview mode, we see that we now have Username and UserId in each row, and we’re happy.

Column Headers
Kind of. What we really need now is a column header for each of these columns, since it’s a bit unclear what’s what. So we return the Layout tab and go to edit the value of the column header Textbox… but alas! There’s only one, merged Textbox for the two columns. You’ll see that if you add additional RowGroups using the same technique there is still just a single, merged Textbox for the entire span of the column header.

Time to bust out the heavy guns. Open the Toolbox and drag a Rectangle into that single, merged Textbox. You’ll see the dots in the background of the report now. Then drag two Textboxes onto the Rectangle, and position them so that one is above the first column (Username) and the other is above the second column (UserId). Adjust the widths of the Textboxes so that they exactly match the widths of the RowGroups, edit the Textboxes’ values to be “Username” and “UserId”, and go back to preview mode. Now we’re cooking.

Enter Chris Hays
Almost. Now what we say is “What’d be really great here is zebra-striping”, so that we can easily see each row. But you’ll notice that the traditional technique of zebra-striping a reporting services report (setting the background color to =iif(RunningValue(Fields!UserId.Value,CountDistinct, Nothing) Mod 2, "WhiteSmoke", "Ivory")) just doesn’t work. This time tilt your head to the other side, and keep tilting. For this hack we turn to Chris Hays and his Reporting Services Sleazy Hacks Weblog, which is far and away the best (only?) source of “How-Do-I”’s on Reporting Services 2005. The blog is from 2004-2006, but it’s relevance hasn’t changed. Read through everything he’s written.

Conveniently, Chris has a Green Bar Matrix posting that will step us through this (yes, it’s going to get real ugly). Following his instructions:

1. Add another RowGroup (at the innermost position, after the UserId RowGroup we added) with a constant (=1) grouping expression. Change the name of the newly created RowGroup’s TextBox to "ColorNameTextbox".
   


2. Put the background color calculation (=iif(RunningValue(Cstr(Fields!UserId.Value), CountDistinct, Nothing) Mod 2, "WhiteSmoke", "Ivory")) into the Value property of ColorNameTextbox.
   


3. Set the BackgroundColor property of this entire new RowGroup to =Value.
   


4. Set the BackgroundColor property of the matrix data cell Textbox (the one that does the Sum()) to =ReportItems!ColorNameTextbox.Value.
   


5. Set the BackgroundColor property of the other RowGroups (for UserId and Username) to the same grouping expression that we used for the Value of ColorNameTextbox (=iif(RunningValue(Cstr(Fields!UserId.Value), CountDistinct, Nothing) Mod 2, "WhiteSmoke", "Ivory")).
   


6. Hide the newly created RowGroup by setting the FontColor to =Value, the FontSize to 1pt, and the Size to Width of 0px. You can also hide the borders as Chris suggests if you mind them.
   

Go Wash Your Hands
Phew! We now have exactly what we wanted, and we only had to feel a little bit dirty about getting it. It’s not deal-with-the-devil kind of stuff, but still, it’s pretty nasty.

But that’s OK. We love Reporting Services. Come back to reality (if you remember what that is) and plug back into the Matrix. Life is good.