Microsoft Dynamics GP 2013 SmartList Export to Excel shows currency values with 5 decimal places
This topic has been in my drafts list for ages after it was mentioned in this partner forum thread: Decimal places in GP2013 - SmartList to Excel. However, I did not want to write it up until I could offer some solutions for it at the same time.
Well, I was beaten to it by Cheryl on the Dynamics GP Support and Services Blog. On the plus side, it means I can point you to her article for more details and solution options.
For Microsoft Dynamics GP 2013, there was a change made to the code that exports data from SmartList to Excel. This change has the side effect that now all currency data types (used for money amounts and quantities) are exported showing 5 decimal places.
The reason it is 5 decimal places goes back to architecture of Dexterity (the development environment for Microsoft Dynamics GP). The primary "floating point" or "real" data type in Dexterity is called Currency (formally Dollar). This data type allows for up to 19 digits with a fixed 5 decimal places. The number of decimal places actually displayed on the windows and reports is controlled by static or dynamic formats within the application. If you look at the data structures in SQL Server you will see that the Currency fields are defined as numeric(19,5).
The change made was to use the OpenXML SDK to transfer the data rather than the previous COM (Component Object Model) implementation. The change means that the export is much faster than before and can also handle much larger data sets. However it was no longer possible to provide conditional formatting of the columns.
I discussed this with one of the developers and here is what they said:
Actually, a conscious decision was made (when porting the SmartList and Navigation List code to use OpenXML SDK rather than COM) to define all decimal type numbers as having 5 decimal places. This is because we have to define styles for each cell, and if we wanted every cell to have different numbers of decimal places (for example, item quantity or currency decimals), it would have greatly complicated the implementation. We decided to err on the side of more, rather than not enough, decimal places.
To demonstrate, I replicated the issue by exporting the Financial >> Account Transactions SmartList favourite from both Microsoft Dynamics GP 2010 and Microsoft Dynamics GP 2013.
The Excel export from GP 2010 showed the Credit Amount and Debit Amount columns using 2 decimal places.
Click on image for full size.
The Excel export from GP 2013 showed the Credit Amount and Debit Amount columns using the full 5 decimal places.
Click on image for full size.
I have attached the example exported spreadsheets to the bottom of this article.
For more information and some solutions have a look at the following articles:
- Cheryl Waswick: Smartlist and Navigation Lists export currency values with 5 decimals in Excel using Microsoft Dynamics GP 2013
- Mariano Gomez: GP 2013: Why are my SmartLists not formatted when exported to Excel?
- This blog: Understanding how Microsoft Dynamics GP works with Microsoft SQL Server (thought you might find this interesting)
Hope this helps.
David