Personalized Community is here!
Quickly customize your community to find the content you seek.
Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2021 Release Wave 1Discover the latest updates and new features to Dynamics 365 planned April 2021 through September 2021.
Release overview guides and videos Release Plan | Preview 2021 Release Wave 1 Timeline
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
ISSUE: Smartlist and Navigation Lists export currency values with 5 decimals in Excel using Microsoft Dynamics GP 2013
In Microsoft Dynamics GP 2013, the way data is exported from Smartlist and Navigation Lists to Excel has been changed. You will notice that amount columns are populated with 5 decimals in Excel and the formatting shown in the Smartlist appears to have been lost during the export. This is by design in Microsoft Dynamics GP 2013. This change was made to increase the performance of exporting data to Excel from Smartlist, and also to allow for exports through Smartlist from the Web Client. The code was changed from COM to OpenXML SDK, and to export the full 5 decimals directly from the SQL tables to ensure there is no data loss. This approach is safer than truncating the data to two decimals, and allows the user to format the field how they want in Excel. This change is by design to improve performance and prevent data loss.
However, we have received several cases asking if this is a bug in Microsoft Dynamics GP 2013, because it did not work this way in prior versions. Going forward, you will have to use one of the methods below to format the data:
METHOD 1: Format in Excel each time.
Format the currency field directly in Excel to two decimals. To do this:
1. Open Smartlist and export the data to Excel.
2. In the excel spreadsheet, right-click on column heading for the column you wish to format, and select Format Cells...
3. In the format cells window, select Currency and set the Decimal places to 2.
4. Choose if you want a Symbol displayed or not, and how you want negative numbers to appear. Click OK.
5. Save the spreadsheet.
METHOD 2: Use Export Solutions in Smartlist.
Create a formatted spreadsheet in Excel and use Export Solutions in Smartlist to reuse the same spreadsheet as a template to export to. To do this:
5. Enter a file name and location to save it to. Click Save.
6. Open the saved file again in Excel. (It should be listed under File | Recent). The line numbers are listed in the left-most column. Right above the ‘1’ for line 1, you will see a blank cell. Right-click on this blank cell and choose Clear Contents. This should clear all the data from the spreadsheet. Close the spreadsheet and save your changes to create a blank template or worksheet.
7. Now in Microsoft Dynamics GP, set up the export to be able to use this formatted spreadsheet going forward. To do this:
a. Click Microsoft Dynamics GP and click Smartlist.
b. In the top menu-bar, click on Smartlist and select Export Solutions.
c. Key in a Name in the Name filed in the upper right side of the window.
d. In the Document field, browse out to the formatted spreadsheet you created.
e. Change the Application field to Excel.
f. In the Works for Favorites section, expand what Smartlist and mark the ‘checkbox’ to the left of that Smartlist(s) you want to use with the saved spreadsheet. You can mark as many of favorites that you want to use this formatted spreadsheet.
g. Click Save. Exit out of the Export Solutions window.
h. Refresh the Smartlist window.
I. Select the Smartlist and now you should now see a drop-down arrow under the EXCEL button, where you can select to use the formatted spreadsheet you made, (or the ‘Quick export’ will be the old way showing all 5 decimals.)
j. Select the name of the formatted spreadsheet you created, and the data will export to Excel, where the currency column will now be formatted to two decimals.
k. Click FILE | SAVE AS and save the spreadsheet under a new name, to leave your template empty.
Note: Create as many formatted spreadsheets as you need and use Export Solutions to link them to the appropriate Smartlist.
I hope you will find this tip useful in the future and save you time from having to format in Excel for each export, when using the same Smartlist. (This information is also found in KB 2881746.)
Cheryl Waswick | Sr. Technical Support Engineer | Microsoft Dynamics GP Support
This blog article is about amounts exporting to Excel with 5 decimal places, and this is by design, not a quality issue. This was done to make the data export faster, and shows 5 zeroes because the SQL table holds 5 decimal places, as explained in the article above. The issue you are referring to is different where the amount does not come over at all. This was an issue (TFS 71173-fixed in GP 2015 RTM), and to get around this in GP 2013, you can add the following switch to the Dex.ini file (in the GP code folder), and then restart GP. This switch calls a different export routine, and will bring amounts over as numeric instead of text.
Here is a blog article that talks more about this dex.ini switch:
I hope this helps,
We recently installed GP 2013 and can't upgrade to GP 2015 for compatibility issues with other programs that we need to integrate. I am testing several of the reporting functions and note that this is still an issue. When I export a Smartlist to Excel 2013, I am getting 0 values in the columns where there are values in Smartlist. Can anyone please tell me if this will ever be fixed? I tried some of the work arounds and they either don't work or are very time consuming for both administrators and users.
Hi Cheryl W,
And one more modification in that Export Solution window, the source file location must be without spacing..
You have to deploy the Excel report tool from Microsoft Dynamics GP->Setup->System->Reporting Tools Setup. And you have to refer the report template in SmartList->Export Solutions to create new Profile by using the excel report file.
Business Applications communities