Tweet |

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.

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.

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.

## 6 comments:

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

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

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.

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.

Hi vishal hope you are doing greatðŸ˜€

I m little confused as I know evaluate function is used to call the database function from obiee and it's syntax is:

Evaluate('db_function(%1...%N)',column1...columnN )

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

As per your farmula structure u r passing functions name from prompt so Wats the point of passing dummy value

the syntax requires at least 1 argument.

the dummy value helps us execute db functions that do not except arguments.

Post a Comment