OBIEE 11g – Unmerge Cells When Downloading OBI Reports to Excel

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:

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.

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

From:

To:

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:

Questions or comments? Please feel to email me below.