SSRS: How to show the Grand total row at the top of the first page only in a table

A Grand total row can be added to a table at either bottom or top. When the row is added to the bottom of a table, it appears on the last page of the table. However when it is added to the top of a table, it not only appears on the first page, but also appears on the every subsequent page as shown in below screen shot, which sometimes is not desirable.

Fig-1: Grand total row on every page

As shown in the above screen shot, the Grand total row appears on the every page of the same table. This is because property 'RepeatOnNewPage' is true. However, it won’t work if we simply change the value of the property to false to prevent the Grand total row from appearing on the subsequent pages. This is because we want the header to appear on every page, which requires the property ‘RepeatOnNewPage’ for the header to be true. Then due to SSRS limitation that SSRS requires all adjacent static members must share same value for 'RepeatOnNewPage' property, this property has to be true for the Grand total row as well. If we try to change the property to false, SSRS would complain as shown in the below screen shot.

Fig-2: SSRS requiring same value for ‘RepeatOnNewPage’ property.

Because the 'RepeatOnNewPage' property for the header is true SSRS expects that the property for Grand Total is true as well.

One solution is to use Matrix instead of Table per a MSDN forum thread. Here presents another solution but with a table instead of a Matrix.

Firstly add another header row below the Grand total row. So there are two exactly same header rows, one header row above the Grand total row, and the other header row below the Grand total row as shown below. Note that the header row below the Grand total belongs to State group.

Fig-3: Two header rows

Secondly, configure the 'RepeatOnNewPage' for two header rows and the Grand total row as false. Therefore, the header row above the Grand total and the Grand total row appear on the first page only, while the header row below the Grand total shows on the every page because it belongs to the State group.

Finally, hide the header row in the State group for the first page. It can be achieved by setting an expression for the Hidden property for both the Tablix member and all the cells for this header row. The expression can be '=RunningValue(Fields!State.Value, CountDistinct, Nothing) = 1', where State is group field. For the first State group, the expression would be true because there is only one group. While for all subsequent groups distinct count would return a value greater than 1, and the expression would be false. Therefore, the header row inside the group would appear on all subsequent pages but not the first page. The following two screen shots show how to configure the Hidden property for both the Tablix member and all the cells inside the header row.

Fig-4: Configure the Hidden property for the Tablix member with the Tablix member and the Hidden property highlighted.

Fig-5: Configure the Hidden property for all the cells inside the header row with all the cells inside the header row and the Hidden property highlighted.

The below screen shot shows the final result where the Grand total row appears only on the first page.