One of the common questions that keeps coming in the forums is “How does one access the Repository and Session variables defined in the repository from BI Answers?”. Since this is again a pretty common question i thought i would blog about it since there is a documentation bug on this one. Before we get into that lets first try to understand the types of variables that we have in BI EE.
1. Repository Variables – There are 2 types of repository variables. Those that are static and those that are dynamic(these can be refreshed at regular intervals of time). These 2 are defined in the repository.
2. Sesion Variables - There are 2 types of Session Variables. System Session Variable and Non-System Session Variable. The scope of these variables is for a particular logon session. The System session variables are predefined and we can override these predefined variables. These are defined in the repository.
3. Presentation Variables - These variables are defined in the Presentation layer and their scope is within a dashboard.
Remember that Repository Variables can be assigned in 2 ways. One is by direct assignment within the repository(this is not possible for session variables) and the other is via Initialization Blocks. There is a small concept that we should understand for using these variables in Init Blocks. We will see that later. First lets us try to understand accessing static Repository Variables. Lets create a simple Repository variable called City and assign the value ‘ALAMEDA’ to it.
In order to access this Repository variable from Answers, we must use the following syntax.
@{biServer.variables['variablename']}
In our case we would use
@{biServer.variables['City']}
Now, Session variables can be assigned only via Init Blocks. In our case lets try to create a simple Session variable called setUser1 and the value for this variable would be obtained from a simple SQL statement
select ‘sample’ from dual
So, basically we are trying to assign setUser1 to the value sample. Also, lets specify a default value for the variable which is sample1. So, if the Init Block fires properly it would set the variable to sample else it will be set to sample1.
Now in order to access this variable from BI Answers we will be using the following syntax.
@{biServer.variables['NQ_SESSION.variablename']}
In our case, we would be using
@{biServer.variables['NQ_SESSION.setUser1']}
Remember, if your underlying data in the database keeps on changing, your session variables (if you had used the SQL similar to the one showed above) would not refresh every time. In order to enable the refresh you need to add a dummy :USER in the where clause of your SQL query. Something like this
select ‘sample’ from dual where ‘:USER’ != ‘ ‘
If you want to use Presentation variables in the where clause of your report, you need to have the syntax below
’@{variablename}’
For example,
’@{datepromptvalue}’
Posts
