Many users of OBI like the functionality of being to export OBI reports into excel. However, it can be frustrating for the user when excel automatically merges cells for them.

For example, the OBIEE report is doing a group sort by year, brand and then a sum by individual office.   With this group sort, the year and brand value are not repeated in each row and only display when the brand or year changes as shown in the screen shot below:

OBI Report

When  exported to Excel, it retains the distinct group sort look of the OBIEE  report, as shown below, but the preferred Excel display is to repeat the group sort columns in every row so that every cell in every row has a value.

excel export 1

In the instanceconfig.xml file located in <EXALYTICS/OBIEE_HOME>/\Middleware\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1, change the following:

From:

<WebConfig xmlns=”oracle.bi.presentation.services/config/v1.1″>

To:

<WebConfig xmlns=”oracle.bi.presentation.services/config/v1.1″
xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>

And add the following lines anywhere in between <ServerInstance> and </ServerInstance>

<Download>
<Export xsi:type=”excel”>
<DataValue>UseFormattedValue</DataValue>
<RepeatRows>true</RepeatRows>
</Export>
</Download>

Restart OBIEE.

After making these changes, the OBIEE report format will remain in group sort (merged cells) format, but the Excel report will show repeating values for the group sort as shown below:

Excel end resultQuestions or comments? Please feel to email me below.

Kiel Briggs kbriggs@ecapitaladvisors.com

Reference: Doc ID 1930660.1