SSRS Carry forward previous page and running totals in header and footer

Introduction
Recently I ran into the ‘problem’ of SSRS not being able to easily show running totals in the footer AND header of a invoice. (carry forward amount).
After some digging and experimenting, I created a solution that worked for me.

Version and database
We are running a SQL 2012 server with SSRS 2012 on a Microsoft Dynamics AX 2009 database.

Dataset
The dataset that we will use is rather simple:

SELECT 
	l.SALESID
	,l.ITEMID
	,convert(decimal(10,2),l.LINEAMOUNT) AS LINEAMOUNT
FROM AXDB_2009_PRD..SALESLINE l
WHERE 
	l.CONFIRMEDDLV = '2015-01-02'
ORDER BY
	l.SALESID
	,l.ITEMID

Returning all salesline rows from a given date with salesid and itemid.

001Dataset

Goal
We would like to have a page header, showing a ‘carry forward amount’ (from all previous pages) AND a running total in the page footer from this and previous pages.


Report design

002Design

The design is pretty straightforward with a header and footer (to add them, click Report in the menu bar and Add Page Header / Footer)
There is one tablix with three visible columns.

  • Salesid
  • Itemid
  • Lineamount

I’ve also added a total, by right clicking on the details group below and choose Add Total.

Next there are four textboxes that have some expressions that I would like to explain.

Hidden fields (1 and 2)
Fields 1 and 2 are in the tablix itself and will be hidden in the final report.
The first one is for the calculation of the actual ‘running total’ for the footer.
I’ve given this textbos the name ‘RunningTotal’ by right clicking on the textbox and selecting Text Box Properties.
The expression was set to:
=RunningValue(Fields!LINEAMOUNT.Value,SUM,”DataSet1″)
(where ‘DataSet1’ is the name of the dataset)

003Field1

The second field in the tablix (also to be hidden) calculates the ‘previous’ running total that will be used in the header.
This one was given the name ‘RunningTotalHeader’ and has the expression:
=RunningValue(Fields!LINEAMOUNT.Value,SUM,”DataSet1″) – Fields!LINEAMOUNT.Value.

Therfor the first row will show the value of the running total from the previous page. (more on that later)

Footer (3)
In the footer I’ve added a textbox with the expression:
=”Carry forward: ” +cstr(Last(ReportItems!RunningTotal.Value))

This will tell SSRS to select the last row (of that page) and get the value from the textbox with the name RunningTotal (nr 1).

Header (4)
Now a textbox was added in the header with the expression:
=”Carry forward: ” + cstr(First(ReportItems!RunningTotalHeader.Value))

This will tell SSRS to select the first row (of that page) and get the value from the textbox with the name RunningTotalHeader (nr 2).

Example
The first page:
004PreviewP1

The second page:
005PreviewP2

Notice the carry forward in the header has the same value as the footer had from the previous page (just how we wanted).


Hiding header / footer fields on first and last page
Of course we will set the visibility of the header to ‘hidden’ on the first page by selecting the Text Box Properties

=iif(Globals!PageNumber=1,True,False)

006HeaderVisible
(to hide the 0,00)

We will do something similar to the last page footer.
Just by using a visibility expression like:
=iif(Globals!PageNumber=Globals!OverallTotalPages,True,False)

Conclusion
As with most of the SSRS challenges, using some creativity can solve many problems.
This one worked perfectly for me, but if you have more to add, just reply to this post and I will try to help!

9 comments:

  1. Thanks for your post.
    I have a question (problem) and I hope that you can help me.
    I want to set the text box (3,4) in to the grid not let it on the header and footer like you.
    So it raise and error and can not deploy.

  2. Excellent solution, so I’m complicated, since in my case I must show the accumulated amount of the previous page in the first row in the region of the body, some idea how to do it considering I do not let me use fields of aggregation in the body

    1. Thanks!
      What do you mean by ‘previous page’?
      Are there more pages before the ‘previous page’?

      Lets say you have
      – Page 1 = total € 100,-
      – Page 2 = total € 100,-
      – Page 3 = total € 100,-

      Do you want the running total on the first row in page 3 be € 200,- or € 300,-?

      Peter

        1. Ok, should be possible with a similar solution.
          Just create a running Total like I did and refer to it with reportitems!…. in the first row of a table. I will give it a try tomorrow if it won’t work.

  3. Greetings Friend, I hope you achieve the goal given it is not possible to use aggregation functions in the body of the report, Thanks in advance

Leave a Reply

Your email address will not be published. Required fields are marked *