Date Formatting and Handling for Siebel Reporting

I have discussed about the Siebel Date related issue before at one of the posts. The issue is that the IO returns the date data in a different format from the one BI Publisher expects (Canonical format), which prevents BI Publisher from formatting the date or handling the date data with its functions.

There are some workarounds available I discussed about at the post. But not anymore! Siebel Reporting product development team at Oracle has addressed the issue so you don’t need the workaround anymore!

IO Returns Date in Canonical Format with 8.1.1.2 !

UPDATE: Actually, this functionality didn’t make it to the production. It’s planned, but I don’t know when or which release will have the fix. I will update once I get the update. Meanwhile you can use the following ‘getCanonicalDate’ function.

First, with the latest FixPack 8.1.1.2, which was released recently, the IO now returns the date in the canonical date format. So you can use BI Publisher’s date related function or formatting with the data without any workaround.

New Function - getCanonicalDate

Second, if you can’t apply the FixPack 8.1.1.2 then you can download an updated XSLFunction.jar file from Oracle Support page (ID 764191.1) and replace the original with this new one. This new library includes a function called, ‘getCanonicalDate’, and this is the one you can use to convert the Siebel date string to the canonical date format.

Here is an example of how you can use the ‘getCanonicalDate’ function.

<?namespace:psfn=http://www.oracle.com/XSL/Transform/java/com.siebel.xmlpublisher.reports.XSLFunctions?> xdoxslt:date_diff('d', psfn:getCanonicalDate(CreatedDate), psfn:getCanonicalDate(LastUpdatedDate), $_XDOLOCALE, $_XDOTIMEZONE)

As you see in the example, make sure you declare the ‘psfn’ namespace first before you use the ‘getCanonicalDate’ function. You can take a look at ‘Siebel’s Extended Function for RTF Template’ if you don’t know how to enable your Word Add-ins Template Builder to test with the library.

And of course, you need to replace the existing one with this new library at the server side to take this in effect when you run the report. You need to do this at the two places, one is at BI Publisher server, and another is at Siebel home.

BI Publisher Server

Take a look at the bookshelf, ‘Copying Siebel JAR files to BI Publisher’, which describes the detail.

Siebel Home

Replace %SIEBEL_HOME%/classes/XSLFunctions.jar with the downloaded one.