Bookmark

About Me

I am Venkatakrishnan J, a Business Intelligence enthusiast working with Rittman Mead Consulting who likes blogging about acquisitions in the BI space, technical workings of the BI tools in general and Oracle Business Intelligence tools in particular. All the views expressed here are my own and does not reflect the views of Rittman Mead Consulting or Oracle. Going forward, i would be blogging at http://rittmanmead.com/blog.

I received this question a couple of days back. The question was “Is there any way to combine say 2 or more number of Report Outputs into a single Excel File automatically, instead of manual exports and copy paste?”. This question keeps coming a lot many times but there is no out of the box solution for this. Lets look at one possible approach today. Remember that this is not an easy way and would involve a call to a lot of components. I am blogging about it just to demonstrate the extensibility of BI EE. In order to achieve this we shall be using 2 reports. Our aim is to use BI Delivers and BI Publisher APIs to export both the reports in Excel 2000 format to a folder. And then use another ibot to call a VBScript to combine all the exported reports to a single excel file. All the ibots will be called one after the other.

1. So, lets start with the Java Code to export the reports in the form of Excel 2000. This Java Code has to be called from an ibot. For more details on how to go about calling Java Classes from ibots check my blog entry here. The code is given below

So what this basically does is, it accepts the output of an ibot and stores it in D:\Work with a dynamic file name based on the Job ID and the InstanceID (Something like this Report240.xls etc).

2. Now the next step is to create 2 ibots and each of them should be made to export 2 different files in Excel 2000 format. Ensure that both the ibots call the above java code using a bundled Jar. I have 2 different reports as shown below

3. Just save the above 2 ibots and make each of them to call the above Java Code. Once this is done, lets move away from BI Delivers and look at the VB Scripting part which would achieve combining of multiple excel files into one excel file. In order to do this, we shall start with a simple VBA or macro script given below.

So, what this basically does is it checks the directory D:\Work and combines all the excel files it finds in that directory into a single file Test.xls.

4. Remember the above is a VBA Script. BI Delivers can only call VB Scripts. Now, in order to call VBA Script, what we shall do is to create a dummy excel file called Final.xls in D:\(ensure that this excel file is not in the same directory as the above export files) and we shall create a macro using the above code in this Excel file. Our aim is to make the macro to run as soon as the Excel file is opened.

Now, in order to make this macro to run as soon as the Excel is opened, right on the Excel icon just above file menu and click on view code. Go to the Workbook and add the below code.

Private Sub Workbook_Open()
Run “MergeExcel”
End Sub

Then just save this file. Also, ensure that you have the security of this Excel file set to Low. Now open a text editor and add the below VB Script which will open the Excel file.

6. Now schedule all the 3 ibots to execute one after the other (you can either use ibot chains or just schedule them to execute one after the other). Its better to use ibot chains since they are synchronous in nature. Now you should see 3 excel reports in the D:\Work directory, 2 being the actual reports and the third being the Test.xls which would be a combination of the above 2.

But just remember the above would work only in windows since VBScript would not work in a non-windows environment