Setting Session Variables in BI Publisher

As of this post I am using release 11g (11.1.1.6.8).

Note: I am going to assume that you have some working knowledge of BIP 11G.

If you want to run a BIP report against OBIEE data (using dashboard prompts) you have three options:

• Source an Analysis for the data set.
• Use the logical SQL that will be directed at the BI Server for the data set.
• Use the physical SQL that will go directly against the data warehouse instance.

For this topic, I am going to talk about the second bullet and that is using the logical SQL.  The easiest way to develop a logical SQL is to create an Analysis that will be close to what you want to report on.  Once this is done, you can go to the Advance tab and copy the SQL Issued:

The SQL copied can be pasted into a BIP Dataset window.  So that is New Data Set and choose SQL Query.  If you are using presentation variables to filter your data then they must be defined in BIP as parameters with the same name.  The SQL will reference this parameters as bind variables; parameter name prefixed with a colon (same as before).  With this release, you cannot use presentation variables with multiple values.  I'm told that it is a known bug.   BIP does not know how to handle multiple value parameters (multiple selections).  Also the UI is buggy.  The work around that I know is to source an Analysis and forget the other two methods.

Setting Session Variables

If you have to set Session Variables in the Analysis, it is a matter of going to the Advanced tab an enter the code in the Prefix section as shown below.  The Session Variables can be set with hard coded values or with Presentation Variables.

In BIP there is no Prefix section.  The SET VARIABLE command has to be placed before the SQL in the BIP data set.  The other problem is that you cannot use the Presentation Variables/Parameters in the SQL.  For clarification, the variables are Presentation Variables on the OBIEE side of the house and they are called Parameters on the BIP side or the house.  The same variable on both sides are called by the same name.

To use the SET VARIABLE command, in BIP, it has to only contain literals.  So the above command may look something like:

SET VARIABLE SCENARIO_MAIN='Actuals',SCENARIO_COMP3='Actuals';SCENARIO_COMP3_LY_FLG='1Y';

In BIP, the method to put this command in front of the SQL in the data set is the use of a lexical.  A lexical is simply a Global Variable defined in a PL/SQL package prefixed by an ampersand (i.e. &SET_VAR).  Lexicals are nothing more that code segments.  See white paper Using Standard Lexicals, Flex Related Lexicals, Filters,Triggers and Grouping.

The lexical is built in the Before Report trigger.  Before Report triggers in BIP are simply a function in PL/SQL package.  Here is an example of a PL/SQL package.  It is important to know that every parameter defined in BIP has to be defined in the PL/SQL package whether or not it is going to be used in the package.  Note that the semicolon that ends the command is part of the lexical string.

*********************************************************************************

create or replace

PACKAGE BIP_REPORTS AS 

 P_SET_VAR VARCHAR2(200);

 PVARSCENARIOMAIN  VARCHAR2(100);

 PVARSCENARIOCOMP3 VARCHAR2(100);

 PVARSCENARIOCOMP3LYFLG  VARCHAR2(4);

-- All variables have to be declared whether they are used or not.

 P_FY              VARCHAR2(4);

 P_Q1              VARCHAR2(7);

 P_Q2              VARCHAR2(7);

-- Used as a before report trigger to set session variables using a lexical.

 FUNCTION SETSESSIONVARS RETURN BOOLEAN;

END BIP_REPORTS;

create or replace

PACKAGE BODY BIP_REPORTS AS

 FUNCTION SetSessionVars RETURN BOOLEAN IS

 BEGIN

 IF PVARSCENARIOCOMP3 = 'Actuals' AND PVARSCENARIOCOMP3LYFLG = '1Y' THEN

    P_HEADER := TO_CHAR(P_FY - 1);

 ELSE

    P_HEADER := PVARSCENARIOCOMP3;

    PVARSCENARIOCOMP3LYFLG := 'No';

 END IF;

 P_SET_VAR := 'SET VARIABLE SCENARIO_MAIN=''' || PVARSCENARIOMAIN || ''', ' ||

              'SCENARIO_COMP3=''' || PVARSCENARIOCOMP3 || ''', ' ||

              'SCENARIO_COMP3_LY_FLG=''' || PVARSCENARIOCOMP3LYFLG || ''';';

 RETURN (TRUE);

 END SETSESSIONVARS;

END BIP_REPORTS;

*********************************************************************************

Now the next step is to associate this the BIP_REPORTS package to the BIP data model.    Choose the Data Model node in the data model and the Properties will be shown.

Pick the Data Source where your PL/SQL package resides and enter the PL/SQL Package name that you gave it.

Now that the PL/SQL package is associated with the data model.  The data model also needs to the have the Before Report trigger associated with the function.  Now choose the Event Triggers node in the data model and create the settings similar to this:

The name of the Event Trigger can be anything.  I just happened to give it the same name as the function. Apparently in 11g, the pull down uses "Before Data" but is the same as Before Report in 10g.

The last step is to put the lexical you created before the SQL in data set.  If you have multiple data sets have to utilize the Session Variables then the Lexical has to be present in every data set.  Edit the data set to open up the SQL window and enter the lexical variable and SQL.

In the data set it will look something like:

&P_SET_VAR

SELECT col1, col2, col3, ..............

When you click on OK then this window will show up:

Just enter any syntactically correct command then click OK to get rid of this window.

Now you are good to go in setting your Session Variables.