Tag Archives: variables

URL-variable not passed thru to session variable

This one I found to be interesting:

The goal is to set session variables using url variables, but can you also do this for the user and password ?
url variable (&Upwd) is not passed to session variable USER_PWD.
The variable USER is correctly passed, the variable USER_PWD is not

The steps to set an OBI session variable via a URL call utilizing the

instanceconfig.xml tag should be as follows

1. Create a session init block that will act as a ‘placeholder’ for the
session variable to be set via the url call – the variable can be set to

2. Set the ‘Enable any user to set the value’ option for the variable.

3. Add the following tag block to the instanceconfig.xml file anywhere
between the <ServerInstance></ServerInstance> tags:

<UserIdPassword enabled=”true”>
“TEST_VAR” should match the session variable name (case sensitive).

4. The following option will need to be appended to the OBI url passed –
&SETVAR=’variable value to pass. So a full example would be:

However, note that you cannot set the value of any System Security Session variable (specifically USER, PROXY, GROUP and WEBGROUPS) using any source method (e.g.: url, cookie, httpHeader) by design. Having this ability would open possible security breaches.

If you attempt to set the USER variable with the following instanceconfig.xml setting:

<Param name=”NQ_SESSION.USER” source=”url” nameInSource=”nquser” />

You will get the following error when using the url: http://localhost:9704/analytics/saw.dll?Dashboard&nquser=user1&nqpassword=public

nQSError: 10018: Access for the requested connection is refused

OBIEE variable

When using Conditional Formatting in Answers I was unable to refer to repo vars

Is it possible to refer repository variables in filters of conditional formatting?


Can use presentation variables only for filters in conditional formatting as per the following bookshelf guide:

Oracle Business Intelligence Answers, Delivers, and Interactive Dashboards User Guide > Basics of Working with Requests in Oracle BI Answers > Using Variables to Display Values in Request Results, Dashboards and iBots

Turned out to be a bug.

Workaround using Bins:

Say you had a dollars measure column in your request. And you wanted to add a conditional formatting to set it to green if it was greater than or equal to repository variable repvar1:
– Create a duplicate column dollars in the request and name it dollars_duplicate.
– Set the column format for dollars_duplicate to hidden
– Set the custom heading for dollars_duplicate column formula to ‘dollars_duplicate’
– Create a bin e.g. ‘b1′ based on a filter condition where dollars_duplicate is >= repvar1 – Then go to your required column e.g. dollars and add conditional formatting based on dollars_duplicate column. And in the filter condition set it to the bin name ‘b1′ and specify the ‘edit format’ as green in colour.

You can similarly add all the required conditions as bin condition first and then reference the respective bin name for conditional formatting.

Cache persistence

There’re a few well-written articles on about OBIEE caching configuration and strategies already. However, I’m posting a solution that I’ve found while investigating an unexpected behavior with tpl cache files disappearing for no reason (no updates in polling table, no manual purge, no BI services restart). Here’s the solution from Oracle:

The behavior was resolved when customer modified the refresh interval for the Repository variables that exist in their Repository.
When a dynamic repository variable is updated, cache is automatically purged. This is designed behavior. Cache will be invalidated (i.e. purged) whenever the initialization block that populates dynamic repository variable is refreshed. The reason that refreshing a variable purges cache is that if a variable was used in a calculation, and the variable changed, then cache would have invalid data. By purging cache when a variable changes, this problem is eliminated.

Since this is the designed functionality, Change Request 12-EOHPZ3 titled ‘Repository variable refresh purges cache’ exists on our database to address a product enhancement request. The workaround is to go through the dynamic repository variables and verify that the variables are being refreshed at the correct interval. If a variable needs to be refreshed daily, there may be a need to set up a cache seeding .bat file that runs after the dynamic variable has been updated. If the cache seeding .bat file runs prior to the refresh of the dynamic variable refresh, then the cache will be lost.

I think they should try to cover similar thing in OBIEE documentation.