Saturday 27 June 2015

Calculated Percentage Column always showing result as 0% Zero Percentage in OBIEE 11g

You may also face similar issue while upgrading OBIEE to a newer version or if you upgrade some of your dashboards from OBIEE 10.1.3.4 to 11.1.1.5. The issue is that, some of the calculations under certain columns in the reports were showing zeros instead of the calculated values.  The requirement was to calculate a match rate % for some data based on another field that showed 'MATCH' or 'NO MATCH'. 
 The real-time scenario was
(SUM(CASE Table1.Column1 WHEN 'MATCH' THEN 1 ELSE 0 END)/COUNT(Table1.Column1))*100

However, this was returning 0% as the result. With the help of some blogs and questions raised on OBIEE threads I came to know that this happens when you have an integer in a calculation. The solution which I used is very simple and was successful in achieving the requirement. The very easy fix to this issue is to multiply the calculation by 1.0.  The updated calculation which works is shown below and provides a meaningful result.
(don't use 1, as this will not work.  Use 1.0)

((1.0*SUM(CASE Table1.Column1 WHEN 'MATCH' THEN 1 ELSE 0 END))/(1.0*COUNT(Table1.Column1)))*100

5 comments:

  1. I also faced same issue after upgrade from OBIEE 11.1.1.5 to 11.1.1.7.

    But I have used the below formula to fix this :

    (cast(SUM(CASE Table1.Column1 WHEN 'MATCH' THEN 1 ELSE 0 END) as double) / cast(COUNT(Table1.Column1) as double))*100


    ReplyDelete
  2. it works ..Thanks much for the post.

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...

ShareThis