We can clarify your doubts about OBIEE in the "Ask Questions..." Page by explaining your issue.....

Tuesday 26 March 2013

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

6 comments:

  1. How do I add a filter to the Evaluate statement. I would like the resultset to be filtered using a value from a different dimension.

    ReplyDelete
  2. Nice explaination but i have one issue how will i convert a sql query in obiee using filters
    1) Tablename.mmc_start_date is between 22nd last month & 21st of this month.
    2) OR Tablename.mmc_end_Date is between 22nd last month & 21st of this month.
    I have declared one Presentation variable. In that i am bringing month and year as prompt.

    ReplyDelete
  3. This is highly informatics, crisp and clear. I think that everything has been described in systematic manner so that reader could get maximum information and learn many things. explanation

    ReplyDelete