Note: This post relates to an issue which has been produced in Version 11.1.1.7.150120 for the purposes of this blog. It may not be present in previous versions and may be fixed in future releases.
You write an analytic and it runs fine. You stick it on a dashboard with prompts and it runs fine. So you now decide to extend that dashboard prompt a little by defaulting in some values – a common method is to use session variables. Your prompt now looks like something like this.
Which seems like it will work well. Apart from when you run the dashboard you see this:
So what has gone on there? What on earth is *)nqgtu(*?? Unfortunately there is a (what appears to be) a bug in BI whereby this odd internal piece of text is passed to analytic prompts when that prompt is defaulted using a non-initialized session variable. You might see a slightly different error report (however with very similar wording) depending on how your analytic is built, however ultimately this is the same issue. The reason I’ve chosen to post this is that there appears to be very little information or explanation on this anywhere on the public web, and even My Oracle Support is extremely limited. I did actually find an unpublished bug listed against the standard BI Applications (11.1.1.8.1 BP6) product however the resolution was simply to remove the default selection as opposed to fixing the root cause of the problem (probably because that is the OBIEE rather than OBIA team’s responsibility). So if it is a bug then as always it’s likely to take a very long time for a fix to surface in the product (the tendency for many OBIEE bugs these days seems to be to fix in future releases rather than release a patch!) and so a workaround must be used; Thankfully this is relatively simple and involves implementing the default selection as a SQL query instead of a variable, with the SQL query just selecting the variable.
It doesn’t really matter where you select from so long as the table returns at least 1 row.
Now we see that when the prompt is used, it instead defaults to “(All Column Values)”.. and more importantly it doesn’t cause the analytic to fail.
This does change the behaviour of presentation variables slightly however; a presentation variable set by the prompt gets a null string value '' when used in a column formula, however when displayed using the @{biServer.variables['']} syntax, it yields the value ‘(All Column Values)’. This is easily resolved if it affects you.
Hopefully this will be fixed in a future release, but until then we’re forced to hack around it!