Casting error when a measure/key figure has mixed data types in SAP BW when using the TIBCO Spotfire Connector for SAP BW
Article ID: KB0080710
Updated On:
| Products | Versions |
|---|---|
| Spotfire Analyst | 7.14 and lower |
Description
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 —
Resolution
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:
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.
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
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.
Issue/Introduction
A key figure/measure column from SAP BW with mixed type values or types other than numerical type fails to load with error 'System.ArgumentException: Input string was not in a correct format'
Additional Information
Doc: How to display IMG activity
Feedback
Yes
No
Powered by
