Wednesday, 23 May 2012

Formatting time in BI Publisher

This article describes a mechanism to format time in BI Publisher.
Let me explain with an example
I have a XML file with the following tag in it.
The requirement is to display this time as 6:00PM

This value is displayed in the start column of the following table

I found the following code at one of the places to get this done

<?xdofx:if to_number(substr(SsStart,1,2))<=12 then to_number(substr(SsStart,1,2))+10-10 else to_number(substr(SsStart,1,2))-12 end if?><?concat(':',substring(SsStart,4,2))?><?xdofx:if to_number(substr(SsStart,1,2))<12 then ' AM' else ' PM' end if?>

While this is a good effort, I think it is too tough to develop this kind of logic esp if you are as lazy as I am :)

So here is what I did.

      1.       Concatenated my time string with a hardcoded date
      2.       Converted the whole string into canonical format
      3.       Used msword date format to get the desired result

My code:

<?psfn:getCanonicalDate(concat('01/01/2012 ',SsStartTime))?>

Here SsStartTime is the node that holds the time component. '01/01/2012 ' is a random date. Note the space in the end of the date. The space is reqd to get the correct output from getCanonicalDate function. getCanonicalDate is a Siebel extension function which returns the date in the canonical format. We obviously have to include the namespace for using this. Below is the namespace declaration for siebel extension functions.

Check this link to know more about using functions defined in a jar by including namespaces in rtf files

If the date is not in the canonical format then msword date formatting cannot be applied. If you are not developing BI Publisher reports for Siebel then form the canonical date string by the using the usual concat function  and format masks for date conversions. Canonical format is 'YYYY-MM-DD' or 'YYYY-MM-DDTHH:MI:SS'

Once this is done, use msword form field properties as shown in the screenshots above. Select h:mm am/pm as the format and Date as the type.

Till next time


Anonymous said...

Hi Vishal,

Can you please let me know how can I translate if I use the time format mentioned by you.
I am using
for my requirement and now I am working on the translation of the report. I tried using

but in the report the date is also displayed.

Please help.

Thanks in advance.

Vishal Pathak said...

Well difficult to comment without your input XML.
Match it with the SsStartTime tag shown in this blog.
In any case, you can always use standard manipulation functions.

Example of one such manipulation can also be seen in the blog

Anonymous said...

plz tell me how to use NVL function
in xml publisher
i require
select sum(nvl(comm,0)) from emp group by deptno
i used

but it is showing only 0 for all
please advise