Search code examples
reporting-servicesreportingssrs-2012ssrs-2008-r2ssrs-tablix

SSRS - Adding total row + percentage of the total

I have two types of fruits - bananas and apples. My report shows (using matrix) how many of each type certain people have, using this test query:

SELECT 1 AS fruits, 1 AS bananas, 0 AS apples, 'person_1' AS people UNION ALL
SELECT 1 AS fruits, 0 AS bananas, 1 AS apples, 'person_2' AS people

I want to remove the "fruits" field and add automatic calculation of the total of the two types on the row above. Also, add the % of the total for each fruit type, i.e. how big part of the total amount of bananas each person has.

enter image description here

I suspect this is done somehow by grouping the rows but I cant figure out which groups I need to use on rows. I don't have an obvious group by column.

Any ideas?

Solution

  • First of all, the data usually comes in a format like this:

    SELECT 'person_1' AS people, 'bananas' AS FruitType, 1 AS FruitCount
UNION ALL
SELECT 'person_2' AS people, 'apples' AS FruitType, 1 AS FruitCount

    FruitAndPeople

    Using a Dataset like this, build a report with a matrix that shows FruitTypes in rows, people in columns and FruitCount (the sum of it) as data. This will automatically create a FruitType row group and a people column group:

    Basic Matrix

    Right-click both groups under Row Groups and Column Groups to add a total to each. For the FruitType group select Add Total Before and for the people group select Add Total After:

    Matrix with Totals

    Rename the cells I selected in the last screenshot to TotalByPerson (the total in the [people] column) and TotalByFruitType (the total in the [FruitType] row). Also, rename the total in the Total row and Total column to GrandTotal. This will make formulas for percentages more readable.

    Now, to add a row for the percentage, right click on the row selector of the [FruitType] row to insert a row inside the group below the selected row:

    Adding the Percentage Row

    You can split the merged cells to enter a separated title for the percentage row, for example [FruitType] %:

    Percentage Row Title

    Now, there are just two formulas missing that you can define in the respective Expression window:

    • in the [people] column: =ReportItems!FruitCount.Value/ReportItems!TotalByPerson.Value
    • in the Total column : =ReportItems!TotalByFruitType.Value/ReportItems!GrandTotal.Value

    Format both values as a percentage using the Text Box Properties window. After defining the background colors of the rows as desired, your design window should look like this:

    Designer of Final Report

    Running the report, you will receive the following result from the test data above:

    enter image description here