Fix to SSRS Export to Excel error


A recent client was troubled by an error message from Excel 2013, stating the file contained unreadable content, when trying to open Excel files that were exported directly from SSRS 2012 reports.   The reports were built off of an Analysis Services 2012 cube with a large number of MDX calculations baked into it.    (The condition also appears for earlier versions of SQL Server and Excel).

Annoying to say the least.  Once the message box with the error message, however, the Excel file would open, but an end user would not be sure / confident of what was removed by excel by answering yes to the dialog.   Even as the developer and knowing every row going into the report during development, I could not be sure exactly what Excel was doing.   This was one of the error conditions that you will encounter and have to chase down any and all blog entries that were remotely close to the condition you've hit. 

Excel Error 

Message Text:  Excel found unreadable content in 'Merchandising Dashboard.xlsx'.   Do you want to recover the contents of the workbook?   If you trust the source of this workbook, click 'Yes'.



The error was consistent.   It was triggered only by calculated columns in the report using SSRS expressions, and these mostly were percentage calculation.   The base values for the calculations from the source dataset would behave as expected.   The calculations would listed the value of Infinity or NaN (not a number) in the column.   That was it.  In the HTML viewer for the report in the SSRS Report Manager portal, you would not receive and error message.   You would see the values of Infinity or NaN listed in the output.  Excel is far more pickier as it was assigning a numeric data type to the column, and was encountering string values in that column.    

So why are we getting Infinity or NaN?    If it was a simple divide by zero, I could deal with it, but we were still hitting the error, even after using IIF in the expression to check for zero.          

The output window in SQL Server Data Tools was also highlighting fields in the dataset that were identified as missing.   
...
[rsMissingFieldInDataSet] The dataset ‘MainQuery’ contains a definition for the Field ‘Net_Sales_Units’. This field is missing from the returned result set from the data source.
...

The result set from the MDX query would sometimes return a rsMissingFieldInDataSet condition.  Most notably from a MDX calculation where it would not return a value.   Example would be ending inventory when the filter specification would not have a value for the slice of cube data requested.   No value or nothing was received.   The IsMissing property or ISNOTHING function was good is handing it.   It was the ISNOTHING function that I had better experience with in the SSRS Expressions.        


A solution

The eventual fix in the SSRS column expression was a little bit cumbersome, but was very effective. 

=IIF(SUM(Fields!Net_Sales_Value.Value)=0 or ISNOTHING(SUM(Fields!Net_Sales_Value.Value)) 
or Sum(Fields!Net_Sales_Value.Value, "Shape_Design_Name") =0  or ISNOTHING(Sum(Fields!Net_Sales_Value.Value, "Shape_Design_Name")),0,
SUM(Fields!Net_Sales_Value.Value) / Sum(Fields!Net_Sales_Value.Value, "Shape_Design_Name"))

You had to check for a zero value or where the value is nothing (IsNothing), before actually performing the calculation.    Once you did that, the excel error condition did not reappear.  


Another option

Another option instead of coding each expression with the actual statements to handing the condition was to use a function inside of SSRS.   This is not the function to the example listed above, but a generic example of how to code

Example 1

Function code:

Public Function GetValue(field as Field) as Long
  If (field.IsMissing) Then
    Return 0
  ElseIf (IsNothing(field.Value)) Then
    Return 0
  Else
    Return field.Value
  End If
End Function


Function call from SSRS Expression

=Code.GetValue(Fields!Internet_Order_Quantity)

Reference:
http://blog.hoegaerden.be/2009/07/06/ssrs-and-mdx-detecting-missing-fields/


 Example 2

Function code:

Public Function GetDeltaPercentage(ByVal PreviousValue, ByVal CurrentValue) As Object
    If IsNothing(PreviousValue) OR IsNothing(CurrentValue) Then
        Return Nothing
    Else if PreviousValue = 0 OR CurrentValue = 0 Then
        Return Nothing
    Else
        Return (CurrentValue - PreviousValue) / CurrentValue
    End If
End Function


Function call from SSRS Expression

 =Code.GetDeltaPercentage(Previous(Sum(Fields!Sales.Value),"ColumnGroupByYear"), Sum(Fields!Sales.Value))

Reference:
http://msdn.microsoft.com/en-us/library/ms157328.aspx