SSRS : Grand Total drill down in a matrix?

Question

I've got a fairly simple matrix in my report which shows some revenue figures by month and then by type of revenue, giving me a report something like this (the datasource is an MDX query) :

      +Jan   +Feb   +Mar    Total
       Total  Total  Total
Area   100    200    300    600

When I drill down into the month I get something like this :

      -Jan        +Feb   +Mar    Total
       A   B   C   Total  Total
Area   50  25  25  200    300    600

What would be incredibly useful is seeing the last column also broken down by revenue type, but I can't see a way to do this. Is it even possible?

We're using 2005, but I'd be happy to know there's a solution in a later version since it'll give me a business case for upgrading. — adhocgeek

Lamak Lamak · Accepted Answer · 2011-01-19T14:59:09

For SSRS 2005, there is no way of doing this if you are using the "subtotal" property of the matrix. You could do it if you add new records to your dataset, where you put 'Total' as a month and the corresponding summarized values for revenue type. This way, "Total" will be jus like another month and you can expand or collapse it. In SSRS 2008, you have a lot more liberties when working with matrix (in fact, this version vastly improved a lot of things, and there is no longer a matrix or a table, you have tablix). Here you can add columns inside groups or outside of them, you can add columns and later decide to group by some value, etc. So you can, actually, add a new column outside your groups, name it "Total", and make it so it groups by revenue type, and do your sum of revenue.

SSRS : Grand Total drill down in a matrix?

1 Answers