NULL values in OTBI

Sylvia Wong

I think it is generally best practice to suppress NULL values in OTBI, but client wants to keep them in report. But in their case, it's selective. Can we enable null values for just one column, or is it a global setting?

For example, if employee has no leave of absence records, it would be blank, but all the other fields in the report would show is there is data to pull from.

Riyaz Ali-Oracle

OTBI does allow selective null value inclusion rather than applying a global setting across all columns. You can enable null values for just one column using View Properties in the Results tab.

1. Go to the Results Tab
2. Modify View Properties (Click on the table or pivot view where you want to display null values. Select View Properties.)
3. Enable Null Values for the Specific Column (Look for the option "Include columns with only Null values" and enable it.This ensures that the selected column retains null values while others follow default suppression rules.)
4. Use Conditional Formatting (If required)

you can apply CASE statements in the formula:

sql
CASE WHEN "Leave of Absence"."Record" IS NULL THEN 'No Data' ELSE "Leave of Absence"."Record" END
This ensures that only the Leave of Absence column retains null values while others remain unaffected.