I have run into issues in support where people are attempting to use the “Export to Excel” feature in SmartList, and receiving an error that says something like, “We found a problem with come content in “My File Name.XLSX”.  Do you want us to try and recover as much as we can?  If you trust the source of this workbook, click Yes.”   The message will look like this. 

The message occurs exclusively with reports that are built in SmartList Builder. 

You will often see strange characters in the SmartList before you export it for specific rows, these special characters are what is causing the export to fail. 

What causes these records are NULL results from the query.  You normally will never see this with default GP SmartLists because Microsoft Dynamics GP does not allow nulls in any table, each field always has a default value. 

The typical reason you see this only in SmartLists built with SmartList Builder is that when you are creating custom joins, depending on the join types you use, you can end up with certain fields returning a NULL.  Not every record will display the special characters, and often times it’s a different record every time you refresh the SmartList, however, the root cause of it is the field returning a NULL value, and SmartList not knowing exactly how to display it, since in GP, a NULL is impossible. 

The easiest way to resolve this is to use a calculated field in SmartList Builder.  In my case, in the above screenshot, you can see that the field “Favorite Color” is sometimes displaying special characters which will cause my export to Excel to fail. 

First, you want to open the report in report writer and then click on the “Calculations” button at the top of the window. 

You need to give the field a name, and also define a field type.  In my case, I know the field is always going to be a string.  I have named my field “CALC_COLOR” and defined it as a string. 

The calculated fields in SmartList Builder use SQL script.  We want to convert any NULLS in this field to blanks.  The Syntax in SQL for this would be:  ISNULL (value, replacement value)

So in SmartList Builder you want to type in INSULL( and then double-click on the field you are using the in the calculated field. 


Resulting in this:

Then you complete the rest of the syntax, which in this case would be ISNULL({CUSTOMERDATA : Favorite Color},”)   The last character there is 2 single quotes and not a double quote.  The 2 single quotes next to each other are blank in SQL.  The window should look like this when you are finished. 

Click on the Validate button and make sure you get a message box saying the script is valid.  Then hit the save button. 

Then back in your SmartList you are going to want to UNMARK the “Display” and “Default” checkboxes for the original field, and then mark them on the newly created calculated field. 

Now the field will not display the special characters in the SmartList, and the report will also be able to be exported to Excel without error.