Monday, August 1, 2011

OBIEE: The repository variable, %S has no value definition

First of all, it's a Session variable, not a repository variable. Whatever.

So I had created a PIPELINED function to serve as the source of a table. From the analysis side, we wanted to use a presentation variable to set a session variable that was referenced in the table source.

Here's the SQL for the Stored Proc call:
FROM TABLE( get_key_value_pairs( 4 ) )
I hardcoded 4 in there just to see how it worked. BTW, Gerard Nico describes the process here.

I then created a session variable along with the init block.
which would populate the P_LIMIT session variable. Nothing terribly fancy.

I then ran a simple report based off of the new table source (with the hardcoded value):

Cool, now let's swap out the hardcoded value with the reference to the session variable.

Since I don't do a lot of front-end stuff, this post at Siebel Essentials has been my bible.

I locate what I am trying to do in the matrix:
Like a big jerk, I have to add spaces to it, you know, for readability. My SQL looks like this:
FROM TABLE( get_key_value_pairs( VALUEOF( "NQ_SESSION.P_LIMIT" ) ) )

OK, let's try without the quotes ("):
FROM TABLE( get_key_value_pairs( VALUEOF( NQ_SESSION.P_LIMIT ) ) )
Same thing.

Let's gid rid of the spaces (I've had issues with spaces before, but I can't seem to find the post).
FROM TABLE( get_key_value_pairs( VALUEOF(NQ_SESSION.P_LIMIT) ) )


Fun. I have noticed that certain areas of the RPD treat spaces slightly differently. If I ever find the other post, I'll link it up here.

Lesson? Be careful with your space.

No comments: