Printed invoices in Microsoft Dynamics NAV often have many lines, over a span of multiple pages. Normally, the subtotal of all the previous lines appears at the bottom of each page – marked as “Transferred to” – and at the top of the next page – marked as “Transferred from.” This running subtotal is updated on all pages of the invoice (Figure 1).
Unfortunately, RDLC reports have a different technical structure and therefore lack this feature out of the box. In an RDLC report, the same “sum” function won’t deliver the expected result. Instead, the function will obtain the total amount for all lines in the same group, instead of the total amount for lines on the current page and all previous pages.
Fortunately, we have a solution for those looking to utilize the transferred amount feature within RDLC.
Step 1: Add a new group for all detail lines in one page
We use the sales invoices report as an example here, but the solution will be the same for other reports, such as sales order, purchase invoices and purchase order. The standard NAV sales invoices report (report 10074) has four levels of groups, as the following image shows (Figure 2):
If an invoice has sales lines that exceed the maximum number per page, it will be printed onto the following pages. But all the sales lines are still in one group, making the sum of AmountExclInvDisc provide the total amount for the whole group.
To begin this solution, add a page break between each group, and let’s name it “NewPageNumGroup.” In this case, when we call the function sum of AmountExclInvDisc, it will get the total for lines only in the current page (Figure 3).
Step 2: Calculate transfer amount for previous group and show it as the amount transferred
Two terms are important to understand:
- the “Transferred from” amount at the top is the total amount for all lines on the previous pages
- the “Transferred to” amount at the bottom is the total amount for all lines on previous page and on current pages
The transfer amount for previous pages should be calculated in NAV code first and added to the RDLC report to obtain the “Transferred from” amount and “Transferred to” amount.
Step 3: Control display of the “Transfer amount” lines
Conditions need to be added to control the visibility of these two lines in order to only show them when necessary.
The solution is now complete, but we should mention one point. A new hard page break is added to the new page group. The max number of lines showing on one page is hard defined in code and this number needs to be adjusted case by case, as many factors will affect how many lines can be displayed on one page, such as the report body section size, font size of the line in report body and content wrapping. It also will take some additional blank page space to ensure the new group is always printed together. This is different behavior from what you will see in the classic report.
For more information on how BDO IT Solutions, Dynamics 365 Finance and Operations, Business Edition or Microsoft Dynamics NAV can help your organization, contact us.