Making OBIEE 11g Evaluate function dynamic

Tweet

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.