Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Combining Multiple Excel Report Outputs(Workbooks) to a Single Excel Report – Using BI Delivers, BI Publisher APIs and VB Scripts

Posted by Venkatakrishnan J on February 7, 2008

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

package bieesoap;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import oracle.apps.xdo.delivery.DeliveryException;
import oracle.apps.xdo.delivery.DeliveryManager;
import oracle.apps.xdo.delivery.DeliveryPropertyDefinitions;
import oracle.apps.xdo.delivery.DeliveryRequest;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJavaExtension;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobException;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobInfo;

import java.io.File;

import oracle.apps.xdo.delivery.local.LocalPropertyDefinitions;

public class ExcelOutput implements SchedulerJavaExtension{
public ExcelOutput() {
}

public void run(SchedulerJobInfo jobInfo) throws SchedulerJobException {
try
{
String OutputFileName;
OutputFileName = “D:\\Work\\Report” + jobInfo.jobID() + jobInfo.instanceID() + “.xls”;
FileInputStream fis = new FileInputStream(jobInfo.getResultSetFile());
DeliveryManager dm = new DeliveryManager();
DeliveryRequest req = dm.createRequest(DeliveryManager.TYPE_LOCAL);
req.addProperty(LocalPropertyDefinitions.LOCAL_DESTINATION, OutputFileName);
req.setDocument(fis);
req.submit();
req.close();
}
catch(Exception ex)
{
throw new SchedulerJobException(1, 1, ex.getMessage());
}

}

public void cancel() {
}

}

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.

Sub MergeExcel()
Dim finalOutput As Workbook
Dim sourceWbk As Workbook
Dim sourceWsh As Worksheet
Dim sourcePath As String
Dim sourceFile As String

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
sourcePath = “D:\work” ‘ change to suit
Set finalOutput = Workbooks.Add(xlWBATWorksheet)
sourceFile = Dir(sourcePath & “\*.xls”, vbNormal)

If Len(sourceFile) = 0 Then Exit Sub

Do Until sourceFile = “”

Set sourceWbk = Workbooks.Open(Filename:=sourcePath & “\” & sourceFile)

Set sourceWsh = sourceWbk.Worksheets(1)

sourceWsh.Copy After:=finalOutput.Worksheets(finalOutput.Worksheets.Count)

sourceWbk.Close False

sourceFile = Dir()

Loop
finalOutput.Worksheets(1).Delete
finalOutput.SaveAs (“D:\work\Test.xls”)
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

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.

Dim app
Set app = createobject(“Excel.Application”)
app.visible = false
app.Workbooks.Open(“D:\Final.xls”)
app.Quit
Set app = Nothing

Save this file in {OracleBI}\server\Scripts\common folder.

5. Now create a third ibot which would just call the above VBScript.

      

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

Advertisements

6 Responses to “Oracle BI EE 10.1.3.3/2 – Combining Multiple Excel Report Outputs(Workbooks) to a Single Excel Report – Using BI Delivers, BI Publisher APIs and VB Scripts”

  1. Qinghe said

    Hi Venkata,

    I have a requirement to combine fields from two reports to generate one single report. These two reports are obtained in different way but with the same granularity.

    Report 1

    123 D1 M1

    Report 2

    123 D2 M2

    and we want to combine these two separate reports into one report:

    123 D1 M1 D2 M2 M2-M1

    Can your technique be used to solve this?

    Thanks a lot,
    Qinghe

  2. njethwa said

    I am still new to OBIEE but take a look at this Excel Dashboards

    Regards
    Nilesh
    Dashboards

  3. anusha said

    i have two reports that has to be combined.But those are in word format.
    these reports have different report layouts, different sorts, different everything.
    is it possible to concatenate them.

  4. pranay said

    I have a query on the report generation in xls. Is there a way to catch the report name dynamically from the java program used to generate the xls?

  5. Nicolas GERARD said

    Thanks. Great example. Just to let this information. You have the class path for the BI Publisher API in this file OracleBI_Hone\web\javahost\config\config.xml in the node loader / xmlp.

    XMLP
    com.siebel.analytics.web.javahostrpccalls.xmlp.XmlpComponentLoader
    XMLP
    {%javahostdir%}/lib/sawrpccalls/sawrpccalls.jar;{%javahostdir%}/lib/sawrpccalls/xmlp5.6.1/aolj.jar;{%javahostdir%}/lib/sawrpccalls/xmlp5.6.1/xdo.jar;{%javahostdir%}/lib/sawrpccalls/xmlp5.6.1/xmlparserv2.jar;{%javahostdir%}/lib/sawrpccalls/xmlp5.6.1/bicmn.jar;{%javahostdir%}/lib/sawrpccalls/xmlp5.6.1/bipres.jar;{%javahostdir%}/lib/sawrpccalls/xmlp5.6.1/i18nAPI_v3.jar;{%javahostdir%}/lib/sawrpccalls/xmlp5.6.1/jewt4.jar;{%javahostdir%}/lib/sawrpccalls/xmlp5.6.1/xdoparser.zip

  6. Curious said

    Hi

    Is it possible to do the combining in 1 single vbscript instead ? So that the file directory can be passed in as a parameter instead of hard-coding it in the excel macro

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: