GP 2013: Why are my SmartLists not formatted when exported to Excel?

Just recently I have been seeing a number of questions around Microsoft Dynamics GP 2013 Smartlist exports to Microsoft Office Excel not being formatted correctly - let's rephrase, the correct word to use here is "nicely". The issues usually involve leading zeroes being dropped (in some cases) and currency amounts being exported with 5 decimals and no currency symbol.

Exported Payables Transactions smart list


To make a one swipe statement here, this behavior IS NOT a product bug - despite the inconvenience.

With the introduction of the Web Client in Microsoft Dynamics GP 2013, the development team needed the ability improve the performance of Smartlist exports over the browser. To gain this dramatic improvement in performance (which is also experienced when using the rich client), exported Excel smart lists were voided of formatting.

There's an undocumented Dex.ini switch that provides limited formatting to Smartlists, thus maintaining export performance:

SmartlistEnhancedExcelExport=TRUE

You can find more information on this Dex.ini switch here:

Undocumented DEX.INI switch cuts down SmartList export times to Microsoft Office Excel

If you are not going to deploy Web Client, then this should be fine as a workaround. However, this switch causes the Web Client to error out with the following message when attempting to export to Excel:

Web Client error exporting smart list to Excel with SmartlistEnhancedExcelExport=TRUE 

So there you have it!

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Comments

Anonymous said…
Does it matter what version of Excel? I have a client using Excel 2010 and the switch doesn't seem to be working.
August 23, 2013 at 10:30 AM
Anonymous said…
Wow, after adding:
SmartlistEnhancedExcelExport=TRUE

to my Dex.ini file, the Excel export runs SUPER fast. It's a huge noticeable difference. Too cool!

By the way, I did this in GP 2013 and GP 2010 R2. Works for both!
December 12, 2013 at 9:55 AM
RanjitCharles said…
This was more than displaying the correct number of decimals for us. In the Inventory Purchase Receipts Excel Export from smartlist (GP 2013), Qty received values greater than 1000 were exported with just the first digit. Eg. a received quantity of 1600 is exported as 1.00, 4032 was exported as 4.00. The Dex.ini setting took care of this problem. Thanks Mariano!
December 20, 2013 at 1:07 PM
go away said…
Problem with this or at least what I have noticed is that if one column is blank it moves the data on the right to the blank column on the left.

February 2, 2014 at 10:39 PM
Microsoft Dynamics GP said…
This article was very helpful in answering allot of questions.Thanks for posting.
May 31, 2014 at 5:06 AM
Dave said…
We are also having the problem that when a column is blank it moves the data to the left...has anyone figured out a solution to that issue?

thansk
November 3, 2014 at 3:36 PM
Anonymous said…
But my user has a column with multiple currencies. If the Currency Symbol is removed after export, how would you know which cell refers to which currency?
I don't think this is very useful.
January 16, 2015 at 4:50 AM
Mariano Gomez said…
You will need to export the currency Id as well.

MG.-
January 19, 2015 at 7:46 PM
Anonymous said…
Just wanna ask what will you do if it happen in GP Web while SmartlistEnhancedExcelExport=TRUE can't be used in GP Web. You have another solution?

Thanks before.
June 17, 2016 at 6:00 AM
Unknown said…
I just submitted a suggestion to fix the problem where the columns shift on the Excel export if you use the SmartlistEnhancedExcelExport=TRUE and the first column is blank. If you're experiencing that error, you can vote for the suggestion here:

https://connect.microsoft.com/dynamicssuggestions/feedback/details/3122390/fix-the-smartlist-export-to-excel-to-properly-align-columns-when-using-smartlistenhancedexcelexport-true
February 13, 2017 at 2:39 PM
Mey said…
Mariano Gomez,
Could you please explain the steps to export the currency id?
We have SmartlistEnhancedExcelExport=TRUE in dex.ini and we need to export the currency correctly to Excel. $ is exporting but not English pound.
Thanks!
June 8, 2017 at 11:50 AM
Mariano Gomez said…
Mey,
What version of GP are you referring to? Is this still GP 2013? I know this problem has since been fixed in GP2015 R2 and above.

MG.-
June 12, 2017 at 6:35 PM
Mariano Gomez said…
Anonymous,
Not sure there's much I personally can do. It will depend on Microsoft to improve the export capabilities of SmartList on Web Client. If something does not meet your requirements, I believe you should be posting it on connect.microsoft.com for the Product Management team to review. Get people to vote on it.

