Show total from column group as row

Question

Im trying to get a row with the total for a column group with SSRS. This is my report:

+-----------+---------------------------+--------------------------+--------------+
|           |        John Doe           |       Brody Shaffer      | Kerry Morrow |
+-----------+-------------+-------------+-------------+------------+--------------+
|    Date   |  Project 1  |   Project 2 |  Project 3  | Project 5  |   Project 4  |
|  1/1/2020 |  $ 2.512,20 |  $ 3.230,00 |         $ 0 |        $ 0 |    $ 200,00  |
|  2/1/2020 |         $ 0 |         $ 0 | $ 35.504,00 | $ 5.200,30 |  $ 3.400,00  |
|  5/1/2020 |  $ 6.640,90 |    $ 987,00 |  $ 1.879,00 |        $ 0 |         $ 0  |
| 15/1/2020 |    $ 650,50 |         $ 0 |         $ 0 |        $ 0 |         $ 0  |
.
.
.

I want this row:

.
.
.
+-----------+-------------+-------------+-------------+------------+--------------+
| Total     |       $ 14.020,6          |        $ 42.583,3        |  $ 3.600,00  | 
+-----------+---------------------------+--------------------------+--------------+

Rows sample:

+---------------+-----------+-----------+-------------+
|   Customer    |    Date   | Project   |    Budget   |
+---------------+-----------+-----------+-------------+
|   John Doe    |  1/1/2020 | Project 1 |  $ 2.512,20 |
|   John Doe    |  1/1/2020 | Project 2 |  $ 3.230,00 |
|   John Doe    |  5/1/2020 | Project 1 |  $ 6.640,90 |
|   John Doe    |  5/1/2020 | Project 2 |    $ 987,00 |
|   John Doe    | 15/1/2020 | Project 1 |    $ 650,50 |
| Brody Shaffer |  2/1/2020 | Project 3 | $ 35.504,50 |
| Brody Shaffer |  2/1/2020 | Project 5 |  $ 5.200,30 |
| Brody Shaffer |  5/1/2020 | Project 3 |  $ 1.879,00 |
| Kerry Morrow  |  1/1/2020 | Project 4 |    $ 200,00 |
| Kerry Morrow  |  2/1/2020 | Project 4 |  $ 3.400,00 |
+---------------+-----------+-----------+-------------+

This is my row group:

Date

This is my column group:

Customer
  Project

When i add a "total" for the "Customer group", visual studio add a new column, but i need a new row with the total data.

Is this possible?

Alan Schofield Alan Schofield · Accepted Answer · 2020-01-28T17:06:30

I don't think it's possible to get exactly what you want because, as far as I know, you cannot merge cells across column groups. However, we can get close.

There are two ways to do this.

Option 1: This is simplest but means the totals appear between the Customer Name and the project name. T do this simply right click the "Customer name" cell and do "Insert Row => Inside Row - Below". Set expression simply to

=Sum(Fields!Budget.Value)

Option 2: This is a little more work but gets closer to your desired output.

First, right-click a cell in your detail row and do "Insert Row => Outside group - Below" In the new row, set the cell expression to

=Sum(Fields!Budget.Value, "Customer")

We include the scope "Customer" so all values within the customer column group are summed, if you omit this scope then it would only aggregate within each project.

If you run the report you'll see that you have duplicate numbers but we only want to show one. We can solve this by checking if the project column matches the first project column within the customer group.

Change the cell expression from what we just set to this..

=IIF(
    FIRST(Fields!Project.Value) = FIRST(Fields!Project.Value, "Customer"),
    Sum(Fields!Budget.Value, "Customer"),
    Nothing
    )

Now we only get the value shown once but there is a visible line between the cells , we can solve this in a similar way.

In the cell properties, leave the BorderStyle - Default as "Solid" but change the BorderStyle - Left to "None". Next set the BorderStyle - Right property to the following expression.

=IIF(
    FIRST(Fields!Project.Value) = FIRST(Fields!Project.Value, "Customer"),
    "None",
    "Solid"
    )

What this does is check if the project matches the first project within the customer (i.e. the left column) and sets the border style accordingly.

The final output should be what you want except the Sum budget value will look offset to the left.

I'm not sure if there is better solution though...

Here's the final design and output with both Options 1 and 2 shown.

Design view

Output

Show total from column group as row

2 Answers