Sunday, 3 May 2015

Making OBIEE 11g Evaluate function dynamic

We know that we can use the evaluate function to call any function stored on the database and pass arguments to it. What if we have a requirement to use different database functions for the same column based on different selections made by the user.
This article talks about a method to handle this requirement.
Before you start, make sure that EVALUATE_SUPPORT_LEVEL = 2;  is set in nqsconfig.ini.

We start with creating 2 functions. The definition of these functions are shared below.

create or replace function get_abc_vishal return varchar2 as
begin
return 'abc';
end;

create or replace function get_xyz_vishal return varchar2 as
begin
return 'xyz';
end;

Now we create a column with the following formula in it.

Evaluate('/*%1*/@{pv_function_name}{get_abc_vishal}()','dummy value')


















Note that /*%1*/ is just to trick OBIEE that we have a parameter. OBIEE evaluate function needs parameters and our stored database functions (get_abc_vishal and get_xyz_vishal) do not have any argument list.

We then create a variable prompt and have the following 2 custom values in it.
get_abc_vishal
get_xyz_vishal





















The value of the evaluate function will change based on the selection made by the user in the variable prompt.



4 comments:

Ibrahim Khaleel said...

Dear Vishal,

Thanks for the information regarding evaluate function.

how can we use evaluate function in dashboard prompt variable using request variable for variable expression (default value). i want to convert normal number to date which accepting two variable from dashboard (Month & Year) and day passing through current date. what is the correct syntax.

i am passing below in column formula and its give correct result.

CAST(EVALUATE('TO_DATE(%1,%2)', DAYOFMONTH(CURRENT_DATE) || '-' || @{pv_DIS_Month}{Month(CURRENT_DATE)}||'-'|| @{pv_DIS_Year}{Year(CURRENT_DATE)} ,'DD-MM-YYYY') AS DATE)

in the formula @{pv_DIS_Month} & @{pv_DIS_Year} taking from other Dashboard Prompt.

but the same formula is not giving correct result in

Vishal Pathak said...

Lets only talk about this blog and the questions related to it on this page.

Sreeni said...

HI Vishal,

Thank you for the nice post.

I would like to call a DB function in OBIEE RPD (CONTENT -WHERE Clause)
like this
Evaluate('GET_GARM(%1.%2)' ,'1' , "CMJULSYS".""."SP"."CUST_PIR_PRJ"."ACCESS_ID" )

BUT If write like this i am getting the ACCESS_ID values in OBIEE. but i need DB_FUNCTION Values in OBIEE.
my DB function values will Return as 1 and 8 .

Is there any way to bring the only one values from DB function in OBIEE.?

Please give me your thoughts on it.

Vishal Pathak said...

im not really able to understand your question.

However, here r my thoughts.

Check the physical sql. The results you get on OBIEE will depend on how the physical sql looks.