Oracle BI Publisher Date-type

You can only format dates when they are in the canonical date format. This format looks like: YYYY-MM-DDTHH24:MI:SS.

You have two options:
1. adapt the XML generation process to generate the date in the canonical format and use the format date functions of BI Publisher in the template.

2. adapt the XML generation process to generate the date in the format of the client (example: DD-MM-YYYY) and print this directly on your report.


Solution 1:

First, we create a new dataset. We give it a name and select SQL Query as Type. Then choose a ‘Data Source’ and check ‘Cache Result’.

We build a query by Query Builder in BI Publisher or type it manually. See below to our ‘Data Set’ -query.

Note: XML-Date is in the canonical format and can be imported directly in the template. Example below is based on a ‘sysdate’ and stored in a field called ‘FULL_DATE’.

DS_Query:

select BI_DATE.FULL_DATE as FULL_DATE
from HRM.BI_DATE BI_DATE

After we create a dataset, we generate an XML to build our RTF-template.

XML-type:

&lt?xml version=”1.0″ encoding=”UTF-8″ ?&gt
&ltROWSET&gt
   &ltROW&gt
      &ltFULL_DATE&gt2008-02-19T13:24:37.000+01:00&lt/FULL_DATE&gt
   &lt/ROW&gt
&lt/ROWSET&gt

Properties:

Load XML into MS Word (with Oracle BI Publisher Desktop). Import the field ‘FULL_DATE’ and edit the properties. We can choose different format-types in the drop-down box. Choose one for example: ‘d-MMM-yy’ or type your own format-type like ‘dd-mm-yyyy’.

Note: Field ‘FULL_DATE’ is a canonical format.

Results in Word:

Solution 2:

First, we create a new dataset. We give it a name and select SQL Query as Type. Then choose a ‘Data Source’ and check ‘Cache Result’.

We build a query by Query Builder in BI Publisher or type it manually. See below to our ‘Data Set’ -query.

DS_Query:

select BI_DATE.FULL_DATE as FULL_DATE,
   to_char(BI_DATE.FULL_DATE) as CHAR_DATE,
   to_char(BI_DATE.FULL_DATE,’dd-mm-yyyy’) as FORMAT_DATE,
   to_char(BI_DATE.DAY_DATE,’DD’) as DAY_DATE,
   to_char(BI_DATE.MONTH_DATE,’MM’) as MONTH_DATE,
   to_char(BI_DATE.YEAR_DATE,’YYYY’) as YEAR_DATE
from HRM.BI_DATE BI_DATE

After we create a dataset, we generate an XML to build our RTF-template.

XML-type:

&lt?xml version=”1.0″ encoding=”UTF-8″ ?&gt
&ltROWSET&gt
   &ltROW&gt
      &ltFULL_DATE&gt2008-02-19T13:24:37.000+01:00&lt/FULL_DATE&gt
      &ltCHAR_DATE&gt19-FEB-08&lt/CHAR_DATE&gt
      &ltFORMAT_DATE&gt19-02-2008&lt/FORMAT_DATE&gt
      &ltDAY_DATE&gt19&lt/DAY_DATE&gt
      &ltMONTH_DATE&gt02&lt/MONTH_DATE&gt
      &ltYEAR_DATE&gt2008&lt/YEAR_DATE&gt
   &lt/ROW&gt
&lt/ROWSET&gt

Load XML into MS Word (with Oracle BI Publisher Desktop). Because we have set our date types in the query, we just can import the available fields without editing the properties.

Results in Word:

Solution 3:

XML-Date is not a canonical date format. Second solution for this problem is similar to solution 2. Except that we don’t edit the SQL-query and generate XML.

Load XML into MS Word (with Oracle BI Publisher Desktop). Import necessary fields and edit properties.

Example field ‘Full Date’:

Choose ‘Word Properties’:

Then ‘Add Help Text…’

Type following statement by ‘Help Key (F1)’: &lt?format-date: FULL_DATE;’dd-MMM-yyyy’;’Europe/Brussels’?&gt

Note: ‘Europe/Brussels’ is the time-zone of your region.

And click ‘OK’ -> ‘OK’.

Disadvantage: you have to apply this solution to every field on the report. If you do it on query-level, you can import fields without editing them in the template.

About these ads

4 thoughts on “Oracle BI Publisher Date-type

  1. Thanks for the information it has been very helpful.Would you update your post to show the canonical format as yyyy-mm-ddThh24:mi:ss instead of yyyy-mm-dd hh24:mi:ss.I had tried yyyy-mm-ddThh24:mi:ss minus the “T”. The result was it does not get recognized as a date in the template builder.BIP can also handle yyyy-mm-dd leaving off the timestamp. Thanks for your help,Todd

  2. Todd,I want to thank you for your comment.I have changed my blog to the correct canonical syntax.In Europe, we mostly use dd-mm-yyyy. That’s why I used this format in my blog.

  3. i have fetched the date value in form of text in YYYYMMDD, i need to print this on the XML report in any valid date format like MM-DD-YYYY.I tried to_date , Substr but no use plz tell me how to slove this.

  4. iam geeting the Xml output as mm/dd/yyyy (same as database format)and not the canonical format as yyyy-mm-ddThh24:mi:ss while generating XML file.Even i tried using TO_CHAR function in query which is giving to wrong format.I need to get the date format as DD/MM/YYYY. Can somebody give the solution for this

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s