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
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.
Perfect. The time part of the date has been removed and it's now being exported exactly as formatted by BI Publisher.
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