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.



Variable



Answers definition



Answers rendered



Do you see it now?

So it was silly...very silly.

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



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



And finally, 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.