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.
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.
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.
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.
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:
(where ‘DataSet1’ is the name of the dataset)
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)
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).
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).
The first page:
The second page:
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
(to hide the 0,00)
We will do something similar to the last page footer.
Just by using a visibility expression like:
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!