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
2. Create DataItem ”Customer”
3. Go to Section Designer and add the following fields:
- 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)
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)
Public Function SetRunningTotal(ByVal CurrentPageTotal, ByVal CurrentPageNumber)
RunningTotals(CurrentPageNumber) = CurrentPageTotal + GetRunningTotal(CurrentPageNumber - 1)
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)
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
16. And then I set distinctive BackgroundColor and font Color just so this Transfooter and Transheader stand out in my report
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:
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.