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
Posts
