My Date doesn’t like me with Siebel !

 

Are you doing ok with your date in the Siebel reporting with BI Publisher ?

If you are coming from Siebel this might make you kind of confused because the BI Publisher user’s guide tells you it supports all sorts of the date formatting and date related calculations but somehow it doesn’t work when you try with the Siebel reports. If you are coming from BI Publisher reporting world you might have figured out why it doesn’t.

BI Publisher provides quite various of ways of the date formatting and date related calculations. You can find the detail from the BI Publisher’s Reports Designer’s Guide. Here is a section for the date formatting. And here is a section where you can find the date related functions that you can use to do the date related calculation.

However, in order for BI Publisher to do the date formatting or the calculation the date data (in the XML data) needs to be presented as something called ‘Canonical’  format, which is something like ‘2008-10-09T05:31:13.000-04:00’. The example contains Year, Month, Day, Time, and Time zone difference from GMT. It doesn’t need to contain the time and the time zone difference parts, but it needs to have the date part at least. And it needs to be in ‘YYYY-MM-DD’ or ‘YYYY-MM-DDTHH:MI:SS’ format.

And now, the date data generated by Siebel with the IO is in ‘MM/DD/YYYY HH:MI:SS’ format, so it is something like ‘10/09/2008 05:31:13’. And BI Publisher doesn’t recognize that this is a date data because it’s not presented in the ‘Canonical’ format!

Due to this fact, not only you can’t do the date formatting but also you can’t do the date related calculation. For example, you might want to print number of day between two presented dates like start date and end date. If the those two date data is presented in the ‘Canonical’ format then you can use something like ‘date_diff()’ function to get the duration counted between the two dates. But it doesn’t do anything or you get an error if you provide those two dates not in the ‘Canonical’ format.

There is a discussion in the Siebel reporting product development that this might be addressed in the next Siebel FixPack by having IO returning the date in the ‘Canonical’ format. But it’s not determined yet and guaranteed.

So what should we do now ? There are two possible and reasonable workarounds to overcome this issue. The first one is to use one of the Siebel’s extended function ‘totext()’ to convert the Siebel date format to the ‘Canonical’ date format before the BI Publisher’s date related function comes in. Here is an example of how to use to convert the date.

psfn:totext(ssCreated,"yyyy-MM-dd","MM/dd/yyyy")

The above example is giving the Siebel date data, telling what date format the presented date data in, and telling how to convert. So if the ‘ssCreated’ is ‘10/09/2008’ then it would return as ‘2008-10-09’, which is the ‘Canonical' date format. Once you get your date data in the ‘Canonical’ format then you can do the date formatting or use the date functions to meet your requirements.

The advantage of this option is easy and quick to implement and you don’t need any customization. The disadvantage of this option is that as you see it gets messy especially when you want to do a date related calculation in a complex business logic because you need to specify the date formats every time you use it.

The second is to develop a custom Java function to convert the date from, the Siebel’s date format to the ‘Canonical’ date format. As you might know BI Publisher supports a way for you to develop custom functions with Java and use them in the RTF template. The advantage of this option is clean and easy to use. The disadvantage is that you need to develop the Java code and deploy it to the server, which might require the system administrator’s help if you don’t have an access to the server instance.

So now, at least you can get your date data work in the Siebel reports! And I will discuss on how to develop the custom Java function for Siebel reports tomorrow. So again, stay tuned!