OBIEE - Evaluate - Embedded DB Functions

Global

The EVALUATE function is not enabled by default because it exposes the database to SQL injection attacks.

If you encounter any error, see if you must set the value of the EVALUATE_SUPPORT_LEVEL parameter in the NQSConfig.INI file.

EVALUATE_SUPPORT_LEVEL = 0;

• 1: evaluate is supported for users with manageRepositories permission
• 2: evaluate is supported for any user.
• other: evaluate is not supported if the value is anything else.

See Documentation

Privileges

OBIEE 10G/11G - OBIPS - Privileges

EVALUATE Function

This function is intended for scalar calculations.

Syntax:

EVALUATE('DB_Function(%1)' [as datatype], <Comma separated Expression of Parameters>)

where:

• datatype is one of the OBIEE datatype. It seems to default to the datatype of the passed parameters.

Example:

SELECT e.lastname,sales.revenue,EVALUATE('dense_rank() over(order by %1 )' as double,sales.revenue) FROM sales s, employee e;

EVALUATE_AGGR

This function is intended for aggregate functions.

Syntax:

EVALUATE_AGGR('DB_Aggregate_Function(%1)', <comma separated Expression)

Example with the Listagg function in a logical SQL (WM_CONCAT works also) :

SELECT order.nummer, sales.qtysold, EVALUATE_AGGR('listagg(%1)', sales.item) From SnowFlakeSales;

EVALUATE_PREDICATE

This function is intended for functions with a return type of boolean.

Syntax:

EVALUATE_PREDICATE('DB_Function(%1)', <comma separated Expression)

Example:

SELECT year, Sales as DOUBLE,CAST(EVALUATE('OLAP_EXPRESSION(%1,''LAG(units_cube_sales, 1, time, 
time LEVELREL time_levelrel)'')', OLAP_CALC) AS DOUBLE) 
FROM "Global".Time, "Global"."Facts - sales" 
WHERE EVALUATE_PREDICATE('OLAP_CONDITION(%1, ''LIMIT time KEEP ''''1'''', ''''2'''', ''''3'''', ''''4'''' '') =1', OLAP_CALC) 
order by year;

[nQSError: 42015] Cannot function ship the following expression

Odbc driver returned an error (SQLExecDirectW).
  Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 2057371920. [NQODBC] [SQL_STATE: HY000] 
[nQSError: 10058] A general error has occurred. 
[nQSError: 42015] Cannot function ship the following expression: Evaluate(....

This error comes from the fact that OBI is unable to know wich aggregation rule it must apply when you ask for a total in a view. You have to define the answer aggregation rule in the column formula of in the view to resolve this error.

nQSError: 46035 - Conversion to …. is not supported

The below error

Message returned from OBIS. 
[nQSError: 43119] Query Failed: 
[nQSError: 46035] Datatype(INTEGER) nullable(1) unicode(1) external char(0) conversion to datatype(DATE) nullable(1) unicode(0) external char(0) is not supported. (HY000)

occurs when the function pushed return another datatype than the passed parameters.

Example: in the below function, we are passing two date and we are retrieving a number:

EVALUATE( 'DAYS_BETWEEN(%1,%2)' ,"Dim"."Day1", "Dim"."Day2" )

To correct it, you need to define the returned data type of the function (in our case, double)

EVALUATE( 'DAYS_BETWEEN(%1,%2)' as double, "Dim"."Day1", "Dim"."Day2" )