Freigeben über

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

  • Artikel

David Meego - Click for blog homepageThis 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

Comments

  • Anonymous
    October 22, 2013
    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.

  • Anonymous
    December 02, 2013
    Thank you, useful.

  • Anonymous
    December 16, 2013
    i just wants to know where the exported file stored which is done by the application

  • Anonymous
    December 16, 2013
    Hi Suku The file is created in the user's Temp folder. David

  • Anonymous
    January 27, 2016
    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?
  • Anonymous
    January 28, 2016
    Hi Sharmaine I would suggest discussing this on the online forums or with Microsoft support. Thanks David