nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Formatting Data in BI Publisher report

Antoine KAIBER

Summary

How to give a good format to date in report

Content

Hello,

I have a report (which I did not create) on Oracle BI Publisher 11.1.1.9.0 and when I launch my report from OTM (software i'm using) without any format I get the right time for the planned arrival date (16:00 -> 4PM) but when I put the format as shown on the screen, I have 17:00 (5PM). Does anyone know how to change that ?

2018-06-25 12_40_48-PEI.GR_PLANNING_EXPEDITION-5.xlsx [Read-Only] - Excel.png

Find more posts tagged with

Reporting

Home and Catalog

Accepted answers

All comments

FPonte

Hi Antoine.

Are you able to tell us what is the Timezone setting for your account on BI Publisher preferences?

You may also find this useful.

https://docs.oracle.com/middleware/12212/bip/BIPRD/GUID-7BD3C665-851A-43FE-BA2D-7501390D95AC.htm#BIPRD2565

Please read the first NOTE.

Regards.

Fernando

Antoine KAIBER

Hello Fernando,

Thank you for your answer, my timezone in BIP is GMT+1 but even if I change it, this has no effect on my report.

When I launch my report with old data (old shipments), the time displayed is correct but apparently for recent shipments this doesn't work. So the problem must not come from BIP but OTM (the software i'm using)...

2018-06-26 14_03_12-Oracle BI Publisher _ Accueil.png

FPonte

Hi Antoine.

Can you try the following (if you have access to do so)?

1. On the Data Model navigate to the properties of your date attribute and set the Date Format to "Date & Time" (Usually it defaults to timezone option).

2. In the Report Template try change the following for the date attribute: Data Formatting > Ignore User Timezone = True

Have a go and let us know the results.

Cheers.

Fernando

Antoine KAIBER

Hello Fernando,

I've already tried changing the timezone the "Ignore User Timezone" setting to True in the properties but it doesn't change anything. I haven't seen the Date & Time attribute on the report.

Update: the bug seems to occur for every report, on recent shipments only (old shipments aren't concerned by the +1 hour).

When I download the XML of the report, the hours is the right one, but when I launch the report and extract it via excel the bug seems to occur. The thing is, it does is for every user, not only me so it doesn't seem to be coming from excel.

It appears there is an .xpt template linked to these reports but I can't open them

2018-06-27 10_46_25-GR_Planning_Expedition - Oracle BI Publisher _ Etat.png

Antoine KAIBER

Where do you have the "Date format" field ? I only have these fields :

2018-06-27 14_55_50-GR_Planning_Expedition - Oracle BI Publisher _ Etat.png

FPonte

Hi Antoine.

The "Date Format" is in the Data Model of your report. If you have access to it just click in the cog icon on the right of the attribute and then go to properties.

More here.

Setting Element Properties

https://docs.oracle.com/middleware/12212/bip/BIPDM/GUID-6B54EDCE-5F3A-4A1E-89F0-99BAEA6FD43C.htm#BIPDM274

Cheers.

Fernando

Antoine KAIBER

Hello Fernando,

Thanks for your help, i've changed the properties of my date fields to "Date and Time" but unfortunately it doesn't change anything. I'll continue searching

FPonte

Hi Antoine

Not sure why you are getting that differently.

I did some demo before replying and almost all dates worked for me. This is my example. (I'm in New Zealand timezone)

Figure 1 shows the results of my report.

Figure 2 shows the properties of my report. Just to check I am accessing the latest model and template I unchecked the "User Level" and "Document Caching" in the report Cache properties.

Cheers.

Fernando

FPonte

Figure 2

Antoine KAIBER

Hello Fernando,

I've used the same parameters as you (except the formatting mask I have is Short_Time and not Medium_Time like you, I don't know what it corresponds to) and still the time doesn't change.

I've also changed the format of the field in my report to "date only" to see if it had an impact and, indeed, instead of having "02/05/18 17:39" I had "02/05/18 01:00", so this changed the hour of the day but didn't actually delete it as you would expect.

In the report properties, in "formating" what do you have ? I have a choice between JVM and User, it was orignally set to user.

I must also add that, as I said, this happens on more recent shipments, so I think the change ocurred when we went from winter time to summer time

2018-06-29 11_34_23-SCE_PROVIDER_PLAN_REPORT_P_D - Oracle BI Publisher _ Etat.png