MG.-
June 12, 2017 at 6:37 PM
Unknown said…
Hi All,

GP2016-620(R2) I am having similar issues in Samrtlist designer where most of the columns are changed into the different type of data after exporting to Excel where in smartlist it is ok, once exported to excel all the data is shared and codify in a strange manner below are few examples:
=DATE(2018,1,1),(suppose to be doc date )
#NAME? (suppose to be Account Description)
#NAME? (suppose to be TaxDetail Id)
0.00000 (suppose to be A Negative number) (-9000.00)

to mention there is no special character used anywhere in data

apart from this smart list is not sorting the data column by clicking on a column heading a usual practice, any idea what is going on
February 28, 2018 at 7:55 AM
Mariano Gomez said…
Verify you DO NOT have present a forms dictionary for Smartlist (EXP1493F.dic), in the path indicated by your DYNAMICS.SET file. If so, rename it and relaunch GP to see if this fixes the problem.
February 28, 2018 at 5:01 PM
Unknown said…
Has anyone posted a fix for the "columns shifts on the Excel export if you use the SmartlistEnhancedExcelExport=TRUE"?

I saw Pam Robertson posted kink to a suggested fix on connect.microsoft.com but that site has been retired.
March 12, 2019 at 3:13 PM
Post a Comment

Popular posts from this blog

DBMS: 12 Microsoft Dynamics GP: 0 error when updating to Microsoft Dynamics GP 2013 R2

Image
Hello all! It's been quite a while since I've actually written anything of remote interest to anyone who follows my blog, but at the same time, you will be pleased to know that I've been quite busy in the consulting front, with upgrades, server migrations, complex multi-instance postings involving 10's of thousands of transactions, wrapping up some Field Service Automation projects, and the list goes on and on. This time around I want to bring to the forefront, an issue I encountered updating  from Microsoft Dynamics GP 2013 SP2 to Microsoft Dynamics GP 2013 R2 plus the latest service pack. Background My client requested a server migration to a new environment where they wanted to deploy Dynamics GP 2013 R2 web client (plus the latest service pack) and upgrade their relational database management system to Microsoft SQL Server 2014. This is something I'm absolutely comfortable with (for the most part) given also that my client was sitting at Microsoft Dynamics
Read more

Cannot insert the value NULL into column 'CONTACT' error when clicking on Items List in Navigation Pane

Image
Moving on from my previous article on a similar subject - see Cannot insert the value NULL into column 'BASEUOFM' error when clicking on Items List in Navigation Pane , I recently came across this error,  Cannot insert the value NULL into column 'CONTACT' when clicking on the All Purchasing Transactions list under the Purchasing Navigation Pane option, after performing an upgrade from Microsoft Dynamics GP 9.0 to Microsoft Dynamics GP 2010 R2. All Purchasing Transactions list error - Purchasing Navigation List The name of the global temp table - in this case, tempdb.dbo.##2093338- varies in almost all cases, but the end result of the error is the same. The issue has been identified running Microsoft Dynamics GP 2010 RTM, SP1 or SP2. Upon further review, the issue is due to bad data in the Vendor ID (VENDORID) column in the Purchasing Receipt History table (POP30300). In summary, if you have a purchasing receipt with a blank vendor ID or a vendor ID that doe
Read more

How to uninstall Microsoft Dynamics GP 10.0 if you cannot uninstall it by using the "Add or Remove Programs" feature

INTRODUCTION This article describes how to uninstall an existing Microsoft Dynamics GP v10 system if you cannot uninstall it by using the Add or Remove Programs feature (or Programs and Features in Windows Vista) in Control Panel. It is recommended that you verify that you cannot uninstall by using Add or Remove Programs first. MORE INFORMATION First, verify that you cannot uninstall Microsoft Dynamics GP by using Add or Remove Programs (or Programs and Features) To verify that you cannot uninstall Microsoft Dynamics GP by using Add or Remove Programs (or Programs and Features in Windows Vista), follow these steps. For Windows XP or Windows Server 2003: 1. Click Start, click Run, type control appwiz.cpl in the Open box, and then press ENTER. 2. Click to select Microsoft Dynamics GP 10.0 product from the application list, and then click Remove. For Windows Vista: 1. Click Start, type Programs and Features in the Search box, and then press ENTER. 2. Click to select the product to be un
Read more