Transfooter and Transheader functionality in RDLC(SSRS) reports - revisited

Transfooter and Transheader functionality in RDLC(SSRS) reports - revisited

  • Comments 8

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

Attachment: Transfooter&Transheader - Version 2.txt
Leave a Comment
  • Please add 3 and 2 and type the answer here:
  • Post
  • nice article and information has been shared

     

  • Very useful. Whats about copies in a report?

    Thank you.

    Hermann

  • 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

  • Hi,

    There might be chances that for more complex scenarios the solution recently proposed here

    mibuso.com/.../transfooter-transheader-working-with-groups-part-1

    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?

Page 1 of 1 (8 items)