The solution I explain here can only be done with NAV 2013 R2. The reason for this is that we are using a new feature in Visual Studio 2012 where it is possible to reset the page number when a group is done. So if you are not using NAV 2013 R2, which supports Visual Studio 2012, read this blog post: Transfooter / Transheader working with groups - Part 1. You could also do Page breaks in the solution described in Part 1, but the solution I explain here is much simpler. If your report has no page breaks per group, no new features in NAV 2013 R2 can help you and you are back at solution described in Part 1: Transfooter / Transheader working with groups - Part 1
So if you are still here you report has page breaks between each group and you have NAV 2013 R2.
I suggest you first download my solution here, so you can play with solution while I explain.
For this scenario we only need this code added to the Report Properties:
Private running As Double = 0
Public Function SetTotal(ByVal val As Double) As Double
running = val
Public Function GetTotal() As Double
As in part 1, we will use the SetTotal to set the total in the Page Footer and GetTotal to get the total from previous page.
And in this scenario you only need 1 column added to your main tablix
We need this column since we need the RunningValue of the Amount_SalesLine in the details so we always know the accumilated value of Amount_SalesLine in the last row on the current page. The expression 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 the Column big so you can see it, but you of course would make this as small as possible and hide the value so this is not visible on your 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 your value is different than Amount_SalesLine you of course modify this value.
Now click on “Group2” in the Grouping Window at the bottom in Visual Studio, again your group might be called different.
Now navigate to the Properties page in Visual Studio, make sure that it says “Tablix Member” right under the name Properties:
Now expand “Group” and “PageBreak” and set the values
With this small exercise, the report will now create a page break every time a new customer is shown in our group, and it will even reset the page number so each customer is shown in its own page range. Yes this means that we can now truly have Page x of y in all our outgoing documents, when our customers are running NAV 2013 R2 of course.
Ok, our work in the body is now complete.
Let’s start on the Page Footer. In this I have only 1 textbox, not 2 as I had in solution explain in Part 1.
In this I have this value
What this will do, is that it will both show and set the RunningValue of the last visible detail line on current page. 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:
=Globals!PageNumber = Globals!TotalPages
So when PageNumber=TotalPages we know we are on the last page of the group and should not show the Transfooter textbox. Notice that there is a difference on OverallPageNumber and PageNumber. OverallPageNumber is for all pages in the report, while PageNumber is only for the group when you Reset the PageNumber and break between the groups.
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:
=Globals!PageNumber = 1
So I just show the value with GetTotal and make sure that I only show the Transheader when on all page except page number 1 of each group.
This solution will show Transfooter and Transheader eventhough the Group footer is alone on last page of the group
See i.e page 15 and 16 in my report:
If you can think of any Transfooter / Transheader scenario that I have not covered in the my 3 scenarios, please let me know in the comments:
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. This blog post.
Scenario 3. Report with groups and with NO page breaks in between each group. I explained this here.
BTW, notice that I also have Copy working perfect in above report. I will not explain here how I did this, but you are of course welcome to explore my report and see how I did it.
/Thanks, Claus Lundstrøm, MVP for Microsoft Dynamics NAV
Abakion.com & Supplychainbox.com
Filed under: NAV 2013 R2, Reporting | Make a Comment »