Last week we discussed how to apply standard formatting codes for currencies and dates to Microsoft Dynamics NAV 2009 RTC reports. As I mentioned in that blog post, there are some hoops we have to jump through in order to show more than one culture’s formats on the same report. In the typical report the localization information is contained in the report header and it will only include a single localization. Fortunately Microsoft includes its globalization standards as a .NET DLL that we can add as an assembly to a report.

The Setup

For this example we are going to use the standard report “Customer Account Details” as a base report (renumbered so we don’t overwrite anybody’s default report). This report already has the option to show customer account details in the customer’s own currency, however it does not include the region specific currency formatting except for the number of decimal places. For kicks, and because it provides a better example, we are also going to take the dates and format those to the localized short date format. Note that this latter customization is one that might cause confusion if used in an actual client environment, especially if you have a particularly diverse set of customer regions being reported on with a single report.

It is worth mentioning that this example does not make any changes to the behavior of the classic version of this report except to add some hidden data fields for the RTC to use. To the best of my (admittedly newbie) knowledge, formatting of this type is not possible using the classic version without creating a complex function to apply the formatting prior to outputting the data to the report.

The Hoops

First Hoop – Letting your report use external assemblies

If you look at the MSDN page regarding currency formatting codes you will see that references are made to a “CultureInfo” object. Yet if you try to define such an object or make use of one per the VB examples shown, you will get an error. This is because we have to add the assembly for it. Before we can do that though, we need to edit the report properties to tell the report to allow external assemblies. Load up the report in classic, put the cursor on a blank line and hit ALT+F4 to bring up the report properties. Once you have that up, set the “EnableExternalAssemblies” option to “Yes”.

If you forget this step then later on you will see an error about ‘not a trusted assembly’ that looks a lot like this:

Second Hoop – Adding the assembly to the RTC version of the report

Once you have external assemblies enabled, the next step is to actually add that assembly to the report so that we can call upon the CultureInfo object within in.

To do this you have to load up the report designer (using the “View | Layout” menu option) and right click on the top left corner of the report in the report designer and select properties:

In the properties dialog, click on the “References” tab. Click on the ellipses to bring up the list of modules, click on the “.NET” tab and locate “sysglobl”:

Note: If you’ve not used sysglobl before, you may have to use the Browse tab the first time.  Browse to your local Microsoft.NET folder and locate a “sysglobl.dll” in the relevant Framework folder.

Once you have added the globalization assembly, it should appear in the list as shown below:

Next we have to add in some code to let us make use of the CultureInfo object.

Third Hoop – Creating functions to use the format codes with the globalization object

In a future blog post we will discuss some of the interesting ways to use the ability to add code to the report. For now though, we just want to create two custom functions that will let us format dates and currencies to a specific localization. As we did in Fig 4., above, we are going to right click the top left corner of the report and select properties. This time, instead of going to the “References” tab we are going to go to the “Code” tab.

Inside the code tab, we will define two functions:

FormatCurr (ByVal Amount as Decimal, ByVal Precision as Integer, ByVal Region as Integer, ByVal BlankNegZero as Boolean)

FormatDate (ByVal TransDate as Date, ByVal Region as Integer)

The first function will take the decimal amount to be formatted, the number of digits of precision (which we will pass in from the Currency.”Amount Decimal Places” setting), the Windows integer region code (taken from Language.”Windows Language ID”), and whether or not to return blanks for zero or negative values. For the second function we do not need to worry about rounding precision on a date so we just pass in the date in question and the Windows integer region code.

The code itself is very simple VB .NET code:

Public Function FormatCurr(ByVal Amount As Decimal, ByVal Precision as Integer, ByVal Region as Integer,

 ByVal BlankNegZero as Boolean)

   Dim TempString as String

   if (Amount <= 0 and BlankNegZero) then

     return ""

   end if

   TempString = "C" + Precision.ToString

   Return Amount.ToString(TempString, New Globalization.CultureInfo(Region, False))

End Function

Public Function FormatDate(ByVal TransDate As Date, ByVal Region as Integer)

   Return TransDate.ToString("d", New Globalization.CultureInfo(Region, False))

End Function

Now all we have to do is add some data to the classic version of the report and adjust some of the RTC fields and we will be done.

Making the customization

In the classic version of the report, we now have to add decimal precision and the Windows language ID as hidden fields. The decimal precision we can look up and pass from the Currency table (ID 4), the Windows language ID we will first have to do a lookup on the customer’s language in the Customer table (ID 18) and use that language code to make a lookup into the Language table (ID 8 ) for the Windows language ID.

Both of these lookups make the most sense to do in the Customer – OnAfterGetRecord of the classic report (the .fob of the report is included at the bottom of this post, search on “SSS ABS” in that module to find the relevant modifications).

We will add the data in hidden text boxes in the section designer in the Customer, Body (8) section.

Once we have those fields added we can then go to Visual Studio and make changes to the expression for the currency fields. It is worth mentioning that I have left most of the original function intact, such that the report will print with the existing default formatting if the “Print Amounts in Customer’s Currency” option is left unchecked. Some of the expressions are thus greatly expanded.

For example, the original expression for a debit amount is as follows:

=Code.BlankNegAndZero(Fields!AmountToPrint.Value)

And the modified version is shown below (with spacing added for readability):

=iif(NOT Fields!PrintAmountsInLocal.Value,

Code.BlankNegAndZero(Fields!AmountToPrint.Value),

Code.FormatCurr(Fields!AmountToPrint.Value,Fields!CurrPrecision.Value, Fields!LanguageID.Value,TRUE))

The last step before we are ready to roll is to look at the formatting of the table cells. While the classic version of the data is likely going to follow currency conventions, it will not include any special currency symbols that can help make it obvious what currency we are looking at. If the user selects not to show local currency we should retain the original formatting, but if they do use the culture specific formatting, then our FormatCurr and FormatDate functions will handle that instead of the Format property. For the same cell as used in the above sample for the expression of the debit amount, the “Format” field of the cell that originally reads:

=Fields!AmountToPrintFormat.Value

Will be modified to this:

=iif(Fields!PrintAmountsInLocal.Value, nothing, Fields!AmountToPrintFormat.Value)

Results

We can now look at the output and see what looks different when we show amounts in local date and currency and not in local date and currency and compare that with the base version of the report.

I am running the standard USA Cronus database filtering only on customers 01445544 (USD), 32656565 (Euro) and 42147258 (Czech koruna). The base version of the report will display as follows with the option to show amounts in customer currency:

The modified version now displays with region specific date and currency formatting:

Conclusion

If you have an international client that does want some multiple region-specific formatting on the same report or even formatting for a single region that is not the client’s installed region, this method will do the trick. The code functions are also easy enough to copy and paste into a new report, which means that once you jump through the hoops of letting the report use the sysglobl assembly, it is pretty easy to do again on another report.

Download Modified Customer Account Detail Report