General OBIEE

Get Involved. Join the Conversation.

Topic

    Stephanie Gott
    Showing '0' for Null Value
    Topic posted May 10, 2018 by Stephanie GottSilver Medal: 2,000+ Points, tagged Analyses, OBI Answers, OBIEE Answers, OTBI 
    5393 Views, 11 Comments
    Title:
    Showing '0' for Null Value
    Summary:
    How do you show a 0 value in place of a blank value on an analysis?
    Content:

    Hello,

    We want to show the total number of something on an analysis (in this case Leads). If there were no Leads created on a day, we want to show '0' but instead our analysis is showing a blank value. I checked the box to "Include Null Values" and tried a case statement. It still shows blank value. See attached screen shots. Also, we need the actual value of 0 rather than just a cosmetic formatting issue. We need to be able to show the average number of Leads at the end of the analysis.

    Any thoughts?

    Thanks, Stephanie

    Document:

    Comment

     

    • Gopinath Kartheesan

      You can try "Edit Formula" for that column and use "IF-THEN" condition to replace NULL with 0.

       

    • Pras Ramakrishnan

      Hi Stephanie,

      Use 'ifnull()' function to replace null with 0. You can do this by editing the formula on the field on which you want this to happen. For e.g., if the field name is 'FLD_NAME', the formula would be ifnull(FLD_NAME,0).

      Thanks,

      pras

    • Stephanie Gott

      Sorry. We tried that as well. It doesn't work.

      • Pras Ramakrishnan

        Could it be a datatype issue? ifnull() works for me with both string and numeric data types without any additional conversion.

        Thanks,

        pras

    • Gopinath Kartheesan

      Instead of using IS NULL in your Case-When statement, use  = '' and see if that works.

       

      Regards,

    • Aaron Leggett

      Hi Stephanie,

      Have you tried transforming the data type? I sometimes come across similar issues and I will CAST it as an alternative datatype (VARCHAR, INT or FLOAT). Sometimes I would even transform it into a varchar and then back into an integer, so sort of like a nested cast function. I would then use this in conjunction with a CASE WHEN statement.

      Alternatively, have you tried CASE WHEN "Lead Facts"."# of Leads" >= 1 THEN "Lead Facts"."# of Leads" ELSE 0 END ? This could work, but I think you might also run into the same issue.

      It tends to be the logic configured in the RPD that dictates how the column behaves. For example, our Project Costing SAs track employee project costs, but only if they're active (therefore not giving us a true reflection of project costs if an employee leaves the company) We're a cloud customer, so cannot change our RPD config, but if you have an on-premises version you could ask your Oracle DBA to look at it and see if they can change it to bring back 0 rather than null.

      Kind Regards

      Aaron

       

    • Melissa

      I used the following expression to populate zero for employee number null values.  In the report, if employee number does not exist on the profile, 0 is returned. If employee number does exist, employee number is returned.

      IFNULL("System User - Personal Information"."User Employee NO",0)

      -Melissa

    • Shawn McDonald

      We had a similar issue with some performance titles and a dashboard. We were counting the number of employees for each rating and when no employees had that rating we got the 'No Results'  - we wanted a zero to display

      We added a union to the Subject Area (the same SA and filters as the original analysis) and used the formula CAST('0' AS INT)

      Hope this helps

    • Shakher Sharma

      Can you try following and see if this works for you:

      • Click on the gear icon next to column name.
      • Select Column Properties.
      • Go to Data Format tab.
      • Select Override Default Data Format.
      • Select Custom as value of Treat Number As list of values.
      • Enter #,##0;-#,##0;0 as custom numeric format.