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.
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.
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.
Thank you, useful.
i just wants to know where the exported file stored which is done by the application
The file is created in the user's Temp folder.
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?
I would suggest discussing this on the online forums or with Microsoft support.
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.