Wednesday, March 21, 2012

reporting services matrix subtotal

I have a matrix and have used the switch function to select the measure group that is displayed (based on selecting a report parameter). All works fine except the subtotal only shows the first row data.

e.g.

2005

2006

2007

Sales

Sales

Sales

a

2

2

2

b

4

4

4

c

6

6

6

TOTAL

2

2

2

The switch function used to select the measure is:

switch(Parameters!Measure.Value="Gross Sales",Fields!GrossSale.Value,Parameters!Measure.Value="Net Sales",Fields!NetSale.Value,Parameters!Measure.Value="Sales Quantity",Fields!SaleQuantity.Value,Parameters!Measure.Value="Sales Rebate",Fields!SalesRebate.Value)

Any idea why this would happen?

Thanks

Looks like you're not aggregating the applicable measure - how about trying this modified cell expression:

switch(Parameters!Measure.Value="Gross Sales",Aggregate(Fields!GrossSale.Value),Parameters!Measure.Value="Net Sales",Aggregate(Fields!NetSale.Value),Parameters!Measure.Value="Sales Quantity",Aggregate(Fields!SaleQuantity.Value),Parameters!Measure.Value="Sales Rebate",Aggregate(Fields!SalesRebate.Value))

|||

Deepak,

Thanks for your help.

all is fine now.

No comments:

Post a Comment