Need the job done? Call a Claremont Superhero

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 Suppot 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