Mutliple Subtotal Rows -- sql server reporting services

humanpuck NO[at]SPAM gmail.com
6/28/2007 7:53:15 AM

Hi folks,

I've been searching for a way to get mulitple sub total rows. Here is
the requirement.

I have a report that looks like this

DataCol
Emp1
Org 1
Expense1 3
Expense2 5
Expense3 6
Org 2
Expense1 1
Expense2 7
Expense3 2
Emp1 Total 24

What I need to add is Totals at the org level.

DataCol
Emp1
Org 1
Expense1 3
Expense2 5
Expense3 6
Org 1 Total 14
Org 2
Expense1 1
Expense2 7
Expense3 2
Org 2 Total 10
Emp1 Total 24

The matrix looks like this

rowgroup1 Emp
Rowgroup2 Org
Rowgroup3 Expense
ColumnGroup 1 Period
Data is Amount

Thanks in advance.

Parag

EMartinez
6/29/2007 7:55:49 PM

[quoted text, click to view]

You can approach this one of a few different ways.
- You can try to group on RowGroup2 and set a report subtotal.
- You can do the subtotaling as part of the query/stored procedure
that sources the report (normally via a cursor or while loop to loop
through the individual groups)
Also, if the first option gives you subtotals for everything, you can
filter out the other groups w/an expression similar to:
=iif(Fields!SomeFieldName.Value Like "Org*", subtotal(...), Nothing)
Hope this helps.

Regards,

Enrique Martinez
Sr. Software Consultant

sql server reporting services : Mutliple Subtotal Rows