Wednesday, December 28, 2011

OBIEE 11g: Calculated % always shows up as 0%

While upgrading some of my dashboards from OBIEE to, I noticed that some of my calculations in Answers were showing zeros instead of the calculated amount.  The calculation I was trying to perform was to calculate a match rate % for some data based on a field that showed 'MATCH' or 'NO MATCH'.  The original calculation was:

(SUM(CASE BuyPoint.Match WHEN 'MATCH' THEN 1 ELSE 0 END)/COUNT(BuyPoint.Match ))*100

However, this was returning 0% as the result.  This happens when you have an integer in a calculation.  A very easy fix to this is to multiply by 1.0 (don't use 1, as this will not work.  Use 1.0).  The updated calculation below works and provides a meaningful result.

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


  1. DMurph,

    Will you be at the RittmanMead forum next week in the ATL? If so, hope to meet you then! Great blog man. Keep up the good work and many thanks!


    1. Thank you, Jeremy. I won't be at the Rittman Mead forum next week, although I did attend it last year. It was well worth the time. I have a conflict with another conference I'm going to, so I can't make the Rittman Mead one. Otherwise, I would be there.

  2. DMurph,

    I had forgotten to subscribe via email to replies on this post, and never saw this until now. Sorry we didn't get a chance to run into each other at the RM Forum (was terrific). I also was at the prior year forum as well -- wish we'd met then! I work over in Norcross, somewhere not too far from "atlantabi" -- wherever that may be in the greater ATL area. :)

    Any chance we'll both meet on the left coast at OOW in Oct? I'm giving a presentation on BI Publisher+OBIEE based financial reporting. Oh joy! LOL

    Thanks again for the "Act As" post. Worked brilliantly.

    If you don't mind, drop me an invite on LinkedIn sometime:


  3. Ran into this issue when converting. Seems like Oracle decided to assume/force integer division in the 11g calculation engine. We had to visually inspect every report for forced integer calculations. Very Disappointing.

  4. This works great! Thank you so much! very helpful

  5. Thanks, DMurph.
    I had the same problem and your tip worked out just fine.

  6. Hi Dmurph,

    Thank you very much for this advice. It helped me fix a problem :)

    I have over 7 years of experience in BI and ETL tools. In order to enhance my learning and share my knowledge, I would like to connect with you on linked In.

    Please send me an invite.

  7. This solved my problem. Awesome find!