Replace Null values in Pivot table

To replace the null values in Pivot table  can be done using several method  some method are as follows  :

Using Column Properties of column in answers   :

        1)      Go to criteria tab and edit the ‘column properties’ associated with your fact measure. 

 

                               2)    Now choose the ‘Data Format’ tab, tick to override the default format and choose ‘Custom’.

                   

 

               3)  Write following custom formula for replacing Null to zero :

  1)          if you want zeros (0) instead of null then enter:

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

  2)      If you want a dash/strike (-) then you could enter:

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

  3)      if you want to add a custom message then something like this would work:

                       #,##0;-#,##0;no data

  4)      if you want zeros (0) instead of null in decimal format :

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

  5)      if you want zeros (0) instead of null with $ sign :

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

  6)      if you want zeros (0) instead of null with % sign :

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

 

 Using Edit Formula of column in Criteria tab: 

1)      Go to criteria tab and edit the Edit Formula associated with your fact measure.

 

2)      Write Following formula  using case function: 

                    CASE Column   WHEN NULL THEN 0.00 ELSE Column END

                         

  Using Admin Tool:

1)      Double click on logical Table source -> Column Mapping ->Edit expression

  

   

2)      Expression builder select the IfNull Function as shown in fig  :

    

3)      Write the formula as below & click OK :

IfNull (Column ,value )

 

  It’s a bug in OBIEE11g

  In  OBIEE 11g it not working . It’s defect in obiee11.1.1.3.version but after obiee11.1.5 released also that major bug is not yet resolved.

 fyi:
  Fix by 11.1.1.6.1 as mentioned in the Bug.
  Bug 13054445 [http://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=13054445] -      REPLACING NULL VALUES WITH "0" IN AN OBIEE 11G PIVOT TABLE IS NOT WORKING