Report upgrade from 2009 to 2013

I have noticed within the group of consultants and developers that the move to Visual Studio to handle report layout is quite a leap.  Not many are courageous enough to make this leap.

One of the most used tricks in the new layout is how headers and footers are handled.  To get the correct data up to the header og down to the footer a method using Visual Basic Code is used.

In our payroll system upgrade from NAV 2009 to NAV 2013 all reports had been upgraded.  However, they did not work properly and so the problem ended on my desk.

I found the problem and of course Claus had already blogged about this change of functionality between NAV 2009 and NAV 2013.

“The textbox is hidden and needs to be first thing that activated when the report is rendered. So hold that thought, it has to be hidden and at the top for Code.GetData to work. In NAV 2009 reports are rendered in Report Viewer 2008 this works like a charm, but in Report Viewer 2010 the SSRS team has been so kind to us to change the logic of how a reports is rendered. So if an textbox is hidden in RDLC 2008, the expression in this hidden textbox, is rendered at the end. So as I said the SetData textbox has to be hidden and at the top for Code.GetData to work, but now it is rendered as the last thing, and of course the result is that the fields get out of sync and the very idea using Code.GetData and Code.SetData is broken in NAV 2013 with Report Viewer 2010. Interesting thought!!! “

The first thing I needed to do was to add a single line of code to the Code.SetData function.  Go to Report Properties to solve this.


The “Return True” line makes sure that the field is visible and therefore rendered at the top.

Next to change the text box with the Code.SetData function.  In NAV 2009 layout we called this function by using it as an expression and set visibility to hidden.


In NAV 2013 layout clear the expression and move the Code.SetData function to the Visibility Section.


And that’s it folks…

AutoFormatType and Role Tailored Layout

If you have worked on a Role Tailored Layout and wanted to show a report without decimals you have most likely seen that the AutoFormatType option that we have in the Dynamics NAV 2009 R2 Classic Client does not work in the Role Tailored Layout

If you study the dataset that the Role Tailored Client delivers to the report the numbering format is not related to the AutoFormatType.  Why Microsoft chose to ignore this in this release I will never know.

In Iceland we do not use decimals in currency amounts.  However, when printing currency amount they are supposed to show decimals.  So I created my own AutoFormat solution.

The first step is to create a codeunit that uses the same logic as the AutoFormatType and delivers the Role Tailored Layout number format.

[code]OBJECT Codeunit 50012 RTC Decimal Format

GLSetup@1100408003 : Record 98;
Currency@1100408004 : Record 4;
SetupRead@1100408000 : Boolean;

PROCEDURE AutoFormatLayout@12(AutoFormatType@1000 : Integer;AutoFormatExpr@1001 : Text[80]) : Text[80];
AmountDecimalPlaces@1100408000 : Text[30];
IF AutoFormatType = 0 THEN


CASE AutoFormatType OF
1: // Amount
IF AutoFormatExpr = ” THEN
EXIT(CreateNumberFormat(GLSetup."Amount Decimal Places"))
IF GetCurrency(COPYSTR(AutoFormatExpr,1,10)) AND
(Currency."Amount Decimal Places" <> ”)
EXIT(CreateNumberFormat(Currency."Amount Decimal Places"))
EXIT(CreateNumberFormat(GLSetup."Amount Decimal Places"));

2: // Unit Amount
IF AutoFormatExpr = ” THEN
EXIT(CreateNumberFormat(GLSetup."Unit-Amount Decimal Places"))
IF GetCurrency(COPYSTR(AutoFormatExpr,1,10)) AND
(Currency."Unit-Amount Decimal Places" <> ”)
EXIT(CreateNumberFormat(Currency."Unit-Amount Decimal Places"))
EXIT(CreateNumberFormat(GLSetup."Unit-Amount Decimal Places"));
10: EXIT(”);

LOCAL PROCEDURE CreateNumberFormat@1100408000(AmountDecimalPlaces@1100408000 : Text[30]) RTCFormat : Text[30];
Pos@1100408001 : Integer;
DecimalPlaces@1100408002 : Integer;
Pos := STRPOS(AmountDecimalPlaces,’:’);
IF Pos > 0 THEN
AmountDecimalPlaces := COPYSTR(AmountDecimalPlaces,Pos + 1);

IF NOT EVALUATE(DecimalPlaces,AmountDecimalPlaces) THEN
DecimalPlaces := 0;

RTCFormat := ‘#,##0’;
IF DecimalPlaces > 0 THEN
RTCFormat := RTCFormat + ‘.’ + PADSTR(”,DecimalPlaces,’0′);

LOCAL PROCEDURE GetSetup@10015() : Boolean;

SetupRead := GLSetup.GET;


LOCAL PROCEDURE GetCurrency@1(CurrencyCode@1000 : Code[10]) : Boolean;
IF CurrencyCode = Currency.Code THEN
IF CurrencyCode = ” THEN BEGIN



Then I implement this on the Classic Section, example in Report 105.  First I add my codeunit to Globals.

Then I insert two hidden fields in the Sections.

The first field for the LCY format

and the second field for the current currency format

This adds the fields CurrencyAutoFormat and LCYAutoFormat to my dataset.

The final step is to replace the default Format property in the Role Tailored Layout.  I look at the field CustBalanceDue_1_ and see that this field can in some cases show currency amount and sometimes it shows LCY amounts.  This is determined by the PrintAmountsInLCY value.  I then set the Format property for this field to “=iif(Fields!PrintAmountsInLCY.Value,Fields!LCYAutoFormat.Value,Fields!CurrencyAutoFormat.Value)”

Next I look at the sum for this field.  This sum is always shown in the local currency.  I then set the Format property for this field to “=First(Fields!LCYAutoFormat.Value)”.

This way I have implemented the AutoFormatType functionality to the Role Tailored Layout.

RTCAutoformat Codeunit

Prepare for Report Transformation

On of the bigger tasks when upgrading to the Role Tailored Client is the report transformation.  In big database there are houndreds of reports.  Some of them might just be idle reports that are never used.  This blog from ArcherPoint that is based on a original post from Mark Brummel got me thinking.  In 2009 Microsoft added to the client the possibility to execute a function with ID 120 in codeunit 1 that only works for the Classic Client.

I mixed these together by adding the code to codeunit 1 in the same way as Mark Brummel but creating a report log table instead of a report print count table.

Here the Entry No. field automatically increments the numbers in the database.  I also changed the property DataPerCompany for the table to No.

The single instance codeunit is also a simple one.

The next step might just be to create a report for Excel Pivot table like I did with ledger tables.

Report Printing Log

Icelandic Localization

In the localized version of Dynamics NAV 2009 R2 the report layout for Role Tailored Client is missing.  I have created the layout and applied a few fixes to the reports.

ID Name Caption
204 Sales – Quote Sala – Tilboð
205 Order Confirmation Pöntunarstaðfesting
206 Sales – Invoice Sala – Reikningur
207 Sales – Credit Memo Sala – Kreditreikningur
210 Blanket Sales Order Standandi sölupöntun
405 Order Pöntun
406 Purchase – Invoice Innkaup – Reikningur
407 Purchase – Credit Memo Innkaup – Kreditreikningur
10911 IRS Details Upplýs. vegna skattstofu
10912 Trial Balance – IRS Number Prófjöfnuður – Skattst.númer
10913 IRS notification Tilkynning skattayfirvalda
10940 VAT Balancing A VSK-afstemming A
10941 VAT Balancing Report VSK

The following ZIP file is encrypted.

NAV IS2009R2 Reports