Export Multiple OBIEE Reports into Single Excel File

Last week, my Boss came back with a new requirement of exporting multiple OBIEE reports to a single Excel file. Normally, we can achieve this by clicking on the Drop-down at the top right corner of the screen. Once you get the Drop-down, follow the path -> "Export to Excel" -> "Export Current Page".

  

But, this downloads the entire Page even with Prompt ('Execution Plan Name' in this case).

But, as per the requirement, only the Reports need to be extracted, and the individual "Extract" will not work in this case as we want to extract both the reports (or may be more) in the same Excel Sheet.

To achieve this, I have created one more page, let us call it 'DL', and same set of 2 reports (appended 'DL' in the name).

For the Header Report; which has 1 column less than the 2nd one, I have added 1 Dummy Column and kept the formula as ' ' i.e. (blank). And have also changed the background color of the column to white, so that when the Report is downloaded; the Dummy Column is not noticed.

I have removed the Title for the "Header" and "Detail". (However, it is entirely based on design requirements OR preference).

Now I have placed both the reports in the page "DL" and have made the hidden the page.

Now, my objective is to add a link in the 1st Page for downloading the entire 2nd Page i.e "DL" in this case. Scripting languages come handy in this sort of tasks, so here I have used JavaScript.

We have multiple options of exporting the file in a single Excel file like, exporting the file using either a Button or a Link. I will demonstrate both the options, but will be using only one option out of the two.

1. Button - 

You can use the below code to show a Button "Export Current Page" on the Dashboard.

<input id="idDashboardExportToExcelMenu" binit="true" role="menu" aria-activedescendant="popupMenuItem" aria-haspopup="true" style="width:175px;height:30px ; background-color: rgb(47, 95, 135); color: rgb(255, 255, 255); font-size:13px; font-family: Arial,sans-serif;  -moz-border-radius: 4px;

    -webkit-border-radius: 4px; cursor:pointer;border-radius: 4px; display:block; padding:4px;background-image:none; text-align:center;" onclick="return saw.dashboard.exportToExcel('/users/admin_user/_portal', 'DL', true);" role="menuitem" value="Export Current Page" type="button"/>

2. Link -

Use the below code to show a link "Export All" on the Dashboard.

<a name="ReportLinkMenu" title="Export to different format" href="javascript:void(null)" onclick="return saw.dashboard.exportToExcel('/users/admin_user/_portal', 'DL', true);">Export All</a> 

In this case, I will be using the 2nd option i.e. Exporting the file using a Link.

After running the report, when the user clicks on the "Export All" Link; he / she gets the desired Excel file as below: