Microsoft Dynamics GP 2013 SmartList Export to Excel shows currency values with 5 decimal places

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

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:

 

Hope this helps.

David

Export to Excel examples.zip

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

6 thoughts on “Microsoft Dynamics GP 2013 SmartList Export to Excel shows currency values with 5 decimal places

  1. In summary:  Since you can now set Decimal Places for your fields in GP 2013 SmartList Designer, this takes away the auto-formatting that used to be done in previous versions.  Customers will have to use SmartList Designer (a free addition in the latest SP of GP 2013) to set these Decimal Places manually.

    Like

  2. When I d/load my smart-list to excel I have 2 issues:
    – Numeric fields are formatted as general so I cannot 'sum' the totals in excel, even if i format the field manually.
    – If there are blank columns, the data is left adjusted.
    Any ideas how to fix this?

    Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.