You might remember I blogged this solution on the NAV Team blog:
This solution unfortunately does not support Reports with several groups.
As I see it we have these 3 scenarios when working with Transfooter and Transheader, so carry over the value in the Page, so it visible in the bottom of the page what the last value are and being transferred to the next page header where it is visible what the last value was on the previous page.
Scenario 1. Simple list report with no groups. I explained how to do this at the NAV team blog here.
Scenario 2. Report with groups and with page breaks in between each group. For us to solve this scenario 100% we need VS 2012 which is supported in NAV 2013 R2. I have explained how to do this here: “Transfooter / Transheader working with groups - Part 2”
Scenario 3. Report with groups and with NO page breaks in between each group.
This is the scenario I will explain in this Blog Post. The solution I’m showing will work in both NAV 2009, NAV 2013 and NAV 2013 R2, but I will do this in NAV 2013 R2. I suggest you download my solution here so you can play with it while I explain. My report is a simplyfied version of the standard report 108. So it shows each the customers in my database where Sales Lines exist.
I want to thank Marko Divnic for pushing me to find a solution for this problem when having groups in your report and want to implement Transfooter and Transheader, just like we had in Classic report in the old days.
First copy this code into the Report Properties:
Private running As Double = 0
Public Function SetTotal(ByVal val As Double) As Double
running = val
Public Function GetTotal() As Double
Private Header As Boolean = 0
Public Function SetShowHeader(ByVal val As Boolean) As Boolean
Header = val
Public Function GetShowHeader() As Boolean
We will use the SetTotal to set the total in the Page Footer and GetTotal to get the total from previous page. And we will use SetShowHeader and GetShowHeader to control if the PageHeader should be shown.
Now you need to add 2 columns to your main tablix
We need these columns since we need the RunningValue of the Amount_SalesLine in the details and we need to know when we have reached the last row. Both the following expressions need to be placed in the details row, for this to work, so not in the Group Header and not in the Group Footer. For demo purpose I have made these Columns big so you can see them, but you of course would make these as small as possible and hide the values so they are not visible on the report.
In the RunningValue details textbox add this value:
With this we always know the accumulated value of the Amount_SalesLine, so we are ready to show this in the PageFooter and PageHeader on next page. If you value is different than Amount_SalesLine you of course modify this value.
In the Group details textbox add this value:
=RowNumber("Group2") = CountRows("Group2")
With this we always know when we have reached the last line in the Group.
If your Group is called different that Group2, you will of course need to modify this value.
Our work in the body is now complete.
Let’s start on the Page Footer. In this I have 2 textboxes a small one in the top left corner of the Page Footer and the Main TextBox which shows my Transfooter. It is light green, if you are in doubt
In the small TextBox I have the following value:
What this will do is that it looks at the GroupDone textbox, the Group column we just added. Remember that we had the expression =RowNumber("Group2") = CountRows("Group2") so when we have reach the last row in the group we have the value TRUE. Now I know when to show the Transheader on next Page, because I will just look at the Boolean value in the visibility expression of the textbox.
In the large light green Transfooter textbox I add this value:
What this will do, is that it will both show and set the RunningValue of the last visible detail line. It looks at the Detail textbox in the RunningValue column with this value: =RunningValue(Fields!Amount_SalesLine.Value,Sum,"Group2")
In the Transfooter we set the visibilty expression to:
=ReportItems!GroupDone.Value OR iif(Globals!OverallPageNumber = Globals!OverallTotalPages, TRUE, FALSE)
Again we use the ReportItems!GroupDone, so we only show transheader when we have not reached the last line in our Group. I also check for last page just to be sure that it is not shown on last page. If you have not upgraded to NAV 2013R2 OverallPageNumber and OverallTotalPages will be new. If you implement this solution in NAV 2009 or NAV 2013 you need to use PageNumber and TotalPages instead.
Now our work in Page Footer is complete, so lets go to the Page Header, where we want to show our Transheader. Here I have added a textbox which is light blue.
This textbox is quite simple, since the value is just:
and Visibility expression is just:
=iif(Globals!OverallPageNumber > 1, Code.GetShowHeader(),TRUE)
So I just show the value with GetTotal and make sure that I only show the Transheader when last row on previus page had not been reached.
This solution works almost 100% but notice on page 23 and 24 of my report that neither transfooter nor transheader is shown.
To work around this, you need to change any of these value until your report does not leave a Group footer alone on next page:
- Height of Top or Bottom margins
- Height of Page Header or Page Footer
- Height in the textboxes in the Tablix
I could of course have fixed this in my report, but I wanted to show you this small limitation
/Thanks, Claus Lundstrøm, MVP for Microsoft Dynamics NAV
Abakion.com & Supplychainbox.com
Filed under: NAV 2009, NAV 2013, NAV 2013 R2, Reporting | Make a Comment »