Previously, numbers were formatted as text in Dynamics GP reports when exported to Excel. Now, when a report is exported to Excel, numbers are formatted as numbers.
Many customers considered the prior behavior to be a bug, but that couldn’t be further from truth. When Microsoft first introduced the Web Client, the developers faced a difficult task: how to optimize in such a way that customers would never experience any performance issues. The solution they came up with involved exporting Excel smart lists without formatting. This often resulted in zeroes being dropped and currency amounts being exported with 5 decimals - or with no currency symbol.
Unfortunately for Microsoft, even though their intentions were good, that behavior caused a lot of problems and overwhelmed their customer support line with complaints from users who just couldn’t understand why the software would behave in such a non-intuitive manner.
Only a few knowledgeable system administrators knew about an undocumented Dex.ini switch (SmartlistEnhancedExcelExport=TRUE) that enabled limited formatting to Smartlists. Of course, such technical measures are not something that the average users should deal with, and system administrators have better things to do than to apply band-aid solutions to problems that shouldn’t exist in the first place.
Exporting to Excel the Right Way
The good news is that in Microsoft Dynamics GP, it’s now possible to export numbers from SmartList records into Microsoft Excel as numbers and not a text format. This allows users, for example, to do mathematical calculations with appropriate values.
- Open the SmartList window by clicking on SmartList in the main Microsoft GP window or from the Administration navigation menu.
- Click on Reports, then click on SmartLists.
- Click on the default Purchase Line Items SmartList report under Purchasing.
- It should have a QTY Ordered column that shows number values.
- Click on the Excel button in the SmartList window to export this report to Microsoft Excel.
- The Document Amount values have a format of 'Number'.
Getting the Most Value Out of Your Dynamics GP Solution
Microsoft’s focus on their innovative web client shows that hosted Dynamics GP is the future of ERP solutions. At