Oracle Analytics Cloud and Server

Products Banner

Formatting Data in BI Publisher report

Received Response
303
Views
17
Comments

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

Answers

  • FPonte
    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
    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
    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

    MyDate.JPG

  • Antoine KAIBER
    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
    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
    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

    MyDateProperties.JPG

  • Antoine KAIBER
    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
    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

    MyDateDemo1.JPG

  • FPonte
    FPonte ✭✭✭✭✭
  • Antoine KAIBER
    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

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    As for the SQL, this is what I have for instance :

    select distinct

    shs.attribute_date1 as Arrival_Plan_stop,
    (utc.get_local_date(shs.ESTIMATED_ARRIVAL,shs.LOCATION_GID)) as Arrival_Estimate_stop,
    (utc.get_local_date(shs.planned_departure,shs.LOCATION_GID)) as Departure_Plan_stop,
    (utc.get_local_date(shs.estimated_departure,shs.LOCATION_GID)) as departure_estimate_stop,

    from...

     

    But I didn't check for all the reports, but they seem to all have had this update on time at the same time

  • FPonte
    FPonte ✭✭✭✭✭

    Hi Antoine.

    My default for Report Timezone is JVM. That is on the FO processing, not sure it will apply on this case.

    If you can test move your timezone one hour less and the results should display as expected. This will make a case to Oracle to see that some or the report properties are not been respected when you change them.

    Cheers

    Fernando

    MyDateDemo4.JPG

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    Hi Fernando,

    Ok I tried with JVM as well but it didn't work. I also already tried changing my timezone, directly in OTM (the concerned information system) and BIP but it had no impact unfortunately.

  • FPonte
    FPonte ✭✭✭✭✭

    Hi Antoine.

    So many places to change the timezone and none is being fully respected by BIP.

    I know that if you change some of your properties you need to log off and log in again for them to take effect.

    Are we missing something!?

    Fernando

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    Hi Fernando,

    Yes I have disconnected and reconnected but it still doesn't work. I found this link which apparently is about this same issue but on Oracle Sales Cloud : https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=269643864077291&id=2328335.1&_afrWindowMode=0&_adf.ctrl-state=125hed2gci_4

    I'm wondering whether I should make a service request or not actually

  • FPonte
    FPonte ✭✭✭✭✭

    Hi Antoine.

    I would open an SR. They may have some additional information that we may be missing.

    One additional test. The report properties.

    How is the last property configured to you, the one at the bottom of the Formatting Tab.

    My reports where upgraded from 11g 1.1.5 all the way to 12c and some still hold the setting "Use 11.1.1.5 compatibility mode" equals TRUE.

    This caused troubles to me but once we set it to FALSE the date works fine.

    Best Regards.

    Fernando

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    Hi Fernando,

    Thanks for your help, unfortunately this compatibility mode was set to False so it doesn't seem to be the solution.

    I will see for an SR and if I have an answer I will keep this thread updated, even though i'm not in charge for SR's so it might take some time.

    Thanks again,

    Antoine