0

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?

Alex_404
  • 399
  • 2
  • 12

2 Answers2

1

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

Wolfgang Kais
  • 4,010
  • 2
  • 10
  • 17
0

set up your matrix as per this image below: enter image description here

detailed below design view showing grouping by row/columns: enter image description here

results should be like this:

enter image description here

SuperSimmer 44
  • 964
  • 2
  • 7
  • 12
  • Thanks for the idea, unfortunately the structure of the report must be very specific, exactly how it looks on my picture :( – Alex_404 Jul 19 '18 at 10:00