When Exporting certain reports from Microsoft Dynamics AX 2012 to Excel, the values are not numbers, but act as strings

When exporting certain reports from Microsoft Dynamics AX 2012 to Excel, the values are exported as string instead of numbers.

To replicate the issue:
1. Menu: General ledger > Reports > Reconciliation > Vendor > Vendor
2. Click Ok on the dialog window.
3. Once the report has been deployed, click on the Export > Excel button
4. Specify a filename and click save.
5. Open the Excel report
6. Try some math functions with some of the fields, e.g. “=E1+1”
7. Excel will display: #VALUE!

Reason:
The report is exporting the field as a string. Within the SSRS Report, you will find the code:
=Microsoft.Dynamics.Framework.Reports.BuiltInMethods.ToDisplayStringAmount(Parameters!AX_RenderingCulture.Value, sum(Fields!LedgerAmountMST.Value), true)

This is causing our values to be returned with a space as a thousand separator, but this is not a normal space (Ascii 32). It is a non-breaking space (Ascii 160).

I assume our localisation settings is causing this. We are using a space for our thousand separator.

Solutions:
1. Firstly you can remove the “Microsoft.Dynamics.Framework.Reports.BuiltInMethods.ToDisplayStringAmount(Parameters!AX_RenderingCulture.Value, ” from the report, leaving you with: sum(Fields!LedgerAmountMST.Value). Remember to redeploy the report.
2. The second option is to do a find and replace within Excel. But the trick is to not replace the normal space (ascii 32), but replace 0160. Use ALT 0160 as the find string.
3. Copy this if you can’t use your Alt and number pad: ” “.

Reports causing this error:
I was able to find only one report with this issue, the Vendor / Ledger Reconciliation report.
Menu: General ledger > Reports > Reconciliation > Vendor > Vendor

About Johan Groenewald

I'm a Microsoft Dynamics AX Developer, working at Axnosis in Pretoria, South Africa, with over 15 years experience as a software developer and working for the past 5 years on Dynamics AX 2009 and 2012.
This entry was posted in Microsoft Dynamics AX 2012. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *