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;

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

Formatting Text in an Excel Template

There are a bunch of ways you can format text (i.e. bold, color, etc) in an RTF template but how about an Excel template?  You can not format text the same way in an Excel template as you did in an RTF template.  You have to resort to the native functionality of Excel.

The problem that I had was to have red text in a cell whenever another data element was blank (black when not blank).   A problem with Excel is that you cannot refer to another data element unless it is in the cell like you do in an RTF template. Another words that data element has to be brought into the spreadsheet.  The column can be optionally hidden.

So lets say we have two data elements called EFFECTIVE_DATE and END_DATE.  Whenever EFFECTIVE_DATE is null then we want to make END_DATE with red text.

END_DATE will be assigned to column J.  Because whenever a data element is referred to in Excel it has to be brought into the spreadsheet.  EFFECTIVE_DATE will be assigned at the end which is this case is column AW.

Of course the assignments are made in this manner using Define Name in Excel











Now to format the END_DATE when EFFECTIVE_DATE is blank.  Click on the Conditional Formatting icon from the Home menu while your cursor is sitting in cell J2.  Then click New Rule, then select the last rule type as shown then click OK.  Enter a formula that returns a boolean value of True or False.  In this case the function ISBLANK() returns True or False. Click OK.

The INDIRECT function points to a particular cell  by using the cell reference string.

The ROW() function in the formula returns the row number the cursor is in.  As the data is filled in the rows, the row number dynamically increases.  So the J column data elements will correspond to the AW data elements.

This solution is more of an exercise in the use of Excel  then anything else.

Dynamic Sorting

I am an advocate of sorting, aggregating, performing computations and arranging a hierarchy when  forming the xml data so that it does not have to be done in the RTF template.  This simplifies the xpath/xslt scripting that needs to be done in the RTF template.

On the other hand, there can be times you don't have control on how the xml data feed is built especially when the source you have to work with is the xml data.  The problem I faced is that the xml data was sent by Siebel integration via web services.  A requirement of a report was to dynamically change as to which data element to sort on depending on the setting of a parameter.

Snipet of  xml data

For this example I am using the parameter SortBy.  The option is to either sort on ENAME or MGR.  First I will declare the parameter on the RTF template like:

<?param@begin:SortBy;'"MGR"'?>

The sort goes right after the for loop:

<?for-each:ROW?>

<?sort:xdoxslt:ifelse($SortBy=’EMP’,ENAME,MGR);'ascending';data-type='text'?>

Yes it is that simple.  You might be asking where xdoxslt:ifelse comes from.  This is one of those many undocumented features.  As you can see, the way ifelse works is that if the comparison is true then ENAME else MGR.  You are are allowed to layer multiple ifelse's.

Another undocumented feature that I found when using the sort statement is lang='no'. So then it becomes: 
<?sort:xdoxslt:ifelse($SortBy=’EMP’,ENAME,MGR);'ascending';data-type='text',lang='no?>.  What does this do for you?  If the data element, that you're sorting on, contains alphanumeric characters, then sorting will not sort correctly.

Combined Running Page and Reset Page Numbers

I had a problem that a client wanted a report such that the page number in the header would reset every time there was a new order number.  At the same time, the client wanted a running page number on the footer.

This was tricky because it is easy to have one or the other but not both.  Hok Min (sp?) from Oracle Product Support provided me with a solution.  Here his the code he gave me:

 <fo:page-number xdofo:report-page-number="true"/>

The problem with using this code is that for it to work, it has to go in a form field.  This presents a problem because headers and footers cannot have form fields in an RTF template.  The work around is to define a subtemplate and to call the subtemplate from the footer.  Understand that subtemplates do not have to be on a separate RTF template but they can exist on the same RTF template.  When I do it this way, I usually put the definitions at the end of the RTF template that I am working on.

Here is the definition of the subtemplate:

<?template:pageno?>TotPageNo<?end template?>

The form field contains the code above in the blue text.

Now to use it in a footer:


--------------------------------------------------------------------------------------
footer section

                                            <?call:pageno?>

For Loops

For Loops

There are some instances that you may want to have a for loop and not have it driven by the XML data coming in.

It is in the user guide in the section where shapes are discussed but it is not elaborated at all.  I used it in my "Dynamic Formatting" blog.

Here is the construct of the loop:

<?for-each@inlines:xdoxslt:foreach_number($_XDOCTX,1,3,1)?><-some text-><?end for-each?>

$_XDOCTX,1,3,1 - This number will be the start of the count.

$_XDOCTX,1,3,1 - The loop ends when this number is reached.

$_XDOCTX,1,3,1 - This number is used as the increment of the count.

The output of the above example will be:  <-some text-><-some text-><-some text->

If you remove the @inlines then the output text will be:

<-some text->

<-some text->

<-some text->

Instead of hard coding the three numbers, a variable can be used in one or more places.

BI Publisher MS Word Add-in: The macro cannot be found or has been disabled because of your macro security settings

All of a sudden your MS Word Add-in will quit working with this error message:  The macro cannot be found or has been disabled because of your macro security settings.

Windows Common Control-based embedded ActiveX controls may fail to load within pre-existing Office documents, within third-party add-ins, and when you insert new controls in developer mode. 

This was caused by the MSCOMCTL.OCX file not being registered properly after after an MS Office security update.

You will look on the web and find all kinds of gyrations that they make you do but it is not necessary.  

This is all you have to do:

Note You must run the commands from commands at an elevated command prompt with administrator permissions. To do this, follow these steps:

1. Click Start, type cmd.
2. Right-click the cmd icon, and then click Run as Administrator.
3. Depending on your operating system, type the either of the following commands, and then press Enter:
   • For 64-bit operating systems, type the following:
     Regsvr32 "C:\Windows\SysWOW64\MSCOMCTL.OCX"
   • For 32-bit operating systems, type the following:
     Regsvr32 "C:\Windows\System32\MSCOMCTL.OCX"