Skip to content
Microsoft Dynamics 365

In one of our previous blog post we discussed the possibility to do Transfooter and Transheader functionality in RDLC(SSRS) reports and describes a viable solution for this in RDLC.

In this blog post we would like to suggest an alternative, a bit more economical and easier to implement solution for the same problem.

For the demo we use the same table and the same report and will strive to achieve the same results as in the mentioned in our previous blog post.

1. Create new report blank report with table 18

clip_image002

2. Create DataItem ”Customer”

3. Go to Section Designer and add the following fields:

  • No.
  • Name
  • Debit Amount

4. Save the report as ID 50000 – Transfooter / Transheader

5. Now go to Visual Studio (View / Layout)

6. Create table and add the fields No, Name and Debit Amount

7. Give this table the name “MainTable”

8. Now we have added the basic for this report. But I would also like to have a Grand total of the Debit Amount so I add this as well. I add this in the Footer of the table

=”GrandTotal: ” & sum(Fields!Customer__Debit_Amount_.Value)

image

9. Now if my report is printed I get a list of my all my customer with Debit Amount displayed and with GrandTotal in the end of the report:

10. Now I create a small block of VBS code in order to perform some calculations and store intermediate data

Open “Report->Report Properties” dialog and select “Code” tab, enter the following VBS code:

11. Define a hashtable for storing running accumulated sums for each page of the report

Shared RunningTotals As New System.Collections.Hashtable

12. Define two public functions, which populate and query the hashtable from above

Public Function GetRunningTotal(ByVal CurrentPageNumber)

Return IIF(CurrentPageNumber > 0, RunningTotals(CurrentPageNumber), 0)

End Function

 

Public Function SetRunningTotal(ByVal CurrentPageTotal, ByVal CurrentPageNumber)

RunningTotals(CurrentPageNumber) = CurrentPageTotal + GetRunningTotal(CurrentPageNumber – 1)

Return RunningTotals(CurrentPageNumber)

End Function 

image

13. Ok, it’s now time to add a Transfooter and Transheader.

Enable Page Header and Page Footer in the report (click “Report->Page Header” and “Report->Page Footer”).

14. In the Page Footer I place a text box with the following expression:

=”Transfooter subtotal = ” & Code.SetRunningTotal( Sum(ReportItems!Customer__Debit_Amount_.Value), Globals!PageNumber)

image

This code actually performs the following actions:

– calculate the sum of all “Debit Amount” values on the current page (sic)

– adds this value to the running total, which has been already calculated for the previous page

– returns this value as the actual running total for the current page

15. In the Page header I place a text box with the following expression:

=”Transheader subtotal = ” & Code.GetRunningTotal(Globals!PageNumber-1)

This code fetches the running total, calculated up to the previous page

image

16. And then I set distinctive BackgroundColor and font Color just so this Transfooter and Transheader stand out in my report

image

17. Now I’m almost done but I would like to not see the Transheader on the first page and not to see the Transfooter on the last page.

So I set the following expressions for the “Visibilty->Hidden” properties of the page header:

=IIF(Globals!PageNumber > 1, False, True)

And for the page footer:

=IIF(Globals!PageNumber < Globals!TotalPages, False, True)

18. Now I’m done, I save, import into NAV and compile. After some fit and finish on the report it now looks like this when I print

Now I’m done, I save, import into NAV and compile. After some fit and finish on the report it now looks like this when I print:

clip_image027

clip_image029

Question: Would this also work in the example of having a list of sales order lines per sales header and the sales order lines goes to multiple pages?

Answer: The report above is a bit simplified in order to illustrate the point. It can be easily extended to support your scenario. I.e. the key for the hash should include page number AND header no to accomplish this.

You can download the report object here, thanks to Nickolay Belofastow.

/Claus Lundstrøm

Transfooter&Transheader – Version 2.txt

We're always looking for feedback and would like to hear from you. Please head to the Dynamics 365 Community to start a discussion, ask questions, and tell us what you think!

Join the conversation

Loading comments...