On a recent project my company decided to use Business Objects as our primary reporting tool, and for the most part it was the perfect fit. The only problem was around one specific set of reports; end users insisted that they should be presented as a multiple tabbed report—one showing current, the other showing historical information. Although each tab of the Business Objects report could be saved as separate Excel or HTML files, for example, we could not find an obvious method to do a direct copy of the Business Objects multiple tab report into a multiple-tabbed Excel report.
We then began to look around on the Internet and within the Business Objects Technical Support site for any relevant articles that may help us overcome this problem and keep our user community happy. After spending a fair amount of time looking at Business Objects, we came to the realization that the version we were using would only support a single tabbed report by default, so we began to look at what other options we had, some of our alternatives included:
- Creating a custom standalone Excel spreadsheet which would have a very complex embedded VBA macro that would gather the data and populate the relevant tabs when executed via a scheduler.
- Writing an on-demand Web version that could use the Excel ActiveX control on the client to populate a two tabbed report (similar to Populate Excel Client Side using ActiveX and ASP).
- Writing an on-demand Web version that could use the Excel ActiveX control on the server to generate the report and then send it via the browser to the user.
- Write an application, e.g., WSH Batch file, VB/Java program that could be scheduled to create the files and place them on the server automatically.
- Tell the users that they would have two reports, one for each tab.
Obviously, the latter was not the option of choice, and given that they had as a company selected to use BO and only BO as their reporting toolset, a non BO solution would have been very hard to sell. Also, the browser security model in use and its enforcement removed the possibility of the browser driven ActiveX approach. Thus, we had to go back to the drawing board.
We knew that we could write VBA within the Excel shell provided by Business Objects, so we began to explore methods of using this. We also realized that we could output each tab separately, and then all we would need to do is to combine them back into a single Excel file for delivery to the end users, so we focused our efforts in this area. We came up with the pseudo code in Listing A for our problem.
We were quickly able to automate a link to Excel and began looking at how to copy each separate report file into a single Excel instance. We tried to open each file as a separate workbook using the Workbooks.Open command. However, while that was fine for the first workbook, it would not work for subsequent ones as it would always replace the workbook in the Excel file with the one opened last, disregarding all other workbooks.
We tried seeing if we could copy one of the tabs into memory using Copy and Paste functionality, then open the other workbook and paste the data into the correct place.
This looked like something along the lines of Listing B.
You can see this in action by running the Demo1.xls file available in the associated download.
To run the downloadable demo Excel files referenced in this article you will have to turn your Excel macro security setting to low.
However, this did not maintain the formatting we wanted and was very slow and resource intensive on the server, so we continued to develop this approach as our fall back while continuing to look for a more appropriate solution.
Eventually, someone suggested that we use a function of Excel called QueryTables, a quick browse through MSDN suggested that it might do the trick, although it required a database connection and some SQL to get the data out, which would have meant that we would have been doing the bulk of the work outside the Business Objects universe.
We then came across another MSDN link suggesting that a URL could be used instead of the database connection/SQL approach. As we could natively output each of the tabs in the Business Objects report as separate HTML files this seemed the ideal approach to take.
We were very quickly able to put together a demo that allowed us to load as many HTML files into our Excel instance as we wanted to, using code similar to Listing C.
You can see this in action by running the Demo2.xls file available in the associated download.
This allowed us to do the import to a new Excel tab for each file, so we could now load the HTML files into the Excel instance. However , the final formatting and layout of the report could not be easily achieved in this manner, so we decided to create a default report XLT—Excel Template—which would contain the correct formatting and layout required by our end users. You can view our update process in Listing D.
The next phase was to interact with Business Objects to generate each tab of the BO report as a separate HTML file. This again was quite straightforward using the VBA API of Business Objects to refresh the report and then export each tab as a separate HTML file as shown in Listing E.
In this way, we were able to loop through each tab in the BO report and export it to a HTML file with each file in the group retaining a similar filename, which was to be used in later processing.
Now that we had the data in a new tab in our Excel Workbook, next to the tabs that were provided by the Template, we simply had to Copy & Paste the data from these tabs into the correct tab and then delete them. This was accomplished using the built-in functionality to Copy and Paste a specified range and then run an autofit to assist in data presentation. We used code similar to Listing F.
You can see this in action by running the Demo3.xls file available in the associated download.
Next we decided to use an Excel template that we could maintain independently of the code; one that could be maintained by either ourselves or our customer. Demo4.xls shows a simple version of this using the template Excel file Demo4_template.xls.
However, any cosmetic changes to this template, such as adding rows, columns, changing the column order etc., would need support from IT, but simple changes such as changing the background color, fonts etc., could be done by the customer.
The final piece of the puzzle was the report specific VBA, which handled calculations, totals and layout of the report, this was run last before we saved our newly created Excel file.
The final code is shown Listing G.
Demo5.xls shows a data load into a more complex template—Demo5_template—and some custom changes for that specific report. Using this approach, we were able to very quickly roll out a huge raft of slightly different reports to our customers by using the standard skeleton based on the QueryTables and then customize the results as required using the instance_specific subroutine.
As you can see, although BO did not give us the flexibility that we needed at the time, its provision of an Excel VBA shell allowed us to interact with Excel in a way none of the developers had previously used to meet the needs of our customers. The base of this code can also be reused, as we did, to allow for a manageable way to meet multiple reporting requirements within your organization without creating a custom macro for each one.