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:
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.
nice article and information has been shared
Very useful. Whats about copies in a report?
I think you can easily extend this technique for the reports with multiple copies or multiple documents – consider replacement of all “Globals!PageNumber” with “Globals!PageNumber – DocumentOffset”, where DocumentOffset is a page offset of next document or copy. This will restart transheaders/footers calculation for each document from zero.
Determining “Document Offset” is another problem, but the solution for it is well-known (e.g. have a look how it is calculated in REP205).
what if transheader/footer is placed in body in Visual studio. In that case hidden expression : =IIF(Globals!PageNumber > 1, False, True) can't be used. ("Globals!PageNumber" can't be used in body).
So what would be the alternative solution for this issue?
Thank it was very helpful
This works only if there is only one table in VS, what is happening if there are more than one tables
There might be chances that for more complex scenarios the solution recently proposed here
might be useful in override the limitation posted with this simplified demo blog.
sadly the sum function executed in the footer can cause a runtime error when Code.BlankZero is used in the Customer_Debit_Amount field.
A possible solution is to set the hidden property for that textbox to =(Fields!Customer_Debit_Amount.Value=0) though that would also hide the formatting like borders and background colors. Who knows a better solution to hide zero's?
But when i use this code hash table reset for next page in report viewer. and the result will show only page total... not cumulative total.. plz post proper solution.
The definition of the Hashtable RunningTotals as "Shared" leads to a repeated consumption of memory at every call of the report in a batch scenario.
The declaration as "Public" or by a simple "Dim" works better.