Nov
12

Changing the value of a session variable at the session level

1 Comment
Share

In OBIEE it is possible to update the value of both session and presentation variables with the  value of  a prompt selection, I recently ran into a situation where I needed to update the value of a session variable beyond this scope. I couldn’t find a good solution without using the write back feature but I thought I would document the NQSSetSessionValue() function. NQSSetSessionValue is an ODBC function that allows a developer to update the value of a session variable for the globally, the syntax for the function call is as follows: call NQSSetSessionValue(‘=;’); A simple example of its use would be: call NQSSetSessionValue(‘string SV_DEFAULT_DEPARTMENT=Finance;’); One caveat is that you cannot use the variables as part of your function call, so I couldn’t use VALUEOF(SV_DEFAULT_DEPARTMENT) as part of my function call, this led me away from this solution path. Note:

This is an ODBC function, it cannot be run from “DIRECT SQL request” against a connection pool, instead you need to go to the Administration link on the top right and click on issue SQL. If your requirement is somewhat different of what I was attempting, you can play around with the function in the screen illustrated below and once you are satisfied with the behavior of your function call you can add this function call at the beginning of your logical SQL in a report.

OBI_Issue_SQL

In OBIEE it is possible to update the value of both session and presentation variables with the  value of  a prompt selection, I recently ran into a situation where I needed to update the value of a session variable beyond this scope. I couldn’t find a good solution without using the write back feature but I thought I would document the NQSSetSessionValue() function…

Tags: , , , , , , , ,

Posted in Business Intelligence
1 Comment Posted by ignacio

1 Response to "Changing the value of a session variable at the session level"

Leave a Comment