How to print TransHeader and TransFooter in Microsoft Dynamics NAV RDLC report 

We know that TransHeader and TransFooter section types were available in NAV 2009 version but there are no such section types in RDLC, even though wecan achieve these in RDLC report using report functions.

We can print TransHeader and TransFooter in RDLC report using below functions:

     1.  Using RunningValue function.

     2.  Using ReportItems function.

RunningValue Function: It uses a specified function to return a running aggregate of the specified expression. It returns current summed up value for the page if we are summing value of a field, for example.

RunningValue function syntax:  RunningValue(Expression, Function, Scope);

Parameters:

     1.  Expression: Expression contain fields on which to perform the aggregation.

     2.  Function:  Aggregate function, sum, count, average, min, max, to apply to the expression.

     3.  Scope: Name of a grouping that contains the report items to which to apply the aggregate function. The running value is reset for each new group.

ReportItems Function: This function can be used to refer value of a text box from other text box.

ReportItems function syntax: ReportItems!<TextBoxName.Value>;

A sales document where we need to print “Continued” caption and “Balance or Amount” in current page footer and the same in next page header as if sales entries continued to two or more pages for a group, for instance.

Here I explain how to do this. We can use RunningValue function to aggregate values of a field and refer the field value from text boxes from page header and footer to get TransHeader and TransFooter values for the current page.

Step by step:

a.  Add a textbox on the detail section of a row in table control. We are here summing up values of a field based on a group hence we must place a text box in details section row, the text box where we add RunningValue function to aggregate values, shown in below screenshot.

Screenshot 1: Shows text box added in Details row of the table control.

 b.  Write RunningValue function in the text box expression window.

 i.  You can also select the RunningValue function from common function list available in the expression window as shown below.

Screenshot 2: Shows how to select RunningValue function in expression window.

ii.   Fill up parameters in the function as I explain below, such as

1.  First parameter (expression): The field which want to sum up. I have selected “Amount” field in “Sales Line” table, for example, as shown below.

2.  Second parameter (Aggregate function): Choose the aggregate function, Sum, Count, Avg, Min, Max, etc. I have chosen “Sum” to sum up sales lines amount, for example, as shown below.

3.  Group (Scope): Choose name of the group to which to apply aggregate function, “No_SalesHeader”, for example. Here I’m grouping sales lines amount based on sales document number, as shown below.

Screenshot 3: Shows RunningValue function parameters and Group.

Save the expression and rename the text box as “TransFooterSum”.

c.   Add two text boxes in Page Footer to shows “Continued…..” caption and “Balance or Amount” of the current page.

Screenshot 4: Shows Page Footer Text Boxes.

d.   Add caption “Continued…..” caption in first text box and for second text box refer the last summed up value from the text box “TransFooterSum”, where Aggregate function has been written to sum up the “Amount” from sales lines.

i.   Write text “Continued…..” in the expression window for first text box and click OK to save and close the text box.

ii.   Use the function “ReportItems!<TextBoxName.Value>” to refer value of the text box, In second text box, as shown below.

iii.  Get last value of the text box, because we need last summed up entry (value) for the current page, it shows summed up value for entries onto the page, in second text box. Here the RunningValue function returns summed up value for each page based on the group to which field value refers to, as shown below.

  

Screenshot 5: Shows how to refer last summed up value from “TransFooterSum” text box.

e.   We should hide TransFooter as we print final sum or total in the last page. We can hide these text boxes using built in functions related to page numbers such as Globals!PageNumber and Globals!TotalPages.

Write below expression in visibility window of text boxes to hide in the last page:

 “=IIF(Globals!PageNumber=Globals!TotalPages,true,false)”

Parameters:

1.   Expression (First Parameter): Function hides the value if expression evaluates to true. Here we want to hide text boxes if we are on the last page. It compares whether current page is equals to last page (total pages).

2.   Hide if true (Second Paramter): Hides if expression evaluates to true.

3.   Show if true (Third Parameter): Shows if expression evaluates to true.

Hence we hide text boxes if current page reaches last page while printing report.

f.    We can use the same functions, fields and steps to achieve TransHeader sum with different values such as:

i.   To get TransHeader sum we need to refer to first summed up value on the page. As I explained above, if we get the first summed up value which includes the previous page sum with current page first entry. As we are fetching the first entry, it has been summed up with the previous page, hence we must subtract the first entry on the pager to get previous page sum, to show the last summed up value of the last page in the current page TransHeader.

    “=RunningValue(Fields!Amount_SalesLine.Value,Sum,"No_SalesHeader") - Fields!Amount_SalesLine.Value”

ii.   After this we refer this value from a text box in the Page Header. Hence we must refer the first value of the function on the page.

     =First(ReportItems!TransHeaderSum.Value)

Report printed screenshots shown below:

       Screenshot 5: Shows first page (No Transheader)

      Screenshot 6: Shows second page (Both TransHeader and TransFooter)