Matrix Report Calculation Issue.

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.
MannyHi 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.
Manny wrote:
>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
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200612/1|||Can you give us a sample on how you accomplished this please?
Thanks,
Manny
pmak wrote:
>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|||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
Manny123 wrote:
>Can you give us a sample on how you accomplished this please?
>Thanks,
>Manny
>>Hi Manny:
>[quoted text clipped - 7 lines]
>>Manny
--
Message posted via http://www.sqlmonster.com