How to change the date format in Oracle BI Publisher?

Question

How do I change the format of the date while creating a report using data model which I created using a simple SQL query which fetches aggregated data from a data warehouse? I need the date in a simple dd-mon-yy format. I am not able to locate where the format option exists in BI Publisher. The BI Component I am using is PivotTable.

The problem with order:

Although, I have ordered my data in the data model, still the data is not showing up in the proper order in the chart. The same thing is working fine on the data table. See below -

Answer 1

I need the date in a simple dd-mon-yy format.

Did you use the proper format model in your query?

For example,

If the date value in the database is 20150225, then you need to first convert it to date using TO_DATE and respective FORMAT MODEL.

to_date('20150225','YYYYMMDD')

In order to display the date in your desired format, just use TO_CHAR and desired format model:

to_char(to_date('20150225','YYYYMMDD'),'DD-MON-YYYY')

Have a look at this article.

Update Regarding alphabetical sorting on month in chart

Found the following information on web:

If you are designing chart using RTF template, please try this:

1. Right click chart image - click size - click AltText tab
2. Copy the chart code into a notepad
3. Look for the tag ColLabelsIt will be something like this:

ColLabelsxsl:for-each select=".//G_1" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"xsl:sort order="ascending" select="FIRST_NAME"/Labelxsl:value-of select="FIRST_NAME" //Label/xsl:for-each/ColLabels

Please see the sorting order that has been highlighted. In sorting order, you can add the field/column that is holding the month/year combination. If your chart is using group function then xsl:sort order="ascending" select="current-group()/FIRST_NAME"/