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:

Functions For

1.

2.

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.

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

**Either By Oracle BI Answers.****OR by using a Logical column (in the Logical Table source) within the Metadata (repository).**

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.

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

**Function.**__Evaluate__
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

In our example the above 3 strings are the perameters, those we need to pass into the Evaluate Function by specifying the

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

Like the following above steps we can resolve that type of errors comming while using the Evaluate function.

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

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

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

Good

ReplyDeletewtf good?

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

ReplyDeleteNice explaination but i have one issue how will i convert a sql query in obiee using filters

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