This discussion is archived
3 Replies Latest reply: Dec 29, 2012 11:38 AM by kart

OBIEE 11G Count problem

Currently Being Moderated
Hi Gurus,

I want to convert to below condition to obiee.

Database Condition like below:

ZEROIFNULL(CAST((CAST(COUNT(CASE WHEN END_output<=.033 and END_output>='-.117' THEN END_output END) AS FLOAT)/
CAST(NULLIFZERO (COUNT(In_PUT)) AS FLOAT)) AS DECIMAL(10,1)))*100 AS Real_Time

In Database output is = 48

I have converted to OBIEE like below.

IFNULL( CAST ( COUNT(CASE WHEN "- Order Fact"."END_output" <= .033 and "- Order Fact"."END_output" >='-.117' THEN "- Order Fact"."END_output" END) AS FLOAT ), 0) / CAST(IFNULL (COUNT("- Sales Fact"."In_PUT"),0) AS FLOAT ) *100.0

In OBIEE output is = 45

Note: I am getting two or three count difference in OBIEE.
I guess the problems is Decimal, how to apply decimal in obiee like (DECIMAL(10,1)))..

Note: I have tired several methods like below but no luck still am facing problem.

(COUNT(CASE WHEN (END_output*1.00)<=.033 and (END_output*1.00)>='-.117' THEN (END_output*1.00) END)/
COUNT(In_PUT))*100.0

Thanks
• 1. Re: OBIEE 11G Count problem
Currently Being Moderated
Hi Gurus,

Any solutions.

Thanks
• 2. Re: OBIEE 11G Count problem
Currently Being Moderated
Hi Actually,

we have the same issue but not with case statement, the value changes bec of missing decimals, so try to put the data format to show decimals and run in a table view to see the missing decimals so u find the exact and with the associate dimension.

Just have a work around on it.
• 3. Re: OBIEE 11G Count problem
Currently Being Moderated
Hi I dont have obiee installed right now so Im just guessing. What is the datatype of your fields.

1) Make them as double in physical layer.
2) Add those fields in one dummy report and go to data properties and set decimal places = 2 and save it.

Legend

• Correct Answers - 10 points