Handling Dates in BI Publisher Excel Templates

You may have struggled with merging date XML data into an Excel worksheet cell. Changing the Format Category does not make it an Excel stored date.

Excel dates are stored as a serialized number. Manually input a date into an Excel cell and you'll see a date (example: 1/6/2009). When you go to Format then the Category will be a Date.



Now change it to General. The result:

Where does that number come from? Excel counts the number of days since January 1, 1900 and comes up with a number. There are a couple of funky aspects that you should be aware of. The count starts at one and the other consideration is that Excel considers the year 1900 a leap year (a bug) and it is not. There is no such date as February 29, 1900 but it is a date in Excel. So you have to add 2 after you subtract the dates.

Cut to the chase
In Oracle SQL it is simply: SELECT DATE1 - TO_DATE('01/01/1900','MM/DD/YYYY') + 2 DATE_SER FROM......
The next step is to define the target cell in Excel with the Category of date.

You can easily test this out with this SQL:
SELECT trunc(sysdate) - TO_DATE('01/01/1900','MM/DD/YYYY') + 2 DATE_SER FROM dual

I usually want to keep the serialized date and the original date in my SELECT. So if I have a DUE_DATE then I may name the serialized one as DUE_DATE_SER.

Function 
I've created a function to serialized the date:

create or replace FUNCTION TO_EXCEL_SER_DATE
-- Created by: Jim O'Connor
-- Create date: 07/28/2017
-- Converts a date data type to an Excel serialized date
(P_DATE DATE)
   RETURN NUMBER
is
BEGIN
     RETURN (P_DATE - TO_DATE('01/01/1900','MM/DD/YYYY') + 2) ;
END;

Prevent Page Break on Group of Data

You have a situation that you want to keep a group of data together on the same and you don't want it to break on separate pages like this:





A method to keep the group together is not to put the data on separate rows on the table but to use one row like this:



Putting multiple lines on one row still won't prevent a break on separate pages, so there is one more step to tell MS Word not to break across pages. Go to table properties and uncheck "Allow row to break across pages".

Valdiate Template

You develop an RTF template and it works fine from your desktop.  You upload your template to the BI publisher server then run it and nothing happens.  What could go wrong?

Unfortunately, previewing the template from the desktop is more forgiving on errors.  You can try validating the template by going to the BI Publisher menu item and validate the template:

BI Publisher Menu

If there are errors or not then this window will appear:

If there are errors then the errors will be listed in the window.  An example of an error is not closing a for-each loop.

Null Value Passed for All

This is an issue that I struggle with to test for a null for a multi-value parameter.  Refer to the screen shot below:

I want to be efficient and pass a null when all values are desired which in most cases mean that there is no filtering in the query. 

This won't work:

T1.TYPE_OF_ASSITANCE_CODE  IN NVL(:PV_TypeAssitant,T1.TYPE_OF_ASSITANCE_CODE)

You have to use the COALESCE function against a list.

This will work:

( T1.TYPE_OF_ASSITANCE_CODE in (:PV_TypeAssitant) OR

COALESCE(:PV_TypeAssitant, 'Get All') = 'Get All' )

The COALESCE function finds the first non-null value in the list.  In the case above, if all values are selected then a null is passed to the parameter PV_TypeAssitant.  The COALESCE function finds "Get All" as the first non-null value. It effectively doesn't do any filtering for that value in the query.

My next blog entitled Lexicals, No Triggers and Other Tricks will refine the above example.

If some values are picked then you only want this line as part of the where clause.

 T1.TYPE_OF_ASSITANCE_CODE in (:PV_TypeAssitant)

If a null is passed (all values), then you simply want the line of code omitted.

T1.TYPE_OF_ASSITANCE_CODE in (:PV_TypeAssitant)



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.