OBIEE Online Training

Friday, June 10, 2011

How to Replace Null Values in an OBIEE Pivot Table?

How to Replace Null Values in an OBIEE Pivot Table

It's simple tip that allows you to supress 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.


Its a bug in OBIEE11g

I have raised SR and confirmed it defect obiee11.1.1.3.version but after obiee11.1.5 released also that major bug is not yet resolved that why i am just wondering
fyi:
Fix by 11.1.1.6.1 as mentioned in the Bug.
Bug 13054445 [http://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=13054445] - REPLACING NULL VALUES WITH "0" IN AN OBIEE 11G PIVOT TABLE IS NOT WORKING
for more refer :
https://forums.oracle.com/forums/thread.jspa?threadID=2319791


Regards,
Deva

3 comments:

  1. Hey,
    Great post!
    Is there a fix for this to show this for a percentage?

    ReplyDelete
  2. Hi,

    I have raised SR and confirmed it defect obiee11.1.1.3.version but after obiee11.1.5 released also that major bug is not yet resolved that why i am just wondering

    fyi:

    Fix by 11.1.1.6.1 as mentioned in the Bug.

    Bug 13054445 [http://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=13054445] - REPLACING NULL VALUES WITH "0" IN AN OBIEE 11G PIVOT TABLE IS NOT WORKING

    for more refer :

    https://forums.oracle.com/forums/thread.jspa?threadID=2319791

    THnaks

    Deva

    ReplyDelete
  3. Hi Deva..

    Adding to your post a small knowledge share..

    People asking the reverse criteria as i faced it in on of my projects.

    Replacing zero with Nulls(Empty Space.

    Use this #,###;-#,###; in the data format which will give empty space in the place of '0'.

    Please ignore if you already knew it.

    Thanks & Regards,
    Rushi.

    ReplyDelete