Group Page Totals and Carried Forward Totals

Question

I have googled a lot about this but found nothing useful. This has been asked before here but noone has responded.

I have a tablix that presents grouped data with an amount column:

Tablix (data)
             Name (group)
                         Amount (rows)

If the "Name" group spans more than one page I want to show a page total for the sum of the amounts present on the first page. On the next page the carried forward total of the previous page must be shown.

I have tried many things, especially with my custom assembly (like keeping a dictionary with the total of each group and in the footer of the report try to show the total etc). The thing that really messes up any implementation is the order where report's header, body and footer are "executed".

Any ideas or suggestions?

Angelos Bousias Angelos Bousias · Accepted Answer · 2013-01-08T11:18:04

Due to:

The fact that there is no way to get whether one instance of the grouped data are split in more than one page at the detail level (so that the carried forward total can be shown below the repeated column headers)
The page header and footer are always "evaluated" after the body elements
You can only reference on ReportItem in the expressions used in the page header and footer
You cannot make changes to the body elements from the page header or footer (mostly because of the point above)
You cannot extend the tablix control to add your own functionality

I decided to go with showing the group page total in the footer, storing it and the picking it up to show it to the next page.

This is done by having a cumulative total column using

= IIf(RunningValue(Fields!AmountDC.Value, Sum, "Group1") <> Sum(Fields!Amount.Value, "Group1"), code.SetTotal(RunningValue(Fields!Amount.Value, Sum, "Group1")), Nothing)

which for the last detail row is setting it to "Nothing"

In the footer a textbox picks up the last value of that cumulative total field.

= code.SetTotal(Last(ReportItems!CumTotal.Value))

and stores the current total in a private field in the embedded code.

Private running As Double = 0

Public Function SetTotal(ByVal val As Double) As Double
    running = val
    Return val
End Function

Public Function GetTotal() As Double
    Return running
End Function

And last in the page footer the stored total is picked up

= code.GetTotal()

It is too simple, but still it is the closest I could get with what I have after many trials!

Group Page Totals and Carried Forward Totals

2 Answers