Reports that show financial data often require a carried forward amount, or per-page cumulative total, or running total, to show in the header or footer of the report. Reporting Services doesn't have a good (well described) way of handling this, until now. I'm going to lead you through a demonstration on how to make it really work, and some of the pitfalls (and the workarounds) I found along the way.

You can download the completed demo project (for SSRS 2008) here: Reporting Services Carried Forward Demo.zip

Getting Started

First we'll need to arrange some fake data that is going to fill the report. Here's some T-SQL which will generate a nice long list of rows containing the numbers 1 to 500.

; With Numbers As (
	Select  1 As FakeNumber
	Union All
	Select  1 + FakeNumber
	From    Numbers
	Where  	FakeNumber <= 500
	) 
Select	FakeNumber
From	Numbers
Option	(Maxrecursion 500)

Now to prepare the demo report.

  • Use the above T-SQL as a dataset.
  • Add a table, name a cell in the detail section to be named FakeNumber and give it this expression: =Fields!FakeNumber.Value
  • Add a footer through the menu system, Report -> Add Page Footer, then add a textbox with the text "ReportItem Total" and another textbox beside it with this expression:=Sum(ReportItems!FakeNumber.Value)

You'll end up with a layout like this:

And if you preview the report and go to the print layout view, it will look like this:

At the moment it's only showing a page total, where we want this to accumulate the total between pages.

First Attempt

The first thing to try is creating a custom code section with a shared variable that accumulates the current page total and displays the total amount thus far. Let's see how that works in practice continuing on from above:

  • Add some custom code to the report:

     ``` vbnet Dim Shared runningTotal As Decimal = 0

    Public Shared Function Accumulate1(ByRef value As Decimal) As Decimal runningTotal += value Return runningTotal End Function ```

  • Now add another textbox in the footer with the text "Accumulate1 Total", and another textbox beside it with this expression =Code.Accumulate1(Sum(ReportItems!FakeNumber.Value))

Now preview it, switch to print layout (if it isn't already), and hit refresh; it will look great! Page up and down, skip to the end, everything tallies correctly with a grand total of 125,751:

Now switch out of print layout mode and scroll to the last page:

Uh oh, 140,177? It will get even worse if you scroll up and down the page. So hit refresh, which will refresh everything back to normal numbering, then go back to PDF view and go to the last page.

126,654? That's right, this method sucks. The reason being that the shared variable in your custom code section doesn't get reinitialised between mode switches in the report, and if you put it on a production server and run the report in a couple instances at the same time, you'll find it's shared between all the reports and makes for some funky subtotals.

Final Attempt

My solution is to create a dictionary of documents, each with a dictionary of pages and totals. As we get to each new page, we add it to the dictionary; and if it already exists then we overwrite it with the current page total (to accommodate mode switches like above). Finally, we return the total of all the pages for this document up to the current page. It's easy once you see it:

  • Add some custom code to the report:

    Dim Shared runningTotals As New Collections.Generic.Dictionary(Of String, Collections.Generic.Dictionary(Of Integer, Decimal))

  Public Shared Function AddTotal(ByVal documentNumber As String, ByVal pageNumber As Integer, ByVal pageTotal As Decimal) As Decimal
  Dim pageDictionary As New Collections.Generic.Dictionary(Of Integer, Decimal)
  If runningTotals.ContainsKey(documentNumber) Then
      pageDictionary = runningTotals.Item(documentNumber)
  Else
      runningTotals.Add(documentNumber, pageDictionary)
  End If

  If Not pageDictionary.ContainsKey(pageNumber) Then
      pageDictionary.Add(pageNumber, pageTotal)
  Else
      ' We reset it here, for HTML->PDF switches
      pageDictionary.Item(pageNumber) = pageTotal
  End If

  Dim total As Decimal = 0.00
  For i As Integer = 1 To pageNumber
      If pageDictionary.ContainsKey(i) Then
          total += pageDictionary.Item(i)
              Else
                      ' We've probably jumped to the end of an HTML report
                      Return Nothing
      End If
  Next i

  Return total
End Function

  • For our demonstration, we'll also need a unique document number, so I've put one into the query; you could just as easily use an invoice number, or create a report variable to NEWID() and use that for a per-report generated document number.

    If you had multiple document numbers per report, it would likely require you to store the document number on each report page, then pass in a ReportItems object to the below function, and directly access the fields inside there, seeing as you can only otherwise reference a single ReportItem in each header/footer expression.

  • Now add two textboxes in the footer, one with the text, "AddTotal Total" and the other with this expression: =Code.AddTotal(First(Fields!DocumentNumber.Value, "Dummy"), Globals!PageNumber, Sum(ReportItems!FakeNumber.Value))

The layout will look like this:

You could remove the older ReportTotal and Accumulate1 textboxes, but I left them in for comparison. And now everything seems to work, though after a lot of testing there are some specific situations where it will break; so here's the list:

  • Print layout view is always correct no matter what. You can go up and down, switch to non-print layout view and back, and it will still always be correct.
  • Non-print layout view will always be correct if you go through the pages sequentially, either forwards or backwards.
  • The "Last Page" button in non-print layout view will be correct if you've gone through the pages sequentially at least once. Otherwise:
    • If you haven't generated a print layout view, it will show nothing.
    • Otherwise, it will show an incorrect total.

There only way around the incorrect total is to integrate Globals!RenderMode as part of your DocumentName key, but that's only available in SSRS 2008R2. An alternative in SSRS 2008 is to integrate Globals!TotalPages which will prevent the error happening, but only if the print and non print views have a different total number of pages, or the non print view is longer than the print view.

Otherwise, it seems stable enough for production use. I have considered that the dictionary might eventually become too large for memory if it is never reinitialised, and I couldn't work out if it ever is, but I did stress test with millions of documents and didn't notice any increased memory usage. If it bothers you, you could add a time stamp and remove bits from memory after a certain number of days.