Oracle Analytics

Get Involved. Join the Conversation.

Topic

    Antoine KAIBER
    Formatting Data in BI Publisher report
    Topic posted June 25, 2018 by Antoine KAIBERSilver Medal: 2,000+ Points, last edited September 11, 2019, tagged Home & Catalog, Reporting 
    1399 Views, 17 Comments
    Title:
    Formatting Data in BI Publisher report
    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 ?

     

    Image:

    Comment

     

    • Fernando Ponte

      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)...

         

         

    • Fernando Ponte

      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

    • Antoine KAIBER

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

       

    • Fernando Ponte

      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

    • Fernando Ponte

      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

    • 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

    • 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

    • Fernando Ponte

      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

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

      • Fernando Ponte

        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

          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

          • Fernando Ponte

            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

              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