OBIEE Analysis Date Format as 0/0/0 [Solved]

Issue: We recently came across an error where client raised a defect that date comes up as 0/0/0 for some of the records.

Analysis: First we checked the particular records in the EDW target schema. We could see that the records are having date as null in the physical tables.

Cause: The root cause of this defect is that in the physical layer of the RPD for this particular column the nullable flag is not ticked. Since the nullable flag is not ticked, OBIEE expects a value here and substitutes 0/0/0 12:00:00 AM for null values.

The nullable flag property in OBIEE RPD Physical column object.

This could also happen if you manually change the data type from datetime to date. OBIEE imports date as datetime by default.

Resolution: Change the data type to datetime and tick the nullable flag. Once done, save and upload RPD to the server. Now clear your cache, reload files and metadata and check again. If that still doesn't change anything then do a restart of your presentation service.

Replace NULL with Decimal 0.00 in OBIEE Pivot Table

Issue description: Replacing null code for pivot table not showing 0 with decimal places. 

Solution: As you all know the solution is very widely known to all. It is as simple as going to the corresponding measure, click on properties, go to Data Format, select custom format and paste in the following code,

#,##0;-#,##0;0

The problem with the above code is that it will not help you show decimal places, to show decimal places all you have to do is to change the code as below,

#,##0;-#,##0.00;0

note that I have replaced the second 0 as 0.00 which will make sure that data in the column will have two decimal places. Again places where there are null this doesn't apply and will show up as 0 instead of 0.00. So to correct this all you have to do is to replace the value after the second ; as 0.00 as below,

#,##0.00;-#,##0.00;0.00

This will ensure that null values are replaced by 0.00 and not 0. Also not null values will have two decimal points. 

Let us know if this worked out for you. Works well for OBIEE 12c.