Column Aliases in BI Publisher


Problem:

If you create a dataset and build your SQL-query by Query Builder or type it manually, you have to watch out that you do not use column aliases like ‘FEED’.

See example below:

SQL-query:

select BI_TABLE.FEED as FEED,
      BI_TABLE.FEEDBACK as FEEDBACK,
      BI_TABLE.APPLICATION_ID as APPLICATION_ID,
      BI_TABLE.NAME as TYPE_NAME,
      BI_TABLE.DESCRIPTION as TYPE_DESCRIPTION
from HR. BI_TABLE;

XML-file:

Internet Explorer seems to perform a strict XML validation. He gives you following error:


Solution:

Replace all column aliases with names like ‘FEED’ and your problem is solved. See solution below:

SQL-query:

select BI_TABLE.FEED as COLUMN_01,
      BI_TABLE.FEEDBACK as COLUMN_02,
      BI_TABLE.APPLICATION_ID as APPLICATION_ID,
      BI_TABLE.NAME as TYPE_NAME,
      BI_TABLE.DESCRIPTION as TYPE_DESCRIPTION
from HR. BI_TABLE;

XML-file:


Note: You can use ‘FEED’ as name for database tables but remember, not for column aliases!!!

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.