In May 2022, DSP, the UK's Leading Oracle Partner acquired Claremont to further expand their Oracle expertise. 
The NT uses Oracle E-Business Suite as its CRM System to manage member transactions. Our Managed Services team reduced the support backlog of incidents, problems, and changes in the NT’s CRM system.
Claremont has provided a solution capable of booking, managing, maintaining and analysing Unite Students’ complete property portfolio, allowing them to significantly differentiate themselves from their competitors.
Stonewater chose DSP to consolidate and migrate their assets to Oracle Cloud Infrastructure with an Oracle EBS Cloud Deployment, leading to a significantly reduced TCO.Contents
Is British Summer Time Impacting Your Reporting?
What do you do when a weird date issue has been hanging around on your system for 2 years and you've been unable to come up with a solution? You fire it off to the Claremont Database Administrator (DBA) team and they will find the answer!
Oracle BI Publisher is one of the reporting solutions that manages and delivers all your reports and documents easier and faster than traditional reporting tools.
Having implemented BI Publisher 12c and been enjoying all the wonderful reports it brings, users complained that when they exported the reports to Excel, some of the birth dates were being incorrectly reported as a day earlier. The data all looked fine in Oracle's Business Intelligence Enterprise Edition (OBIEE) so it was a confusing issue.
Over the course of 2 years, the client's usual application support partner had been investigating the issue, but failed to find a resolution. As a last-ditch resort, Claremont was asked to upgrade BI Publisher from 12.2.1.2 to 12.2.1.4 and apply a series of one-off patches. While this wasn't necessarily a bad thing to do, it didn’t fix the core date issue.
Thankfully, Claremont’s DBA team think a little differently to competitors’ support teams and are not happy without a fix! So rather than getting buried in Oracle support notes, they took a different tack. First port of call was to take a closer look at the Excel data which revealed that the date in Excel was not exactly what was being displayed:
Although the date was being displayed as 03/06/1970, it was actually "03/06/1970 23:00", so the Date of Birth was not being translated as 1 day earlier, but only 1 hour earlier. Reviewing all the dates showed that this only happened for birth dates around 1970, so the question was, why were those the only dates being affected?
The answer to that is surprisingly due to a "Period of Deviation" of British Summer Time. Between 27 October 1968 and 31 October 1971, the UK remained at GMT+1 throughout the year, with a resulting impact of birth dates during the summer months appearing to be stored in raw format as 1hr earlier compared to what we'd expect.
Given that BI Publisher is reporting the dates correctly, we concluded that BI Publisher was rounding up the raw data correctly, but Excel was rounding it down and thus showing the wrong date. Should the raw data be changed? Or do something clever in Excel to change the date rounding? Actually, we did neither!
The clue to what we did lay in My Oracle Support Doc ID 2455582.1. This suggested that patch 28006490 might fix the issue, but as that was already applied, it clearly didn’t fix it. However, the workaround mentioned suggested that we could instruct the Excel download operation to user Formatted rather than Raw values in the export. That sounded like a winner as we didn't need to change the raw data or make any config changes to Excel.
It's not who we are, it's what we do that defines us
The Fix
Edit the $DOMAIN_HOME/config/fmwconfig/biconfig/OBIPS/instanceconfig.xml
Add a new entry to the root WebConfig Tag
<WebConfig xmlns="oracle.bi.presentation.services/config/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
Add a new Download section with "UseFormattedValue" to override the default "UseRawValue"
<Download>
<Export xsi:type="excel">
<DataValue>UseFormattedValue</DataValue>
<RepeatRows>false</RepeatRows>
</Export>
<Export xsi:type="formattedText">
<Delimiter char=","/>
</Export>
<Export xsi:type="pdf">
<KeepRowsTogether>true</KeepRowsTogether>
<Orientation>Landscape</Orientation>
</Export
<Export xsi:type="ppt">
<Orientation>Portrait</Orientation>
</Export>
</Download>
Restart OBIPS1 and retest.
The Result
Perfect. The time part of the date has been removed and it's now being exported exactly as formatted by BI Publisher.
References:
- Oracle® Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition
- OBIEE 12c: When Exporting Date Columns To Excel 2007+ It Showing One Day Behind With Time Stamp (Doc ID 2455582)
Choosing the right Managed Services Provider
If you are looking for an Oracle partner who can help you with your Oracle Managed Services and goes about it the right way and can back up the talk, then contact us. If you would like to find out more about the E-Business Suite updates or have a question, you can email us at info@claremont.co.uk or phone us on +44 (0) 1483 549004