3

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.

enter image description here

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 -

enter image description here

MontyPython
  • 2,906
  • 11
  • 37
  • 58

3 Answers3

2

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"/

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • The solution does work but going on the same lines, I converted the month into 'Mon-Yy' and created a chart - now the months are out of order - they are in alphabetical order! – MontyPython Feb 25 '15 at 06:08
  • Use to_char only in select, while you order, just order by the original date column. For example, `SELECT to_char(hiredate, 'DD-MON-YYYY') FROM emp ORDER BY hiredate; ` – Lalit Kumar B Feb 25 '15 at 06:20
  • Thanks, I am aware about how we do it generally. So, essentially the publisher will take the data in the same order as in the data model. We cannot change the order on the front end. Is that right? – MontyPython Feb 25 '15 at 06:39
  • The tool will take the same format as that of your locale specific NLS_DATE_FORMAT – Lalit Kumar B Feb 25 '15 at 06:44
  • Although, the data table is showing me the same order as prescribed in the data model that I'm using, the chart is still showing the months basis alphabetical order. – MontyPython Feb 25 '15 at 06:53
  • No, that was earlier. I've already changed the settings to Dd-Mon-RR now. Also, now that I don't have a date column, it should not matter. The date is in 'Dd-Mon-Yy' and the month is in 'Mon-Yy' - both explicitly converted into char using to_char – MontyPython Feb 25 '15 at 07:00
  • I used the basic template provided by OBIEE. The format is .xpt – MontyPython Feb 25 '15 at 07:30
0
  1. Select the column ( type : date)
  2. Click on properties (left Bar)
  3. Change: formating Style (Oracle) and formating Mask ( DD/MM/YYYYY)

enter image description here

Andreas
  • 5,393
  • 9
  • 44
  • 53
tetito007
  • 11
0

It is very simple. Follow the navigation.

Double click the field-> Select Type as Date and Date Format as dd-MMM-yyyy. This should resolve your issue

I wanted to post a image, but no reputation.

How this helps..

user2198624
  • 11