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!

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!

Matrix Report and Expression on field

I have a matrix report that has two columns, and one of the colums has the following expression for background color:

=IIF( Fields!Percentile.Value >= .10, "Yellow", "White")

Basically if the percent is greater than 10 highlight the field, for some reason i have some fields that dont show up yellow, see below:

http://duhaas.googlepages.com/percent.Jpg

your colour expression needs to wrap the field reference with the same aggregate function as the textbox value expression.

So, if you are using: =sum(Fields!Percentile.Value) as your value then change the colour expression to

=IIF( SUM(Fields!Percentile.Value) >= .10, "Yellow", "White")

this is because when no aggregate function is specified the the first value returned is used by default to evaluate the expression.

|||YOU DA MAN

Matrix BackgroundColor

Hi all... again.
I am still having problems setting the backcolor of my matrix cells.
I am able to get a handle on eachcell as the rows and colums have vlaues
associated to them.
E.G.
I have four columns and 4 rows. these will never change.
The code below access the bottom left corner of the matrix and this works.
all i want to do is do 16 if statements to manually set the color.
The problem is when i go to add more if staements. the report jsut doesnt
compile.
How do i add 16 if statements. what is the syntax.
iif(( Fields!PotentialPoints.Value = 25)AND(Fields!PerfPoints.Value = 25),"RED","Yellow")
--
Thanks in advance,
Dave HuntIt may be easier to use VB Switch function.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"DustpanDave" <DustpanDave@.discussions.microsoft.com> wrote in message
news:9723C975-71A4-4379-846B-3EF59E7DB241@.microsoft.com...
> Hi all... again.
> I am still having problems setting the backcolor of my matrix cells.
> I am able to get a handle on eachcell as the rows and colums have vlaues
> associated to them.
> E.G.
> I have four columns and 4 rows. these will never change.
> The code below access the bottom left corner of the matrix and this works.
> all i want to do is do 16 if statements to manually set the color.
> The problem is when i go to add more if staements. the report jsut doesnt
> compile.
> How do i add 16 if statements. what is the syntax.
> iif(( Fields!PotentialPoints.Value = 25)AND(Fields!PerfPoints.Value => 25),"RED","Yellow")
> --
> Thanks in advance,
> Dave Hunt