SSRS ..Problem with SubTotal and Calculating Percentage
Group, I am having problem to calculating % on base of subtotal value in Matrix Report. R1 % R2 % GrandTot % --------------------------------------------------------------- A 1 20 40% 10 50% 30 34% 2 30 60% 10 50% 40 66% ---------------------------------------------------------- Total 50 100% 20 100% 60 100% ----------------------------------------------------------- B 1 20 33% 10 20% 30 30% 2 10 16% 10 20% 20 20% 3 30 51% 30 60% 50 50% ---------------------------------------------------------- Total 60 100% 50 100% 100 100% ---------------------------------------------------------------- The Bold % i need to calculate. I need help to calculate the % column. I believe if someone can let me know how can i get the total for each group in the detail column than it should be easy for me In Advance thanks for help SandipSP
May 8th, 2009 12:08am

Any Comment please?SP
Free Windows Admin Tool Kit Click here and download it now
May 8th, 2009 4:41pm

Hi, What is the version of Reporting Services you are using? I assume you are using Reporting Services 2005, Because this issue could be simply solved in Reporting Services 2008. OK, Suppose the below one is the matrix structure: <ColumnGroup> Data %GrandTot % ---------------------------------------------------------------<RowGroup1> <RowGroup2> detailColumn1detailColumn2 ---------------------------------------------------------- Total<Group2> You need the inscope() function. You can set the cell expression of detailColumn1 to be: =sum(Fields!data.Value) Set the cell expression of detailColumn2 to be: =switch( inscope("<RowGroup1Name>") and inscope("<ColumnGroupName>"), cstr(sum(Fields!data.Value)/sum(Fields!data.Value, "<ColumnGroupName>")*100)+%, inscope("<RowGroup1Name>") and inscope("datasetName"), cstr(sum(Fields!data.Value)/sum(Fields!data.Value, "<RowGroup1Name>")*100)+% ) For better understand the scope in a matrix, see: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/0bde66d7-b0e4-40da-9a0c-838663e66119 Also, please install the latest Server Pack, otherwise, the scope will not return correct result.Server Pack 3 Hope this helps. Raymond
May 12th, 2009 11:16am

Thanks Raymond, I am using the SSRS 2005. I got the solution with following code =Iif(InScope("CloumnGroup"), Iif(InScope("RowGroup"), IIF((Sum(Fields!OrderDetail.Value))is nothing ,"0%",(Sum(Fields!OrderDetail.Value)/Fields!Runtot.Valu e)), IIF((Sum(Fields!OrderDetail.Value))is nothing ,"0%",(Sum(Fields!OrderDetail.Value)/Fields!Runtot.Value ))), Iif(InScope("RowGroup"), "9999", IIF((Sum(Fields!OrderDetail.Value))is nothing ,"0%", Sum(Fields!OrderDetail.Value)/Sum(Fields!OrderDetail.Value,"matrix1_ProcessName")))) and it solve my purpose, but here for getting the subtotal I am using the store proc calcucation and return to report with field name Runtot. Thanks for you response i will try in different way and also switch statement as i would like to gave less burden to store proc Good work. SandipSP
Free Windows Admin Tool Kit Click here and download it now
May 12th, 2009 9:14pm

Hi, What is the version of Reporting Services you are using? I assume you are using Reporting Services 2005, Because this issue could be simply solved in Reporting Services 2008. OK, Suppose the below one is the matrix structure: <ColumnGroup> Data %GrandTot % ---------------------------------------------------------------<RowGroup1> <RowGroup2> detailColumn1detailColumn2 ---------------------------------------------------------- Total<Group2> You need the inscope() function. You can set the cell expression of detailColumn1 to be: =sum(Fields!data.Value) Set the cell expression of detailColumn2 to be: =switch( inscope("<RowGroup1Name>") and inscope("<ColumnGroupName>"), cstr(sum(Fields!data.Value)/sum(Fields!data.Value, "<ColumnGroupName>")*100)+%, inscope("<RowGroup1Name>") and inscope("datasetName"), cstr(sum(Fields!data.Value)/sum(Fields!data.Value, "<RowGroup1Name>")*100)+% ) For better understand the scope in a matrix, see: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/0bde66d7-b0e4-40da-9a0c-838663e66119 Also, please install the latest Server Pack, otherwise, the scope will not return correct result.Server Pack 3 Hope this helps. Raymond Great!!! Thanks Raymond .It worked!!!
May 27th, 2009 5:45pm

hi ,I have similar kind of scenario. A B C Total 2 5 7 14 3 2 1 6 Total: 5(5*100/20%) 7(35%) 8(40%) 20 can you give me idea for total and percentage function expression ?i dont have any group on column or rows.can you help me out to get 5(25%) ,7(35%)Thank you in advance..PPatel
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2010 11:53pm

i dont have any group on column or rows.can you help me out to get 5(25%) ,7(35%) Without column group, how could you get the total column at the right side? Anyway, you can try the below expression:=cstr(sum(Fields!data.Value)/sum(Fields!data.Value, "<datasetName>")*100)+”%”Hope this helps,Raymond
February 18th, 2010 9:14am

I have a similar situation. I am using SSRS 2008 and have the following matrix within a Parent group: Provider Count % 1 342 2 1,119 3 1,198 4 11,904 5 30 6 217 7 1,364 Total 16,174 I'm trying to get the percentage for each provider based on the Total. I have tried using the following formulas with no luck: =(Fields!COUNT.Value)/Sum(Fields!COUNT.Value) =Count(Fields!COUNT.Value)/Sum(Fields!COUNT.Value) Any help on this would be greatly appreciated.
Free Windows Admin Tool Kit Click here and download it now
October 18th, 2010 6:46pm

Hi Raymond, Thank you very much, I am able to resolve the issue with your code block. thanks SanjaySanjay
December 23rd, 2010 4:01am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics