Thursday, January 27, 2011

BI Publisher: Formatting Date


We can format dates in BI Publishes in the following three different ways,

1. Formatting Date using Microsoft Word’s native date format mask.
2. Formatting Date using Oracle’s format-date function.
3. Formatting Date using Oracle’s abstract date format masks.
4. Format with Calendar.
    Lets me now explain little details about the above three ways, and below is the XML I am going to use in the below examples,
    <ROWSET>
        <NORMAL_DATE>2011-01-26</NORMAL_DATE>
        <DATE_UTC>2011-01-26T18:00:00-08:00</DATE_UTC>
        <DATE_TEXT>26/Jan/2011</DATE_TEXT>
    </ROWSET>


    1. Formatting Date using Microsoft Word’s native date format mask.
    In the BI Publisher Properties window, select type as Date and select the format from the drop down box.
    image

    Here is the output sample of all the three types of date’s we had in the XML. Format I selected is
    d-MMM-yy

    image

    2. Formatting Date using Oracle’s format-date function.
    format-date() is an inbuilt function in BI Publisher.
    Syntax:
    <?format-date:fieldname;MASK)?>
    In the above syntax MASK is an optional parameter. We will see more details about MASK later in this post.

    To use this format-date function, under the BI Publisher Properties window -> keep type as Regular Text -> click on Advanced tab –> call format-date function with the tag name. (For time being we will not pass the MASK parameter.)
    image
    Here is the output sample of all the three types of date’s we had in the XML.
    image

    3. Formatting Date using Oracle’s abstract date format masks.
    In this section we will seeing various MASK parameters that can be passed to <?format-date:tagname;’MASK’?> function.

    Out of box we have the following MASK available for various format of dates,
    Mask Constant Output
    SHORT 1/26/11
    MEDIUM Jan 26, 2011
    LONG Wednesday, January 1, 2011
    SHORT_TIME 1/26/11 8.10PM
    MEDIUM_TIME Jan 26, 2011 8.10PM
    LONG_TIME Wednesday, January 1, 2011 8.10PM
    SHORT_TIME_TZ 1/26/11 8.10PM GMT
    MEDIUM_TIME_TZ Jan 26, 2011 8.10PM GMT
    LONG_TIME_TZ Wednesday, January 1, 2011 8.10PM GMT

    The default MASK constant is MEDIUM.

    In order to user these abstract’s you need to have your XML date data in canonical format as,
    YYYY-MM-DDThh:mm:ss(+/-)HH:MM
    (For example check out DATE_UTC tag value in the above XML.)

    Here,
    YYYY is the year
    MM is the month
    DD is the date
    T is the separator between the date and time component
    hh is the hour in 24-hour format
    mm is the minutes
    ss is the seconds
    (+/-)HH:MM is the time zone offset from Universal Time (UTC) or Greenwich Mean Time

    Here is the output for our XML data using the MEDIUM_TIME mask,
    image

    Now if you notice the output that we got earlier while using format-date with the default parameter, column for date_utc alone is showing Jan 27 were as the date we have in the XML is 26.

    This is because of the time zone offset –08:00.

    Where this timezone offset is nothing but, the time zone difference of the date that we passed in the XML with respect to GMT. So BI Publisher will automatically understand the timezone of the XML data, and print/convert it as per the BI Publisher Local Server time.

    How to get Time zone offset of the data?
    Ok now we know its important to pass the time zone offset in the XML data to use the MASK. Lets see how to get them in the XML data,

    First Option:
    If your data source is a data definition file, then no need to worry, the standard BI Publisher data definition engine by default will give the data in the canonical format with time zone offset. So you are good. No need of any extra coding.

    If you do not want the template to change the date as per the time zone then, user the TO_CHAR function to print without the time zone offset. So that template engine will not modify the date as per the server time zone.

    Second Option:
    Lets say your data source is an RDF file. In that case use TO_CHAR function in your SQL query and bring the data in required canonical format.

    Third Option:
    Lets say, you do not have control over the data source. You have only access to the template and know what will be the time zone of the data that is going in come in the XML.

    In this case you can use the third optional parameter in the format-date function where you can pass the time zone name like this,
    <?format-date:NORMAL_DATE;’MEDIUM’;’America/Las_Angeles’?>

    If do not want the time zone and still want to use the abstract format, then specify UTC in place of time zone.
    <?format-date:NORMAL_DATE;’MEDIUM’;’UTC’?>

    4. Format with Calendar
    Now that we had seen different types of formatting dates, let finally see how to use the above formats with various calendars.

    For this we again have one more buitin function,
    <?format-date-and-calender:tagname;’MASK’;’CALENDAR_NAME’?>

    Here is the supported calendar types,
    - GREGORIAN
    - ARABIC_HIJRAH
    - ENGLISH_HIJRAH
    - JAPANESE_IMPERIAL
    - THAI_BUDDHA
    - ROC_OFFICIAL (Taiwan)


    I think, I had covered all the date formatting techniques in BI Publisher. If you feel I had missed anything or would like to explain some specific example, feel free to put on the comments.


    4 comments:

    Michael Coughlin said...

    We have a BI Publisher report with this function in the RTF:



    This is NOT reading the database, is it? Because it is different from the sysdate of the DB.

    It is one hour behind. I am thinking it is missing daylight savings. Do you have any idea why?

    Michael Coughlin said...

    We have a BI Publisher report with this function in the RTF:

    ?xdoxslt:sysdate('DD-MON-YYYY HH24:MI')

    This is NOT reading the database, is it? Because it is different from the sysdate of the DB.

    It is one hour behind. I am thinking it is missing daylight savings. Do you have any idea why?

    Migaja said...

    It reads date and time from server not from database.
    If you need specific time, timezone use: format-date.
    Cheers

    Anonymous said...

    I have been surfing on-line more than 3 hours nowadays, yet I
    by no means discovered any fascinating article like yours.

    It is lovely price enough for me. Personally,
    if all website owners and bloggers made good content as you did, the
    internet shall be much more useful than ever before.

    Post a Comment