Matrix Report Calculation Issue.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Manny - 22 Dec 2006 05:43 GMT

Hi There,

I have this matrix report that shows sales for last couple of years and is
grouping on 5 different regions and separate years on different columns, I
want to calculate the % Sales for each region in another column. how do I
accomplish this in Reporting Services? I think the static cell on a matrix
don't allow this, I have been trying to somehow call the single cell name
from a Expression but no luck. I think this could be accomplish with some
code, if it is, can you please give some samples or direct me to a place
where I could research this topic a bit more?

any advices would be really appriaciated.

Thanks very much in advance.

Manny

Reply to this Message

pmak - 22 Dec 2006 18:52 GMT

Hi Manny:

There is a way to achieve this using "IIF" and "InScope" function. I did it
on a Matrix Report and it will dynamically calculate the percentage based the
Row/Column grouping.

>Hi There,
>
[quoted text clipped - 12 lines]
>
>Manny

Reply to this Message

Manny123 - 27 Dec 2006 17:25 GMT

Can you give us a sample on how you accomplished this please?

Thanks,

Manny

>Hi Manny:
>
[quoted text clipped - 7 lines]
>>
>>Manny

Reply to this Message

pmak - 27 Dec 2006 22:53 GMT

Here is the example:

IIF(InScope("matrix1_RowGroup1"), IIF(InScope("matrix1_ColumnGroup1"),Sum
(Fields!Funding.Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup1"),Sum
(Fields!Funding.Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup2")),IIF
(InScope("matrix1_Program"),IIF(InScope("matrix1_ColumnGroup1"),Sum(Fields!
Funding.Value,"matrix1_Program")/Sum(Fields!Funding.Value,
"matrix1_ColumnGroup1"),Sum(Fields!Funding.Value,"matrix1_Program")/Sum
(Fields!Funding.Value,"matrix1_ColumnGroup2")),""))IIF(InScope
("matrix1_RowGroup2"), IIF(InScope("matrix1_ColumnGroup1"),Sum(Fields!Funding.
Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup1"),Sum(Fields!Funding.
Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup2")),IIF(InScope
("matrix1_RowGroup3"),IIF(InScope("matrix1_ColumnGroup1"),Sum(Fields!Funding.
Value,"matrix1_Program")/Sum(Fields!Funding.Value,"matrix1_ColumnGroup1"),Sum
(Fields!Funding.Value,"matrix1_Program")/Sum(Fields!Funding.Value,
"matrix1_ColumnGroup2")),""))

Paul

>Can you give us a sample on how you accomplished this please?
>
[quoted text clipped - 7 lines]
>>>
>>>Manny

Reply to this Message