Help with added column in obiee 11g

Joe Choueiri-Oracle

Hi

The subject area I am working with does not have quota so I need to create a new column in my analysis and have the quota in it so I have created a case statement to say that if region is NAMER then 1000000 when region is EMEA then 2000000 etc and that will display a number beside each region if I have 2 columns analysis the region and quota...now I need to add a column from the subject area which is the spend so now I have 3 columns region, quota (made by case statement) and spend...I need to create a 4th column myself (not from the SA) to show the attainment which is Spend *100 / the case statement I built and now I am getting an error.

Here is my formula (my case statement in here is only for NAMER just to test it)

"Fact - Customer AR Revenue"."Amount USD"*100/ CASE  WHEN "Ship Customer Country Parent Geo"."Ship Customer Country Parent Region" = 'NAMER' THEN '10000000'  END

Here is my error

Formula syntax is invalid.

[nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 22023] An arithmetic operation is being carried out on a non-numeric type. Please have your System Administrator look at the log for more details on this error. (HY000)

SQL Issued: SELECT "Fact - Customer AR Revenue"."Amount USD"/"Fact - Customer AR Revenue"."Amount USD"*100/ CASE WHEN "Ship Customer Country Parent Geo"."Ship Customer Country Parent Region" = 'NAMER' THEN '10000000' END FROM "Customer Spend"

Any idea on how to do that?

Thanks

Joe

[Deleted User]

Joe,

'10000000' is a string - equivalent to 'blue' or 'banana' or 'slartibartfast'. Not sure what the mathematical result of a 50000 / 'telephone' should be with that formula.

Maybe read up on what a data type is:

https://en.wikipedia.org/wiki/Data_type

Gianni Ceresa

Once you went through data types and fixed that part I can already tell you the follow up question you will have: why is the result of the calculation of your 4th column not right?

The good news is that the result is actually right.

You will maybe want to look for "integer division" vs floating point division: https://gerardnico.com/dat/obiee/int#division

[Deleted User]

LOL X-D

You just had to point that out didn't you

Gianni Ceresa

99% chances the question was coming right after, so I provide a reply before he even has to ask

[Deleted User]

Not saying you're wrong at all. Just funny.

Joe Choueiri-Oracle

Thank you both- I had an idea that I needed to convert the string to number but was not sure how and now I guess I need to read more and more about the CAST function in OBIEE. Thanks again.

Joe