Wednesday, December 5, 2007

OBIEE EVALUATE function and presentation variables

I have this function defined in a report:
evaluate('bi_server.fnc_XXX_l_ind (%1,%2)' as varchar(250),@{MACHINEID}{'1234'}
,@ {PTESTCODE}{'X56GH'})

both %1 and %2 are varchar

When I put the report in a dashboard and try to fill the presentation values the report comes back with:

[nQSError: 27005] Unresolved column: "X56GH". (HY000)

The reason for this error is that is't trying to use the column "X56GH" instead of the string "X56GH".

The solution is to put single quotes around the presention variables:
evaluate('bi_server.fnc_XXX_l_ind (%1,%2)' as varchar(250),'@{MACHINEID}{1234}','@{PTESTCODE}{X56GH}')

But not around the default values!

1 comment:

Drive said...

Hi, it's very usefull post.
I have one question.
I'm using SQL SERVER 2005 as datasource, how can I pass Nvarchar parameter using evaluate function ?
it passes question marks instead of real daa.