Implementing Transfooter and Transheader Functionality in Dynamics NAV 2013 R2 Reports

Introduction

When Dynamics NAV 2009 was released application reports changed quite a bit, with it being possible to design reports directly in Microsoft Visual Studio. Unlike the built-in Dynamics NAV Classic report designer, Visual Studio report designer allows for some new features not previously available, like charts with 3D effects, images and colors, and dynamic sort based on different columns.

In Dynamics NAV 2009 there was a choice for users between the Classic and Role-Tailored Client, which then affected the choice of report designer respectively. However, with Dynamics NAV 2013 the Classic Client was discontinued and a new Report Dataset Designer was implemented. Hence, some features available in the Classic report designer and native Dynamics NAV report engine were now obsolete. A good example of such a discontinued feature is Transheader/ Transfooter functionality. This was a special type of header and footer which allowed the storage of intermediate values between the page breaks.

Below is an example of a Transheader/Transfooter in a Detail Trial Balance report:

The Transfooter is storing the subtotals for account 1240.

The Transheader brings the subtotals for account 1240 to the next page.

In this demo, we are going to replicate the same functionality using Visual Studio report designer and Dynamics NAV 2013 R2. The Sales Quote report copy will be used to demonstrate the feature.

Creating the Custom Code

First of all, we need to create custom functions to calculate and store the intermediate values.

  1. Open Dynamics NAV 2013 R2 Development Environment and in the Object Designer, select the Sales Quote report copy. In our case, this is report 50000. You can create a copy of standard Sales Quote report by designing it and clicking File, Save As… in the menu:

  2.  

    Click Design to open the report designer, then click View, Layout. If you do not have Visual Studio installed, it is possible to use built-in Report Builder environment, which is similar to Visual Studio. To use the Report Builder, click Tools, Options and select the User Report Builder option:

     

  3. In the Report Builder designer, click somewhere outside the report layout in order to bring up the general report properties. Locate the Code property:

  4. Click on the AssistEdit button to open the Code editor. Enter the following functions:

    The PadRight function will create a dotted line after the Continued caption.

    The SetTotalLine and GetTotalLine functions will retrieve the subtotals on the report footer and header sections. The SetTotalReport and GetTotalReport functions will display the actual subtotals on the header and footer sections.

    Click OK to close the window.
     

  5. Now, on the report body, add the right-side column to the Table_Lines table:

  6. Right-click on the cell in the respected row (as shown on the screenshot) and click Expression. Add the following expression to the textbox:
    =RunningValue(Fields!LineAmt_SalesLine.Value,Sum,”Table_Lines_Group1″)

    Click OK to close the Expression window. In the textbox properties, set the Color property to Red, the Hidden property to True and the Name property to RunningValueLine.

    This cell will store the running values for the quote lines.
     

  7. In the Navigation pane of the report, select Insert tab and click Footer to add the report footer:

  8. On the footer, place 2 textboxes as shown below:

    Select the first textbox and set its Color property to White. This is needed to hide the textbox values from the user. We cannot set the Hidden property to True as we will use this cell values in our calculations further. If we set it to Hidden, the values of Transfooter textbox will be zero.

    Assign the following expression to this textbox:
    =Code.SetTotalLine(Sum(ReportItems!RunningValueLine.Value))

    Select the second textbox and set its Name property to Transfooter and its Hidden property to:
    =Code.GetGroupPageNumber(ReportItems!NewPage.Value,Globals!PageNumber) = Globals!OverallTotalPages

    This is to make sure that the textbox will not show on the last page of the report.

    Set the TextAlign property to Right.

    Assign the following expression to the textbox:
    =Code.PadRight(“Continued”) & ” ” & FormatNumber(Code.GetTotalLine())

     

  9. On the report header, add the following textbox:
  10. Set its Hidden property to: =Code.GetGroupPageNumber(ReportItems!NewPage.Value,Globals!PageNumber)=1

    This is to make sure that the textbox will not show on the first page of the report.

    Set the textbox Name property to Transheader.

    Assign the following expression to the textbox:
    =Code.PadRight(“Continued”) & ” ” & FormatNumber(Code.GetTotalLine())

    Set the TextAlign property to Right.

    Close the Report Builder and save the report layout. Close and save the report in Dynamics NAV as well. If you run the report now, selecting the quote with many lines, you will notice the Transfooter and Transheader sections appearing respectively:

    Page 1:

    Page 2:

Conclusion:

To sum up, the following steps are required for replicating the Transheader/Transfooter functionality in RDLC:

  1. Add the custom code to hold the subtotal values
  2. Add the hidden cell on the table group row to hold the running values for the amounts
  3. Add the Transheader/Transfooter textboxes on the report header and footer and set its properties
  4. Add a call to the footer to set the values taken from the running values in the report body section
  • Alexander

    What does function GetGroupPageNumber(ReportItems!NewPage.Value,Globals!PageNumber) do and where is it set?
    Thanks.