Wednesday, March 21, 2012

Matrix Subtotal but not all colums

Hello,

I have this problem that I do not now how to solve it:

I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).

If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.

Nr. Of Orders Nr. of Lines Average Delivery Time

Open Order 10 20 3.2

Closed Order 15 30 5

Total 25 50 8.2 (it should be, for example, 4.5)

Is there a way in which I can tell the system to not calculate the total for the third column?

If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.

Thanks to all!

Andrea

You would need to use the InScope RS function in the matrix cell's expression to determine if the cell is neither in the row group's nor in the column group's scope (i.e. the overall total cell).

Please search this forum for threads related to the InScope function.

-- Robert

|||

Robert, as the subtotal is on the rows, and the subtotal cell appears in a column calculated in the dataset, then surely in this case he does want to be in scope of the column group?

Is there an expression that will help determine what the current column is? That could then be applied to the visibility. Something like for Visibility->Hidden:

= (Not InScope("row_group")) And (Fields!Measure_Name.Value = "Average Delivery Time")

|||

I am playing a little bit with the inscope functions but it doesn't seem to work. How can I change the scope of the subtotal that is placed automatically by RS?

Basically all my columns are in the row group scope and I just need the subtotals to act differently.


Thanks again for your help!

No comments:

Post a Comment