As is well known, the Transheader-/Transfooter functionality of the classic client report generator in NAV versions 2009 or earlier is no more available for the RTC client. Furthermore there seems to be not a single carryover line in standard NAV report layouts, anymore.
Because some customers using Dynamics NAV RTC client still attach importance to carryover lines, especially in document type reports like invoices, I tried to find a most easy, general and elegant method of implementing this feature into RDLC report layouts of version NAV 2013 R2 or higher, …
…though some effort has already been made by others, http://dynamicsuser.net/blogs/clausl/archive/2014/03/29/how-do-i-create-transfooter-and-transheader-in-microsoft-dynamics-nav-2013-r2.aspx or http://www.dynamics101.com/2014/10/implementing-transfooter-transheader-functionality-dynamics-nav-2013-r2-reports/ , to mention only a few of them.
In order to develop a general principle for the implementation of carryovers, one must always be aware of the following principles (peculiarities) of RDLC report rendering:
I) A RDLC report layout consists of a report body and optionally one page header and one page footer:
To understand, what’s going on in the following examples, you must know, the way, RDLC report pages are rendered. From my tests the following order of rendering results:
Body1 – Header1 – Footer1 – Body2 – Header2 – Footer2 – ….
That means, the body of page 1 is generated (rendered) first, followed by the header of page 1, and so on. This is possible because page headers and footers either have a fixed height on a page or are missing completely. For example there are options to omit the header/footer on the first/last page. So the report rendering function can always determine the maximum height needed for the body, calculated from the page height, the top-/bottom margins and the header-/footer height. This rendering sequence has decisive consequences for the way, one can refer to values of the body on the header and footer and vice versa. So it is not possible, for example, to use the page number on the page body, because you cannot set a shared variable in the page header and use it within the body of the same page, because the page body is rendered first. But the opposite is possible, and widely used, indeed. For example, the well known SetData-/GetData method or the improved SetFields method https://massivedynamicsblog.wordpress.com/2015/11/15/getting-rid-of-setdata-and-getdata-in-rdlc-reports-of-ms-dynamics-nav/ make wide use of the fact, that the body of a page is rendered before the header and footer of the same page. Of course, there must be an additional mechanism, which makes the overall number of pages already available on the first page’s header, but the way this works exactly is not of interest within this context. One can easily test the order of rendering by incrementing and printing a public shared integer variable via a custom increment function from within the body, the header and the footer of a report (see one of the following blog posts).
II) During this rendering process, sometimes a strange thing happens: When the rendering of the page body has been finished, the report generator suddenly decides, for one reason or another, to put one or more lines from the bottom of the page body to the top of the following page body. This effect has severe consequences for the reliability of shared variables, that were set in the visibility property of the textboxes of those moved lines, for example. Suppose, you may have the idea to sum up the carryover within a shared variable, carryAmount, through the visibility property of the textbox containing the line amount. The corresponding summing function in the Report custom code could return False, because the line amount textbox is normally visible. If, for example, the last line is moved to the next page body, this will happen after the summing function has already summed up the carryAmount variable, resulting in a wrong (too high) carryover value.
As a consequence, shared variables set in the page body are only reliable for use in page header and footer, if they are set on the top of a page body. This is the reason, why everybody impresses upon you to put the hidden tablix or textbox on the top of the report body, so that it is rendered on the top of the same page, where its values are to be shown in the page header.
III) The value of every textbox, which is rendered on a page body can be referred to on the header and/or footer of the same page. This is done via the ReportItems collection, eventually supplied with a preceding First or Last aggregate function, if the textbox is within a repeating tablix member.
This textbox can even be hidden. But only the value of a statically hidden textbox can be referred to.
If the hidden property is evaluated through an expression, and this expression returns True, the value of this textbox is no more part of the ReportItems collection and can not be referred to.
For that reason, a statically hidden textbox containing a RunningValue is working, at all, and the line, containing the textbox, can even be dynamically hidden, too.
If you keep these three principle facts in mind, you will understand the implementation of carryovers in the following examples.
One consequence of these facts is, that it is by no means possible to place the carryover line within the report body, except you fix the number of lines per page by implementing manual page breaks (see Claus’ blog). Instead it has to be placed within the report header and footer.
For clarity purposes, I prepared a kind of simplified Sales Order report, with the following dataitem structure:
and the corresponding RDLC layout:
As you can see, the carryover line consists of two textboxes contained within a rectangle in order to switch their visibility in a simple way. You will find the usual SetFields (or SetData) textbox implemented as a statically hidden row on top of the NewDocument tablix (yellow line), which sets the values for the header textboxes, each time a new document number causes a page break. The rightmost column of the SalesLines tablix has been added and statically set to Hidden=True. It contains the RunningValueLineAmount textbox (yellow rectangle), which evaluates the following expression:
The (last) value of this textbox is referenced in the report footer, for the expression of the carryover:
Note, that implicitly Last(ReportItems!RunningValueLineAmount.Value) is fetched, so the Last aggregate function can be omitted.
The complete custom code for the carryover functionality is defined as follows: (details will be explained below)
The function SetCarryOver sets the variable carryOver to be used on the header of the next page, which works because of the rendering order (see principle I)
The corresponding expression for the carryover textbox on the page header is simply:
Note, that because of the carryOver variable is declared as public, there is no need for a GetCarryOver function. Instead, the value of the variable can be referred to, directly.
Now, on the first page of each document (provided there are several documents to be printed within one print job) the carryover line on the page header has to be suppressed. The same is true for the carryover line on the page footer of the last page of each document. If number of copies are implemented, this is true for each copy of a document.
From principle II) above we have learned, that it might be dangerous to use Boolean shared variables, set them in the report body and use them in the Hidden property of the carryover textboxes of the header and footer. In many cases the page number can be used to always hide the carryover textbox on the page header of page 1, provided, the page number is reset, appropriately. But fortunately from principle III) a general method can be found, which will work in any case for these kind of document reports:
A) Find or create a textbox on the report body, for which the following is true:
On every page, where this textbox is not printed, the carryover on the page footer of this page has to be printed and on every page, where this textbox is printed, the carryover on the page footer of this page must not be printed. This textbox may even be statically set to Hidden=True.
A candidate for this textbox can easily be found with the Sum textbox in the group footer of the SalesLine tablix, which will do the job. The idea is, to set a shared Boolean variable hideCarryOver to True on the page footer and use its value for the hidden property of the carryover line on the next page’s header. So the hidden property of the carryover rectangle on the page footer expresses to:
Note, that this expression correctly evaluates to False, if the Sum textbox is not printed on a page. In this case, the Sum.Value returns Null (or the empty string), which is not greater than the empty string and the function SetHideCarryOver returns False.
The value of hideCarryOver can be used directly for the Hidden property of the carryover rectangle on the page header:
Note, that hideCarryOver is initially set to True in the custom code. Otherwise a carry over line would be printed on the first page’s header.
Here you can download this sample report for NAV2015:
Regarding carryovers there is another kind of report, which must be treated slightly different. In the example above, there was a page break between the group instances (No_SalesHeader) for the line amount of which the carryover had to be calculated. For that reason, the sum textbox, which is always printed at the end of each group instance, occurs at most once on a page.
In the standard report 4, “Detail Trial Balance”, for example, there may be no such page break. If the corresponding option “New Page per G/L Acc.” is not chosen, more than one G/L Account and their sum lines may be printed on a page:
For these kind of reports, the principle A) from above has to be slightly enhanced. Obviously, the carry over in the page footer must only be printed, if the last line is not a sum line. So how can we accomplish this?
The answer is quite simple. At first, every row in the dataset is supplied with a sequential number, which I will call the GroupNoGLAcc. Then two hidden textboxes are added to the TableGLEntry tablix within a rightmost column. These textboxes are named GroupNoGLAccLine and GroupNoGLAccSum, respectively and the expression for both is set to GroupNoGLAcc.
The additional column on the right with the two yellow textboxes is statically set to Hidden=True.
For this special report, there is no need to define a RunningValue column, since the column GLBalance can be used quite comfortably.
It is easy to recognize, that the carryover line on the page footer has now to be hidden, if the last value of the GroupNoGLAccLine textbox is less than or equal to the last value of the GroupNoGLAccSum textbox on the page body. Since a Hidden expression cannot refer to more than one ReportItems value, the whole ReportItems collection is passed to the SetHideCarryOver function and the comparison is made within the function:
The SetHideCarryOver function is now defined as:
This is working for all possible options combinations of standard NAV report 4 and for all possible data situations. But what, if a page break occurs right after the headline for a G/L Account has been printed? Fortunately, this case cannot occur, because the corresponding static tablix member is set to KeepTogether=True and KeepWithGroup=After, which permits a page break directly after the headline. Otherwise, a third textbox for the header line has to be implemented into the layout and into the carryover condition.
Of course in this example, the carry over on the page footer is somewhat superfluous and can be hidden. This is most easily accomplished by modifying the SetHideCarryOver function to return always True and also by setting carryover from within this function:
The SetCarryOver function together with the expression of the carryover textbox on the page footer can be deleted in these cases, where the page footer is generally hidden.
Here you can download this example, the modified Report 4 of NAV2015
Summary: Starting from some basic principles we found, that there is a simple and elegant way to implement carryovers for different kinds of reports.
Some additional tips at the end:
1. Avoid setting shared variables in the report body, because of II)
2. When you test carryovers or when you make strong use of shared variables, set PreviewMode to PrintLayout in the report properties, otherwise strange effects may occur.