If you’re trying to export numeric values in OBIEE 220.127.116.11.0 to Excel 2007+, you may notice there are issues when you attempt to sum a column that has negative numbers. This problem will occur if you have a negative number’s font color set to red. Excel 2007 disregards those values when directly exported whereas if you export to Excel 2003 you will not have this issue but will lose some of the formatting from OBIEE.
Proposed is an easy solution to implement that will allow users to have useful data and not be limited to where they can export to.
Currently, if you have all currencies set in OBIEE 18.104.22.168.0 to this:
When the user exports their data to Excel 2007+, it treats the negative numbers as text values. This causes the user to calculate incorrect values.
With the proposed solution we will have output that is identical. The solution is in the column “Invoice Line Amount” and the form that doesn’t export correctly is “Invoice Line Amount 2” (results in OBIEE are shown below):
When the user exports this report to Excel 2007+, the column on the right will cause errors. These errors are indicated by the green triangles in the corner of the negative cell (shown below):
If the user wanted to sum the right column, Excel will disregard the negative values and only sum the positive values.
The first setting that will need to be fixed is the currency setting; instead of having the negative values automatically turn red we will set it to a black font.
Then click on the “Conditional Format” tab and select “add condition”:
Once you select that option, select the column that you’re interested in. For this case I will be selecting “Invoice Line Amount 2” and setting the condition as Operator = Less than and Value = 0 and then select “Ok”:
The next window that will be prompted is an edit format option. We will select the font color to be Red and once you change these settings the Conditional Format tab should reflect the changes made (shown below).
Select Ok and make sure to save the analysis. This will fix the export issue to Excel 2007+ issue from OBIEE.