Exporting Negative Numbers from OBIEE 11.1.1.7.0 to Excel 2007+

Brian BurdiTechnical Tips2 Comments

Exporting Negative Numbers from OBIEE 11.1.1.7.0 to Excel 2007+

If you’re trying to export numeric values in OBIEE 11.1.1.7.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.

The Problem:

Currently, if you have all currencies set in OBIEE 11.1.1.7.0 to this:

IMG4

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):
IMG2
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):

IMG3

If the user wanted to sum the right column, Excel will disregard the negative values and only sum the positive values.

The solution:

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.

IMG4

Then click on the “Conditional Format” tab and select “add condition”:
IMG5

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”:
IMG6

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).
IMG7

Select Ok and make sure to save the analysis. This will fix the export issue to Excel 2007+ issue from OBIEE.

2 Comments on “Exporting Negative Numbers from OBIEE 11.1.1.7.0 to Excel 2007+”

  1. Yes, that is true that it does not hold true for column totaling. This solution was designed for users who were interested in doing all calculations outside of OBIEE using excel. This was a proposed work around to a bug that was fixed in 11.1.1.7.1. I thought it would be easier to reformat one number rather than Oracle’s suggestion to ‘Reformat the cells in Excel [that are in a text format]’. In my opinion Oracle’s work around would be better if most calculations were being computed within OBIEE and this work around would be better if the user wanted to do the calculations themselves (which prompted this solution).

    Thank you for reading this post and taking your time to comment on it.

Leave a Reply to Brian Burdi Cancel reply

Your email address will not be published. Required fields are marked *