OBIEE - Evaluate - Embedded DB Functions

Obiee Ceim


This Evaluate functions (since version enables users and administrators to create more powerful reports by directly calling Database functions from:

  • either Oracle BI Answers
  • or by using a Logical column (in the Logical Table source) within the Metadata (repository).

The following new BI Server database functions are supported

Functions For
EVALUATE scalar function that are computed post-aggregation
and analytic calculations
EVALUATE_AGGR aggregate functions with group by clause
EVALUATE_PREDICATE functions with a return type of boolean

This feature is restricted to support SQL sources. MDX or MOLAP data sources are not supported with an exception for Essbase.

Permissions, security, enable and prerequisites


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.


  • 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


OBIEE 10G/11G - OBIPS - Privileges

Privilege Evaluate Predicate

Escape character

The escape character is the quote.

Example: The regular expressions of oracle contain the pattern expression in quote (in the code below '^[A-Z]+').

To prevent a syntax error, you must add a quote before your quotes such as:

EVALUATE('REGEXP_SUBSTR(%1,''^[A-Z]+'')', MyTable.MyColumn)

If you don't do it, you can get this error:

[nQSError: 27002] Near <^>: Syntax error [nQSError: 26012] .



This function is intended for scalar calculations.


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


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


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


This function is intended for aggregate functions.


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;

Obiee Evaluate Aggregate


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


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


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
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.

Obiee 10g Aggregation Rule

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" )


Discover More
Obiee Period To Period Comparison Lag
Blog - Obiee 10G – Period to period comparison with the analytical function Lag/Lead

During this thread on the Obiee forum, Goran point out a solution for a period to period comparison...
Obiee Essbase Unbalanced Hierarchy
OBIEE - Essbase

Cube scope: Member attributesunbalanced hierarchies“”“”hierarchy columnattribute column Support...
Obiee Ceim
OBIEE - Functions

The home page of OBIEE functions of the logical sql and from BI Server. See Logical SQL Reference
Obiee Ceim
OBIEE - Logical Sql

The logical SQL is the SQL that understand BI Server. It's a full Ansi 92 SQL (including subqueries and derived tables), plus special functions (SQL extensions) like AGO, TODATE, EVALUATE, and others....
Obiee Period To Period Comparison Lag
OBIEE - Period to period comparison (YAGO, MAGO) with the analytical function Lag/Lead

lag and lead are analytical functions that can be used to get the value of a column in a previous/next row. More ... densewith sparse datadense use the Obiee Ago Function or densify your data ...
Obiee Evaluate Regexp Formula Column
OBIEE - Regular Expression (Regexp) and how to filter on string pattern ?

Regular expression are really powerfull feature to find pattern through a filter in a string. OBIEE doesn't have a function oustide of the box but permit you to use the regular expression of your database...
Obiee Measure Column
OBIEE 10G/11G - Measures (Columns|Attributes)

A measure column is a logical column with an aggregate function. Any column with an aggregation rule is a measure. Examples include Revenue or Units Sold. Measures need to be defined in a logical fact....

Share this page:
Follow us:
Task Runner