Casting error when a measure/key figure has mixed data types in SAP BW when using the TIBCO Spotfire Connector for SAP BW

Casting error when a measure/key figure has mixed data types in SAP BW when using the TIBCO Spotfire Connector for SAP BW

In SAP BW, if there is a column of double/numerical type, the results of 'Division by zero', 'Error', 'Numeric Overflow' are by default replaced with the character string 'X' (as seen in screenshot3.png) which will make the column data type as a mixed type containing numbers and the string "X" character. TIBCO Spotfire always expects a measure/key figure column to have only a single data type like numeric, dates, times etc (see Key Figures for reference).

When trying to load such mixed data type columns in to TIBCO Spotfire, they will fail with following error:

System.ArgumentException: Input string was not in a correct format.Couldn't store <X> in [Measures]. [006NZK4HRQJGSJ2LFQPFHSC62] Column. Expected type is Double. ---> System.FormatException: Input string was not in a correct format.
at System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo numfmt)
at System.String.System.IConvertible.ToDouble(IFormatProvider provider)
at System.Data.Common.DoubleStorage.Set(Int32 record, Object value)
at System.Data.DataColumn.set_Item(Int32 record, Object value)
— End of inner exception stack trace —

  Below are two options to help resolve the issue:

1) If your version is one of 7.11, 7.13, 7.14, apply the TIBCO Spotfire Analyst hotfix as below or use a higher version:

• 7.11 HF-010
• 7.13 HF-002
• 7.14 HF-002

These fixes check for any unexpected values (eg: 'X') and converts them into null, so that the column from SAP BW can be imported without any error

2) If you have access to the SAP BW front end system, you can make the following change to the default value in order to get this working in other TIBCO Spotfire versions.

• Open SAP BW system, Press F5 which gives you 'Display IMG' as seen in screenshot-1.png
• Expand 'Sap NetWeaver' > 'Business Warehouse' > 'Settings for Reporting and Analysis' > 'General Settings for Reporting and Analysis' 
• There is a 'Display of Exceptional Values in a Query Result' entry
• Click on the 'IMG Activity' icon (you will get view as shown in screenshot-3.png) and change the value to some double/numerical type value which matches the column data type.

Doc: How to display IMG activity

• https://espressotutorials.wordpress.com/2016/03/23/tip-how-to-display-img-activity-or-transaction-code-in-spro/