Oracle Transactional Business Intelligence

Products Banner

Showing '0' for Null Value



How do you show a 0 value in place of a blank value on an analysis?



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


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


  • 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).



  • slgott
    slgott ✭✭✭

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

  • slgott
    slgott ✭✭✭

    Thank you. We tried this with the Case statement. See screen shot. It doesn't work. Thanks.

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



  • 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



  • 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)


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




  • 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

  • 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.
  • slgott
    slgott ✭✭✭

    We tried this too. It doesn't work for us. If it did, I think it would be cosmetic only. Thanks though.

  • slgott
    slgott ✭✭✭

    I think you may be right. It could be a data type issue in the database. We will probably have to log an SR. Thanks!