none
Report Builder 3.0 - Matrix - Create Column with Percentages based on Totals Column RRS feed

  • Question

  • I have created a Matrix in Report Builder 3.0 for a SharePoint list.  It works great.

    The matrix includes data grouped by months and grouped by the number of Yes and No answers to a single question.

    Now I would like to add a column that calculates the percentage of the total based on the number of Yes answers.

    The matrix looks like this:

    Question       Yes    No    Total

    Jan                5       5       10

    Feb                10    10       20

    March             15    10       25

     

    I have a added a new column title Percentage and have tried several different expressions and I keep getting errors.

     

    I would like the matrix to look like this:

    Question       Yes    No    Total     Percentage

    Jan                5       5       10           50%

    Feb                10    10       20          50%

    March             15    10       25          60%

     

    The percentage is the Number of Yes answers divided by the Total.

     

    I have search online and cannot find the solution for us.  I thought it would be simple but Report Builder is proving not to be so easy to figure out.  I use Excel Pivot tables all the time and can easily create this in Excel. 

     

    Friday, July 15, 2011 2:19 AM

Answers

  • Hi NikkiZ,

    How do you add total column to the matrix? I think this is the same to add a new column besides the total column. Please take the following steps:

    1. Right click the total column, select “Insert Column”
    2. Select “Right”

    If you see group column, please select "Outside Group-Right"

    If you have any question, please feel free to ask.

    Thanks,
    Eileen




    • Marked as answer by Eileen Zhao Saturday, July 30, 2011 2:56 PM
    Tuesday, July 26, 2011 9:54 AM
  • Hi Analyst Kim,

    It seems that you would like to calculate the percentage of the total based on the number of Yes answers.

    If that’s the case, I suggest you can use expression in Matrix to reach your goal, please refer to the following steps and then check if it works for you.

    1. Add a new column besides the Total column, and name it as Percentage.
    2. Right-click the blank textbox and select Expression.
    3. Type in the expression like following: =Sum(Iif(Fields!FiledsName.Value=”Yes”,Fields!Number.Value,0))/sum(Fields!Number.Value)
    Note: please replace FieldsName and Number with your own fields name.

    If you have any question, please feel free to ask.

    Thanks,
    Eileen

    • Marked as answer by Eileen Zhao Saturday, July 30, 2011 2:56 PM
    Tuesday, July 19, 2011 10:02 AM

All replies

  • Hi

    I've just resolved a similar problem in Visual Studio whereby I had created a matrix with several columns and wanted to show each column as an overall percentage of each row.

    It wasn't in sharepoint, but it may give you an idea of where to look to help you resolve this issue?

    Basically, you have to tell the formula which set of data you want to add together to use as the number to do the divison by:

    For example:

              |Column1| Column 2 | Column 3 |

    Row 1  |     10    |     10       |     10       |

    Row 2  |     10    |      10      |     20       |

    If we assume we want to add the numbers in row 1 and show them as a percentage, we would expect to see 33% across all columns.  In row 2, we would expect to see 25%, 25% and 50%.  The formula to do this is:

    Sum(Field)/Sum(Field,"RowGroupName")

     

    It works in Visual Studio in a report matrix - hope it helps point you in the right direction for Sharepoint?

    Regards

    Kaz

    Friday, July 15, 2011 10:27 AM
  • Hi Analyst Kim,

    It seems that you would like to calculate the percentage of the total based on the number of Yes answers.

    If that’s the case, I suggest you can use expression in Matrix to reach your goal, please refer to the following steps and then check if it works for you.

    1. Add a new column besides the Total column, and name it as Percentage.
    2. Right-click the blank textbox and select Expression.
    3. Type in the expression like following: =Sum(Iif(Fields!FiledsName.Value=”Yes”,Fields!Number.Value,0))/sum(Fields!Number.Value)
    Note: please replace FieldsName and Number with your own fields name.

    If you have any question, please feel free to ask.

    Thanks,
    Eileen

    • Marked as answer by Eileen Zhao Saturday, July 30, 2011 2:56 PM
    Tuesday, July 19, 2011 10:02 AM
  • Hello Eileen -

    I'm trying what you suggested and cannot get passed the first step.  I can't add a plain old new column.  It keeps wanting me to add another group column.  What's the secret there?

     

    thanks - NikkiZ

    Friday, July 22, 2011 5:32 PM
  • Hi NikkiZ,

    How do you add total column to the matrix? I think this is the same to add a new column besides the total column. Please take the following steps:

    1. Right click the total column, select “Insert Column”
    2. Select “Right”

    If you see group column, please select "Outside Group-Right"

    If you have any question, please feel free to ask.

    Thanks,
    Eileen




    • Marked as answer by Eileen Zhao Saturday, July 30, 2011 2:56 PM
    Tuesday, July 26, 2011 9:54 AM
  • hi

    i use a tablix to genarate my report but i need one field to show percent of row data see example:

                      c1     p1       c2       p2      c3     p3      

    1               22      %22   35      %35    44    %43

          a         12      %29   15       %36   14    %34

          b          10      %16   20      %34   30    %50

    2

           a

           b

           c

    3

            a

    How to create this report ?  i created this without percentage col



    • Edited by rmajidi Monday, January 6, 2014 6:13 AM
    Monday, January 6, 2014 6:02 AM
  • Hi Analyst Kim,

    It seems that you would like to calculate the percentage of the total based on the number of Yes answers.

    If that’s the case, I suggest you can use expression in Matrix to reach your goal, please refer to the following steps and then check if it works for you.

    1. Add a new column besides the Total column, and name it as Percentage.
    2. Right-click the blank textbox and select Expression.
    3. Type in the expression like following: =Sum(Iif(Fields!FiledsName.Value=”Yes”,Fields!Number.Value,0))/sum(Fields!Number.Value)
    Note: please replace FieldsName and Number with your own fields name.

    If you have any question, please feel free to ask.

    Thanks,
    Eileen

    please explain more about "Number " which field you mean?
    Monday, January 6, 2014 7:22 AM
  • Hi, I am new to Microsoft Report Builder 1.0. I have problems when generating matrix.  It is tough to find information on report builder 1.0 matrix. Would anyone who can offer me some information on it or can help me solve the issue?

    Thanks in advance. 

     
    Monday, August 4, 2014 3:02 AM