locked
SSRS 2008 - Expression for totals in a Matrix RRS feed

  • Question

  • Hi,

    I have a simple Matrix with the following groups:

    Row Group: Program

    Column Group: Employee

    I need to add a new total column on the right side of the matrix using a simple calculation but I haven't been able to find how to do that other than doing it on the query side. Here's how the Matrix looks like:

    Program Employee1 Employee2 Employee3 New Column
    Program1 45.0% 0.0% 87.5%
    Program2 12.5% 50.0% 3.8%
    Program3 28.8% 1.3% 8.7%
    Program4 1.3% 23.8% 0.0%
    Total 87.6% 75.1% 100.0%

    The new column should display the sum of each program divided by the sum of all totals....for example for Program1 the calculation should be (45+0+87.5)/(87.6+75.1+100) which should be equal 50.44% ...the same logic applies for the other rows.

    The number of columns (Employees) and rows (Programs) are dynamic, so they change according to who is seeing the report. I'm unable to reference the sum of the total at the bottom row, not sure what I am missing.

    Any input on this is greatly appreciated.

    Thursday, April 9, 2015 8:41 PM

Answers

  • Hi Cleber,

    I have tested on my local environment and your issue can be caused by you haven't include the scope in the sum function, please find details information below about how to do the calculation:

    1. Please design the matrix like below:
    2. You can find the Row Group name is "Program" and the Column Group name is "Empolyee", this will include in the sum expression as below:
      Expression1: =SUM(Fields!Amount.Value,"Empolyee")
      Expression2: =SUM(Fields!Amount.Value,"Program")
      Expression2: =Sum(Fields!Amount.Value,"Program")/Sum(Fields!Amount.Value,"DataSet1")
    3. Preview you will got the result like below:

    If you still have any problem, please feel free to ask.

    Regards,
    Vicky Liu

    If you have any feedback on our support, please click here.


    Vicky Liu
    TechNet Community Support


    • Marked as answer by Cleber75 Monday, April 13, 2015 1:09 PM
    Friday, April 10, 2015 4:59 AM
  • Hi Cleber75,

    Per my understanding that you want sum the total of "50.44%+25.24%+14.77%+9.55%" to get "100%" display in the report, right?

    If so, you can modify the expression like below to get the correct result:
    =Sum(Fields!Amount.Value)/Sum(Fields!Amount.Value,"DataSet1")

    Preview:

    If I have some misunderstanding, please try to provide more details information about which field you want to calculate and what is the expect result you want to get.

    Regards,
    Vicky Liu


    Vicky Liu
    TechNet Community Support


    • Proposed as answer by Visakh16MVP Monday, April 13, 2015 4:55 AM
    • Marked as answer by Vicky_Liu Tuesday, April 14, 2015 12:47 AM
    Monday, April 13, 2015 2:33 AM

All replies

  • Hi Cleber,

    I have tested on my local environment and your issue can be caused by you haven't include the scope in the sum function, please find details information below about how to do the calculation:

    1. Please design the matrix like below:
    2. You can find the Row Group name is "Program" and the Column Group name is "Empolyee", this will include in the sum expression as below:
      Expression1: =SUM(Fields!Amount.Value,"Empolyee")
      Expression2: =SUM(Fields!Amount.Value,"Program")
      Expression2: =Sum(Fields!Amount.Value,"Program")/Sum(Fields!Amount.Value,"DataSet1")
    3. Preview you will got the result like below:

    If you still have any problem, please feel free to ask.

    Regards,
    Vicky Liu

    If you have any feedback on our support, please click here.


    Vicky Liu
    TechNet Community Support


    • Marked as answer by Cleber75 Monday, April 13, 2015 1:09 PM
    Friday, April 10, 2015 4:59 AM
  • That is great, thank you Vicky. I was missing the proper scope reference pointing to the the dataset.

    Just one more question, I need to add the total below it, just to show it's adding to 100%...I tried adding the total but it does not like the Program scope ref on that cell, how would I do that?

    Regards,

    Cleber

    Friday, April 10, 2015 12:46 PM
  • Hi Cleber75,

    Per my understanding that you want sum the total of "50.44%+25.24%+14.77%+9.55%" to get "100%" display in the report, right?

    If so, you can modify the expression like below to get the correct result:
    =Sum(Fields!Amount.Value)/Sum(Fields!Amount.Value,"DataSet1")

    Preview:

    If I have some misunderstanding, please try to provide more details information about which field you want to calculate and what is the expect result you want to get.

    Regards,
    Vicky Liu


    Vicky Liu
    TechNet Community Support


    • Proposed as answer by Visakh16MVP Monday, April 13, 2015 4:55 AM
    • Marked as answer by Vicky_Liu Tuesday, April 14, 2015 12:47 AM
    Monday, April 13, 2015 2:33 AM
  • That did the trick, thanks for all your help Vicky.
    Monday, April 13, 2015 1:09 PM