Evaluate() Function in OBIEE to Embedded DB Functions

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

1. Either By Oracle BI Answers.
2. 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

1. EVALUATE( ) --> It is a scalar function that are computed post-aggregation and analytic calculations

2. EVALUATE_AGGR ( ) --> It is a aggregate function with group by clause

EVALUATE( ) Function :


Syntax:
EVALUATE('db_function(%1...%N)' [AS datatype] [, column1, columnN])

Example:

1. Evaluate('TO_DATE(%1,%2)' AS CHAR ,'20130326','YYYYMMDD')

2. Evaluate('substr(%1,%2,%3)' as Char,'AllamNarasingaRao','1','5')

Passes the specified database function with (optional) referenced columns as parameters to the back-end data source for evaluation. This function is intended for scalar and analytic calculations, and is useful when you want to use a specialized database function that is not supported by the Oracle BI Server, but that is understood by the underlying datasource.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

EVALUATE_AGGR( ) Function:
Syntax:

EVALUATE_AGGR('db_agg_function(%1...%N)' [AS datatype] [, column1, columnN])
  Example:

EVALUATE_AGGR('sum(%1)', "sales"."quantity")

Passes the specified database function with (optional) referenced columns as parameters to the back-end data source for evaluation. This function is intended for aggregate functions with a GROUP BY clause.
The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Let see How can use the Evaluate Function properly.

Need Click on the Edit column Formula>f(...) to select the Evaluate( ) Function.

In Insert Option Dialog prompt need to select the DB Functions to specify the Evaluate Function.

 

After select the Evaluate Function in the Prompt dialogbox, the syntax of the Evaluate function will see like the above in screen shot.

 

There we need to replace the inner things with our valid syntax formate. As specified Below

 

 

Here

 

Evaluate('substr(%1,%2,%3)' AS Char,'AllamNarasingaRao','1','5')

• Evauate ( ) is the function we know about it.
• Substr ( ) is the DB Function. It needs to enclosed in the single quotes, like this 'substr( )'
• AS Char is the Datatype, We need to specify this also after enclosed your DB function. Then after we need to place the , .
• 'AllamNarasingaRao','1','5'

In our example the above 3 strings are the perameters, those we need to pass into the Evaluate Function by specifying the %1 for first string ('AllamNarasingaRao'), %2 for Second String ('1') and %3 is for Third String ('5').

Lets run this by clicking on the Results Tab. And See what will happen.

In this case we will get one common error in Evaluate Function that is

[nQSError: 42015] Cannot function ship the following expression:Evaluate( substr(%1,%2,%3),'AllamNarasingaRao', '1', '5') . (HY000)


This Error is came beacouse of taking the only one column that too it has Evaluate function. In this case we can resolve this error by taking at least one more column from exisiting subject area.    

    Like the following above steps we can resolve that type of errors comming while using the Evaluate function.   Note: We need to add at least one more column to the exsisting Evaluate function column.  

• Here I am specifying some examples with Evaluate function used in the OBIEE 11g.

1. Evaluate('TO_CHAR(%1 ,%2)' AS CHAR ,"Itemwarehouse"."Last Update Date",'DD-MON-YYYY')

2. Evaluate('NEXT_DAY(%1,%2)' ,"Itemwarehouse"."Last Update Date",'SUNDAY')

3. Evaluate('TO_DATE(%1,%2)' AS CHAR ,'20130326','YYYYMMDD')

4. Evaluate('TO_CHAR(TO_DATE(%1,%2),%3)'AS CHAR ,'20130326','YYYYMMDD','DD-MON-YYYY')

5. Evaluate('TO_CHAR(TO_DATE(%1,%2),%3)'AS CHAR ,"Itemwarehouse"."Last Update Date",'DD/MM/YY HH:MI:SS AM','DD-MON-YYYY HH:MI:SS AM')    

---The End---