OBIEE 11.1.1.9.5 'Include null Values' — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OBIEE 11.1.1.9.5 'Include null Values'

Received Response
104
Views
12
Comments
user13816768
user13816768 Rank 3 - Community Apprentice

Hi all,

We are creating graphs, pivots in the obiee analystics (11.1.1.9.5) in the new releases we have option of showing rows with null values also.

But is there a way to print zero in the place of null?

The requirement is to show 0 instead of nulls for the months if there is no data.

Because of showing nulls, the lines on the graph are splitting because of no data.

Please help.

Graph With null values.png

Graph With 0 values.png

«1

Answers

  • choracy69
    choracy69 Rank 6 - Analytics Lead

    Hi,

    Try to use Conversion Funstions -> IFNULL.

    IFNULL(column measure, value)

    For your measure:

    IFNULL("Resolved Cases", 0)

    pastedImage_0.png

  • user13816768
    user13816768 Rank 3 - Community Apprentice

    Hi choracy,

    I am using option of 'Include Null Values' in the pivot/graph.

    Nulls are printing but not able to print 0 by using ifnull function.

    Thanks.

  • IFNULL will not work because the data is not null, it doesn't exist (and it's different).

    So the formula with IFNULL is not evaluated for "nulls" values (the physical query retrieve the dimensions attributes in a different query than the fact so dimensionality attributes aren't restricted by the facts).

    On the screen in a table / pivot you can display the 0 by setting a custom data format mask, just not sure it will render the way you want in the chart.

    Just keep in mind your data is not NULL, it doesn't exist at all ...

  • user13816768
    user13816768 Rank 3 - Community Apprentice

    Hi Ceresa,

    You are 100 % right, the data not existed at all..but the problem is we were able to show null, the question from the users will be ... if you are able to show nulls.. then why can't we show 0 in that place.

    I agree with you completely,,, but need to find a way to print it.. Thanks for the time..

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    What Gianni is telling you is that "Showing" sometbing as null and actually technically RETRIEVING null is something completely different.

  • user13816768
    user13816768 Rank 3 - Community Apprentice

    Hi Berg,

    Yes, I got that.. Thanks for looking into this..

    Thank you..

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    So the result is that you may need to remodel your models in order for queries to be spawned which manage the ifnull part and you actually have physially existing nulls (not zeros...that's a completely different answer btw) in the actual result set

  • The name of that checkbox is wrong as (we all agree on that) you are not retrieving NULLs values but inexistant values.

    As I said in a table/pivot you can display 0 by setting a custom format mask on the value, on the chart I suspect it has no effect at all (didn't test it, so give it a try ....).

    The only other option to do what you look for is to get some real data in your source. So store NULL or 0 in the fact table and they will be on screen. Or change your model to generate that data in the physical query (but going by this way open the door of cartesian products etc.).

    EDIT; as often thinking and writing the same thing as Christian with few seconds delay

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Hahahaha that always looks like

    {quote}

    WhatChristianJustSaid --verbose

    {quote}

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    For DW design - minimize null values in your fact tables and based on use of information (your specific case) don't skip rows at the granular level.  So in ETL/ELT where a certain measure doesn't apply to the grain - write in the zero row ... only do it for the fact tables that need to support that type of use.  Your case matches this principle...