Tuesday, March 16, 2010

OBIEE: Variable Rendering

I was fighting with this one particular issue for a few days when I finally called in the big gun. Of course it took him about 5 minutes to figure out.

The Scenario
One of the report developers was trying to set some presentation variables based on the values from an init block.

I created a report referencing those values like VALUEOF(VARIABLE_NAME) so that he could easily reference those variables and see what their values were.

However, the report returned the value in the Default Initializer, and not the value from the SELECT statement.

The SELECT statement was using the built-in USER variable to find a record specific to that user, it looked something like this:
SELECT
group,
user_display_name
FROM user_table
WHERE user = UPPER( 'VALUEOF(NQ_SESSION.USER)' )
I assumed it wasn't firing but couldn't find any indication of it in the NQServer.log.

I then removed the single quotes around VALUEOF(NQ_SESSION.USER). Logged out and logged back in. In the log there was an error, invalid identifier. OK, so it is firing...so why aren't the values being populated?

I tried hardcoding the username in the query, tested it, logged out, logged in. No error in the log file. The report was still showing the Default Initializer though. WTF?

Have you found the error yet?

Here's some pretty pictures to better illustrate.

First, the init block.

init block

Variable

variable

Answers definition

answers definition

Answers rendered

answers rendered

Do you see it now?

So it was silly...very silly.

Solution
VALUEOF(TEST) should have been VALUEOF(NQ_SESSION.TEST)

new definition

Don't forget to logout. Session Initialization Blocks are fired upon login, otherwise, you'll get this lovely error:

error

And finally, it works

it works!

The part that I don't understand is why it showed me anything. If it knew (evidenced by the fact that it rendered as 'BEFORE INITIALIZATION'), why didn't it just work? Why didn't it just throw an error or something at runtime?

Debugging this particular problem was difficult. A syntax error that wasn't really a syntax error. There should be a some sort of warning...especially if it is aware.

6 comments:

gerardnico said...

If you want to know what is the value of your server variable (and not presentation variable, it's two different things). You can go in the session manager.

Check here:
http://gerardnico.com/wiki/dat/obiee/obi_server_variable_find_value

Cheers
Nico

oraclenerd said...

Nico,

Thanks for that. Very helpful. And thanks for all your other posts...you have tons of great stuff.

The thing I don't understand is how OBI knew what the Default Initializer was but didn't know what the value should be. I understand I got the syntax wrong for it...just not sure what the underlying mechanism that knows one but not the other (without throwing an error).

chet

gerardnico said...

Becareful with the syntax. Check this good blog of Alex on the naming convention for the variables:
http://siebel-essentials.blogspot.com/2008/09/oracle-bi-ee-variables.html

It seems that OBIEE save the default initializer value in the same space than for a repository variable.

The call for a session variable is:
VALUEOF("NQ_SESSION.Variable Name")

But the call for a repository variable is:
VALUEOF("Variable Name")

Very strange for sure.

Good day
Nico

oraclenerd said...

Thanks for that, I was using that exact document. It's nice to finally have the source of it...

I'm going to have to say that was not a great decision by the original creators. :)

Mesh said...

Nico,
I have a strange problem related to session variables. I tried to create a session variable with SELECT 1 from DUAL and populate it to a variable. But while I test it, it produces the following error.
NQSerror 23006: The session variable NQ_SESSION.ICX_SESSION_COOKIE has no value definition

Carla Marques said...

Hi, I'm using OBIEE 11g and I have two dashboard prompts (Product Name and Product Number). Theses prompts are hierarquical, so the values that are displayed in the Product Number prompt have dependency with the values chosen on the Product Name prompt. This I was able to do, but now I want to put a default value on the Product Number prompt. The steps are:
1 - The user chooses the value for the Product Name Prompt and by doing that the Product Number prompt is automatically filled with the correspondent value.

But unfortunatelly this is not working, after choosing the value for the Product Name the default value for Product Number is not displayed (when you open the prompt the values displayed are correct and already filtered considering the Product Name but the prompt is not automatically filled with the default value).
What I did is:
On the Product Name Prompt I've created a Presentation Variable called PROD.
On the Product Number Prompt I've selected:
1 - For the "Choice List Values" field I've checked the option "Limit Values by All Prompts"
2 - For the "Default Selection" field I've chosen the SQL Results option and enter the following SQL:
SELECT "Products"."Product Number" FROM "Sample Sales Lite"."Products" WHERE "Products"."Product"='@{variables.PROD}

What am I doing wrong?

Thank