Friday, January 29, 2010

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.


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!


  1. Hi there...
    do u have any idea how do we sort on dates.

  2. You can use the MS-Word Addin, BI Publisher Template Builder to do the sorting with its UI. Or, you can just use the following syntax.

    The above does the sorting on 'CREATED_DATE' column in ascending way. You need to have this inside 'for-each' loop.

  3. Hi,
    Thanks for the reply
    do we need to have this in sorting property of for each.

    i tried this but it gave syntax error.


  4. Yes, you need to do that in the sorting syntax. Or you can put the value into a variable then use the variable in the sorting syntax.


  5. Just realized that all my XML are erased by this blog service. Maybe something to do with the tag.

    Anyway, retry...


  6. "sort:v_date;'ascending';data-type='text'"

  7. Thanks a lot i will give it a shot..trying to get ldap authentication right(siebel BI reports)

  8. Hi..
    I got it right.
    But the scheduling in siebel oracle BI publisher does not work properly.
    if i try to schedule a report for say every sunday at some doesnt work
    it cant take the date properly.
    u have any idea of this


  9. Thanks Kanichiro!

    I am in the process of converting our actuate reports to BIP. I do not understand why all this date handling is such a pain. It was a pain already in Actuate, and now it is an even bigger pain in BIP.

    Thanks for the post, you saved my live!

  10. I hear you Benjamin. I heard that the Siebel reporting produce development is working on to change the IO so that it will produce the date data in the 'Canonical' format. Then you don't need to do this workaround, and the date handling would be much simple. So we're getting there, so hold tight! I will keep update on that.

  11. Hi Kanichiro,

    The date format posts proved to be very helpful to me for my BIP report. Can you please suggest, if I were to format the date as 25-May-2010 6:00 PM, how would I do that? I believe in US locale, you can't do that. And, I did find a list of masks that you can use, but couldn't come closer to what I want to accomplish.

    Any help is greatly appreciated.

  12. Hi Kanichiro,

    After converting from Actuate to BIP, the rtfs I have automatically have the date in the Cannonical format i.e. they are making use of the psfn:totext() function. Although, while trying to preview the report in BIP Desktop in MS word, it is throwing a java error at all the places wherever the text "psfn" is present.

    Kindly suggest how do I preview the report without having to actually delete this field from the rtf.

  13. hello, I have a question, I have to work with Siebel to make a report with IO, the XML file print the date in the format MM / DD / YYYY you said there using that formula there "psfn: totext (ssCreated," yyyy-MM "dd", "MM / dd / yyyy") "where is this formula? in OI? in BC? in the report template?, and once I have the canonical XML format, which makes use in the report template to generate the report when I print the date in the format DD / MM / YYYY??

    I expect quick response, salu2.