This week I am switching gears to talk about a cool design technique for the Reporting Services matrix.

At least it is cool to me J and hopefully it will be helpful to you.

In some design scenarios, you may require two “subtotals” for the same group of data that point to different database fields. This is a little tricky with the matrix given that there is one subtotal per matrix group. You can, of course, use the InScope function to apply different subtotal expressions to various groups, but in this scenario, InScope is not immediately helpful because different subtotals are needed for the same group.

I have mocked up a very simplified version of a business scenario where this can occur:

The business requirements behind the table are as follows:

·         The Count of Completed and Planned Events is a database field containing the total count of the completed and planned events by month. From a business perspective, they are combined together to calculate the overall event workload.

·         The Quarter Totals and Grand Total are normal subtotals of the database field by quarter and year.

·         Now the interesting requirement. The YTD Total should only display the total for the Year-To-Date completed events. The completed events measure is actually a different database field that stores completed events by month.

So put simply I need a Grand Total that sums one data field and I need a YTD total that sums a different field.

·         To interpret the example data in the table, assume that Today is February 15^th. In this scenario, the YTD Total includes the January events (10) and the competed February events as of February 14^th (2). The YTD total is 12. There are 3 other remaining events in February.

To solve this problem, I thought of a few different ways to do this on the database side and even considered reverting to a table, but the most appealing to me was to create a dummy group so that I could use InScope J

So the requirement above ended up can be implemented with the design as below.

1. Create Matrix Column Group 1 On Months
2. Create Matrix Column Group 2 On Quarters
3. Add in normal subtotals for Month and Quarter to get the Quarterly Total and Grand Total

Here is the trick

1. Create Matrix Column Group 3 on Years – This is the dummy group. Even though there is only one year in the table, creating this extra group allows you to use InScope to apply a different expression to each group subtotal.
2. Add in a Subtotal for Year to create a placeholder for the YTD Total and then use the Position property to place the subtotal before the data detail.

For the Data Expression, you can use the InScope expression as follows:

=IIF(InScope("matrix1_GroupYear"), Sum(Fields!TotalCount.Value),Sum(Fields!YTDCount.Value))

So InScope was handy once again with a little bit of extra work to get there!

Hope this helps you in your next matrix report.