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:

[code language=”sql” gutter=”false”]
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
[/code]

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!

15 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. Author

      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. Author

          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


  5. Hi, Thanks for the post. It really helped me.
    now my report looks perfectly alright but when I try to save into PDF.. throwing some unknown exception
    please see below exception.

    [ReportServerException: An internal error occurred on the report server. See the error log for more details. (rsInternalError)]
    Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +541
    Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +1236
    Microsoft.Reporting.WebForms.ServerReport.Render(String format, String deviceInfo, NameValueCollection urlAccessParameters, String& mimeType, String& fileNameExtension) +84
    Microsoft.Reporting.WebForms.ServerModeSession.RenderReport(String format, Boolean allowInternalRenderers, String deviceInfo, NameValueCollection additionalParams, Boolean cacheSecondaryStreamsForHtml, String& mimeType, String& fileExtension) +127
    Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +154
    Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +216
    System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +341
    System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +69

    need your kind help to solve this. Thanks in advance

    1. Author

      Ok, what version of SSRS are you running?

  6. Thanks a lot for providing solution. On the similar line I got report where I have to show and carry forward total in footer and header respectively, but number of columns are around 23. Now for that as per your way do I have to add 23*2=46 hidden columns more in the tablix? Is there any shortest way as my report is itself horizontally big that is A3 paper size report.

    1. Author

      I think that is one solution indeed.
      Maybe you can also use some formula’s that make for the same values (like column income – column expenses = column result)
      But you might also wonder why you need 23 columns on one report….
      Nobody reads that amount of information, let alone expect totals per page (adding even more numbers to the mix)
      But then again, maybe I’m too much used to KISS….

  7. Hi, I am having an issue, I can export the report in pdf as long the column / fields 1 and 2 are visible , but if I hide these columns, I get error when I try export in pdf as follows (Please note : the report displays on the screen correctly but throw error at the time of export only. I am using Dynamics 365 update 22 and Visual studio 2015).

    There is a problem with the server
    Sorry, the server has encountered an error. It is either not available or it can’t respond at this time. Please contact your system administrator.
    500 Error

    1. Author

      Hi Tahseen,
      That one is hard to reproduce for me.
      I don’t have these issues myself on VS2017
      But I’m not sure if that is the reason for me not having these issues.

Leave a Reply

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