OBIEE Cross Tab with missing cells data in database (or) Suppressing Null values in OBIEE Pivot

Standard

Hello Folks,

After long time… 🙂

Well, I’m using Pivot view to show some column values across in cross tab. And, some other column in rows part of pivot.

For column and rows combination if measure value present in database it shows, if not it displays blank value (null).

You can’t suppress this null even by using ifnull(measure, 0)

Work around I found:

This will not show zero, instead strikethrough (-), which is acceptable and meaningful.

Here we go..!!

  • Here is scenario based screenshot. Makred cells are null and no data in database for particular row & column combination values.

  • Go to column properties > Conditional Format of measure column
  • Add Action > Measure Column and add the following filter (isnull) as shown

  • In Edit Format window, select and apply Strikethrough from the Effects option dropdown.

  • Here is the result

Blanks are replaced with hyphen ().

Thanks Everybody,

Kishore

Advertisements

About Kishore & Naresh

Hi.. This is a blog operated by Kishore and Naresh. We are colleagues and working as BI Developers. Here we want to share our ideas and researches here. We were the active members of OTN Forums and inspired with Venkat and John's blog. We may not reach to that level, but we brings up all our ideas and research work we done. All the views expressed here are our own and does not reflect the views of our employers. We need all your blessings and support to grow. Thanks

8 responses »

  1. Thank you. Worked. And I think this method is much faster than any SQL function, because it does formatting only for presestation, not when selecting data from database.

  2. Hi,

    I am just getting started with Oracle BI; I cannot seem to find column properties for any of my Pivot Measures. Do you mind providing more details on how I can format the Measures?

    Phiri

  3. I am new to Oracle BI and have been trying to replicate what you did without any success. I have a measure that I calculate by counting a field using “COUNT DISTINCT” aggregation rule. Unfortunately, I can not seem to find the column properties in the PIVOT view.

    Please help.

    Phiri

  4. Overriding column default format to

    #,##0;-#,##0;0 —-> Nulls are displayed as 0’s
    #,##0;-#,##0;- —-> Nulls are displayed as -‘s

    Regards,
    Ashok

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s