Have you ever come across column formatting issues after you’ve exported a SmartList to Excel? Sean Hunter, Turnkey’s Dynamics GP reporting specialist, probably knows why:
In a recent customer request, we created a custom SmartList using Microsoft’s SmartList Designer in Dynamics GP (version 2013 R2 +). The design of the report was best completed by creating a custom SQL view as the basis for the Smartlist. Since this method was used, Smartlist Designer has the native ability to deploy an Excel version of the report as well. The Excel reports work just as the other Dynamics GP Excel reports, with the direct data connection and refresh capabilities.
All worked perfectly until the client began making favorites with the new SmartList which involved adding filters and moving/removing columns that weren’t needed. Once they attempted to Export the records to Excel an error message displayed with the exported data coming out wrong. The most prevalent of these in this specific case was the formatting of the Date column.
So what happened during the export? As it turns out, when the Excel version of the SmartList was deployed, the column that the TRX Date originally was in (column C) was formatted as a Date (Fig 1) and the new column that the TRX Date column was moved to (Column E) was formatted as General (Fig 2). This is because upon deployment of the Excel version, a Data Connection was created along with the Excel Report version, columns were formatted for the specific data types, and without any changes, all works and looks perfect (Fig 3).
So how to fix this? A simple, undocumented explanation! I did some research and came across a few articles speaking of decimal place format rendering issues upon exporting from GP to Excel. In those instances a Dex.INI switch was put in place. This Dex INI switch just happens to be one we had covered in the past at our user groups and is the same switch which is promoted to substantially speed up exporting Smartlists to Excel. Being that we had covered it before and seemed to help in other scenarios I gave it a try.
NOTE: Before continuing to the solution is that it is not recommended to implement this if the users are utilizing the Web Client version of Dynamics GP. The switch apparently has adverse effects on those environments.
The switch is the following SmartListEnhancedExcelExport=TRUE and is applied to the DEX.INI file.
- Locate and open the DEX.INI file (Fig 4). The DEX.INI file is normally found in the Data folder of your Dynamics GP installation (EX: C:\Program Files (x86)\Microsoft Dynamics\GP2016\Data).
- So I opened the Dex Configuration settings file in Notepad and added the Enhanced Excel Export switch to the end of the list and saved the file (Fig 5).
- After the change I exported the same Smartlist again and the issue was gone (Fig 6).
The solution works perfectly with the way SmartList Designer works with the Excel publish. Now the Favorite exports to a static format while the * version pulls up the Excel version.
Hopefully this specific scenario and explanation helps you in your future endeavors!
Please let us know if you have any questions about these steps, or anything else regarding Dynamics GP.