SSRS 2012 Show Row Group Total in Column

1
votes

Is there a way to merge cells vertically so that a Row Group's Total can be shown in the column? Here is how I want my report to look:

+-------+--------+--------+---------+--------------+--------------+
|Year   | Half   | Qty    | Amount  | Year Ttl Qty | Year Ttl Amt |
+-------+--------+--------+---------+--------------+--------------+
|       | 1st    | 500    |  2,000  |              |              |
| 2012  +--------+--------+---------+    1,200     +     5,500    +
|       | 2nd    | 700    |  3,500  |              |              |
+-------+--------+--------+---------+--------------+--------------+

What I have done so far is create a Table with Year and Half in Row Groups. Half is a child to Year in the Row Group. I have added two more columns after Amount - Year Ttl Qty and Year Ttl Amount. Both these are calculative columns. I have used an aggregate sum with a group on "Year" to calculate the Year Ttl Qty:

=Sum(Fields!Qty.Value, "Year")

This got me the right value but it repeated for both the 1st and 2nd Half rows. So I went ahead and set the Hide Duplicate parameter to "Year" Row Group which shows blank in the 2nd row. Like the following:

+-------+--------+--------+---------+--------------+--------------+
|Year   | Half   | Qty    | Amount  | Year Ttl Qty | Year Ttl Amt |
+-------+--------+--------+---------+--------------+--------------+
|       | 1st    | 500    |  2,000  |     1,200    |     5,500    |
| 2012  +--------+--------+---------+--------------+--------------+
|       | 2nd    | 700    |  3,500  |              |              |
+-------+--------+--------+---------+--------------+--------------+

However, this still doesn't get me my desired output.

I have seen a very similar post at SSRS - show row total in column. However, the answer there doesn't really help me.

EDIT (12thOct'14)

The nearest I have come to a solution is this:

In the 2nd grid, I have used expressions to draw the top and bottom border to give the impression of merged cells. For the borders of the Year Ttl Qty cell, I have used these expressions:

Top: =iif(Previous(Fields!Year.Value) =Fields!Year.Value, "None", "Solid") Bottom: =iif(Count(Fields!Qty.Value,"DataSet1") = RowNumber("DataSet1"), "Solid","None") 

 +-------+--------+--------+---------+--------------+--------------+
 |Year   | Half   | Qty    | Amount  | Year Ttl Qty | Year Ttl Amt |
 +-------+--------+--------+---------+--------------+--------------+
 |       | 1st    | 500    |  2,000  |     1,200    |     5,500    |
 | 2012  +--------+--------+---------+              +              +
 |       | 2nd    | 700    |  3,500  |              |              |
 +-------+--------+--------+---------+--------------+--------------+

Unfortunately, this is still a hack in my opinion :( For example, in this scheme, I cannot vertically center the Year Ttl Qty across 2 cells.

1
reporting-servicesssrs-tablixssrs-groupingreportbuilder3.0ssrs-2012

1 Answers

0
votes

There are several ways to achieve what you want. You can bottom align the Year ttl Qty and Yeat Ttl Amt and remove the bottom cell padding to get the value as central as you can to at least come close but you are right it feels "hacky".

The better option providing you don't mind your totals to come first is detailed below but if you absolutely have to have your totals last then you can achieve similar results by nesting tables inside each other.

Assuming your data is something like this:

Dataset

then set up a table with row groups for Year and Half like this:

DesignView

and your results will be like this:

Preview