[OBIEE 10g] Replacing Null Values In Pivot Table

 

Replacing Null Values In Pivot Table in 10g

Here’s a simple tip that allows you to suppress null values in a pivot for cells which have no data.

Create a simple report. I’m using the Samplesales RPD here.

A pivot table view of this data would look something like this by default:

Now switch back to the criteria tab and edit the ‘column properties’ associated with your fact measure. Choose the ‘Data Format’ tab, tick to override the default format and choose ‘Custom’.

It seems that the syntax for this custom format is positive-value-mask (semi colon) negative-value-mask (semi colon) null-mask. So this means we have a few options.

E.g. if you want zeros (0) instead of null then enter:
#,##0;-#,##0;0

If you want a dash/strike (-) then you could enter:

#,##0;-#,##0;-

Or if you want to add a custom message then something like this would work:

#,##0;-#,##0;no data Our pivot table now shows something other than null values.

One thing to note is that you should be careful when using 0 in place of null. Make sure it makes sense in the context of your data. Null is not the same as zero!