How to Replace Null Values in an OBIEE Pivot Table

Posted on October 14, 2010 by Paul McGarrick
Filed Under 10.x Answers, 10.x Dashboards, OBIEE 10

See also this update (Jan 2012).

Here’s a 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.



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!

Enjoy.
Paul

Comments

21 Responses to “How to Replace Null Values in an OBIEE Pivot Table”

  1. nag on October 19th, 2010 12:36 pm

    hey paul,

    A pretty solution.

    -nag

  2. John Minkjan on October 19th, 2010 1:14 pm

    Nice one Paul!

    Regards

    John

  3. jeevan on October 19th, 2010 2:28 pm

    Thanks Paul. This helped.

    I’ve a similar need to show NULL values as a 0 for CHART.

    Any idea how we can do it?

    Thanks,
    Jeevan

  4. Paul McGarrick on October 19th, 2010 5:12 pm

    Hi Jeevan

    In order for a different value to be passed to the charting engine you will need to edit the column formula. Try replacing the measure with IFNULL(measure_name, 0) but beware that this will slow your report down. Good luck!

    Paul

  5. jeevan on October 19th, 2010 5:25 pm

    Thanks for the suggestion Paul. But my requirement is slightly different. let me explain.

    I want to draw a chart [ line graph ] based on
    Date [x axis], # of Activities [ y axis], Activity Type [ legend ]

    Assume the Data is like this

    Date;Activity Type;# of Activities
    9/2009;A;2
    9/2010;B;3
    10/2010;A;2
    10/2010;B;3
    10/2010;C;4
    10/2010;D;5

    Now when I draw a line graph for Date [x axis], # of Activities [ y axis], Activity Type [ legend ]
    the graph is broken for Activity Type C & D on 9/2010 – because there is no data for that [its NULL]

    Is there a way I can substitute 0 for NULL in this scenario – In other words, the chart should be equivalent to a data of this fashion:

    9/2009;A;2
    9/2010;B;3
    9/2010;C;0 [this row does n't exist in result set ]
    9/2010;D;0 [this row does n't exist in result set ]
    10/2010;A;2
    10/2010;B;3
    10/2010;C;4
    10/2010;D;5

    The purpose for me is to have an “unbroken” line graph even if I dont have fact data.

    Let me know your thoughts, Paul

  6. Paul McGarrick on October 19th, 2010 7:50 pm

    Hi Jeevan

    I think John describes this on his blog. You would have to use ‘combine with similar request’ to get a complete dataset.

    Paul

  7. Pravin on October 21st, 2010 1:43 pm

    Hi Paul, I am have similar issue as of Jeevan.
    Both options didn’t worked. With using data formatting table view I am getting 0 in place of NULL.
    But in line chart it is still broken.

  8. saichand on November 18th, 2010 1:26 pm

    Hi Paul,
    Good one….

  9. Mindaugas Mindaugas on March 7th, 2011 2:57 pm

    Thank you! Where in official documentation I could find about custom formats?

  10. Paul McGarrick on March 7th, 2011 3:32 pm

    As far as I know it’s undocumented.

  11. Jakub on June 21st, 2011 6:45 pm

    Hi Paul,
    I have a problem with your solution: if a have a number format #,#0;-#,#0;0 and there is a long number in the column, the result is for example 12 345 678 – not 12 345 678,90
    Dont you know, how can I solve this problem ?

    Thanx

  12. Anzy on August 24th, 2011 7:57 am

    Hi Paul,

    Thanks Paul.But my requirement is to show null values for a calculated column in pivot table(calc column in the sense data is expressed as a % of page value).

    Will you please help me to find a solution for this.

    I have one more issue regarding pivot table customization.Let me explain this scenario.

    suppose my data is like

    for branch1:
    doument #of request sum percentage(%)
    doctype1 1 2 3 0 6 12.76%
    doctype2 2 3 4 5 14 29.78%
    Sum 3 5 7 5 20 42.55%

    Total for branch1: 20
    for branch2:
    doument #of request sum percentage(%)
    doctype1 4 2 3 0 9 19.14%
    doctype2 2 7 4 5 18 38.29%
    Sum 6 9 7 5 27 57.44%

    Total for branch1: 27
    Grand Total for two branch:47

    The % column in the pivot table is the sum values expressed as a % of the grand total for two branches.

    I am stuck at this point .How to generate this % column.
    Please help to solve this issue.

    Thanks in Advance
    Anzy.M

  13. asha on January 4th, 2012 9:51 am

    very good solution….

  14. muthu on January 19th, 2012 10:19 am

    HI all,
    i need to display the Phone number 9990009990 as +09990009990 and to download in the exact format in an xls file

  15. Paul McGarrick on January 19th, 2012 12:41 pm

    Sounds like you should convert it to a string and append the + symbol at the beginning. Regards, Paul

  16. muthu on January 23rd, 2012 7:23 am

    Hi Paul,

    thanks for your reply, but i have given the column as varchar2 and while displaying in the OBIEE Answers it is showing exactly as in the given format ie., +09990009990 but while downloading it to the excel format using the download option it is downloaded as 9990009990.

    thanks in advance,
    muthu

  17. Paul McGarrick on January 24th, 2012 6:06 pm

    Add a second, duplicate version of the column to the report and change the formula so that it is prefixed with a single quote. Hide this column on the page somehow if you don’t want the users to see it. When the data is exported I hope that the single quote will fool Excel into keeping the formatting. I haven’t tried this so it’s mere speculation – good luck with it! Paul

  18. muthu on January 31st, 2012 11:57 am

    Hi paul thanks,

    I have tried your solution it worked. i duplicated the view and override the default format as custom text and added a single colon before it. I got the output as ‘+9990009990 and while downloading also i got it as same. But my doubt is that while i am overriding the default format and adding single colon in the duplicated view it is also reflecting with single colon in all the views and even if i delete the duplicated view its still the same.Is there anyway of making the change in the duplicated view and the change not reflecting in the original view.

    Regards,
    Muthu

  19. Paul McGarrick on February 8th, 2012 11:51 pm

    Hi Muthu, I meant that you should duplicate the column and change the formula to concatenate this single quote rather than change the default format. I guess you will have figured out a workaround by now. Regards, Paul

  20. saurav on March 19th, 2012 3:26 pm

    Hi Paul,

    I am not able to put the column value in total by colunm in a pivot or table view.
    Eg.
    Total by Branch 1 ——– 12345
    Total by Branch 2 ——– 45678
    I can edit the caption and put “Total by Branch” but unable to capture the value 1 or 2.

    Regards,
    saurav

  21. Paul McGarrick on March 19th, 2012 4:11 pm

    Hi Saurav. Go to format labels and insert the @ symbol to refer to the data value. Cheers, Paul

Leave a Reply