Creating an Excel Batch-Reporting Solution
With Integration Services and OfficeWriter

By Chris Webb
IT Consultant, SQL Server MVP

It is a truth universally acknowledged in the Business Intelligence industry that all data ends up in Excel spreadsheets sooner or later. No matter how many thousands of dollars you’ve invested in state-of-the art AJAX-enabled thin clients or ad-hoc query tools that can slice and dice your data down to the millisecond, your users will always want to know where the ‘export to Excel’ button is.

As a result, one of the most common requirements for a BI solution is to be able to generate and distribute large numbers of Excel-based reports on a regular basis and in this article I’ll describe one way of doing just that using SQL Server 2005 Integration Services and SoftArtisans OfficeWriter.

Before I do that, though, I’m going to answer two questions that those of you with some experience in the BI industry will probably have after reading the title of this article: firstly, why use a third-party tool to generate Excel spreadsheets when both Integration Services and Reporting Services have native functionality that allow you to do this? And secondly, why use Integration Services for this task when, if you’re using the Microsoft SQL Server BI suite, Reporting Services is the obvious choice of tool for the job?

SoftArtisans OfficeWriter is a complete solution for the server-based generation of Excel spreadsheets and Word documents that doesn’t need Office to be installed on the server.

It exists as a web application and a set of custom rendering extensions for Reporting Services, but you can also use the underlying components in your own code and that’s what I’ll be doing later on. Its big advantage over Integration Services’ Excel dataflow destination and Reporting Services’ Excel rendering is that it is template-based: you create your own Excel spreadsheet with ‘data markers’ defined in it, and when you want to generate a new spreadsheet OfficeWriter takes this template and replaces the markers with real data.

Integration Services and Reporting Services’ native functionality create an Excel spreadsheet from scratch and, as a result, are severely limited in terms of the features of Excel they support – you’re only able to create the most basic of spreadsheets and if it is possible to get more than that it’s a major development task do so. With OfficeWriter, anyone familiar with Excel can create a template and these templates can make use of any Excel feature such as formulas, VBA macros, pivot tables and multiple sheets. As a result, you can let your resident Excel gurus off the leash to design the flashy dashboards they love, but at the same time be able to generate these dashboards in a production-strength, server-based system. This allows you to avoid the manual cutting and pasting that has been the bugbear of Excel-based reporting in the past.

In addition, the Enterprise Edition of OfficeWriter has some cool functionality that allows you to modify the design of the spreadsheet at the time of generation: for example, a chart type could be changed if a user preferred to see a line graph rather than a bar graph, or if you were using colored cell backgrounds to highlight values over a certain threshold you could set these thresholds differently for different users.

As you may know, SQL Server Reporting Services has a feature – data driven subscriptions – which allows you to automatically run a batch of Reporting Services reports using parameter values sourced from a query; it works very well, but there are a few reasons why you might not want to use it. First and foremost is price: data driven subscriptions are an Enterprise Edition feature and if you’re using SQL Server 2005 Standard Edition you might not be able to justify the significant extra cost of upgrading to get this functionality: the list price of a Standard Edition per processor SQL Server licence is $5,999 compared to $24,999 for Enterprise Edition. The solution I’m about to describe can be implemented in Integration Services Standard Edition.

Furthermore if you need to do any transformation or integration on your data before you can build your reports you’re probably going to need to do some work in Integration Services anyway so you might as well implement the whole solution there. And of course if you’ve already got Integration Services skills but have never used Reporting Services, why bother to learn a new tool?

Using the OfficeWriter components inside Integration Services is straightforward, and I put together the following package to show just how simple it is.

Here’s a screenshot from BI Development Studio showing the control flow:

The first step is an Execute SQL task which runs the following query against the Adventure Works DW sample database that comes bundled with SQL Server 2005:

select top 100 CustomerKey from dimCustomer

This returns 100 customer ids from a dimension table in a star schema, and what we’re going to do is create one Excel report for each of these customers which list all of the rows in a fact table that relate to them, then save these spreadsheets to disk. To do this I’ve created a ForEach loop inside which I first of all dynamically generate the SQL query needed to get the rows from the fact table for each customer in a script task.

Here’s an example of the kind of query that it produces:

select SalesOrderNumber, SalesOrderLineNumber, SalesAmount from FactInternetSales where CustomerKey=11358

This just returns the Sales Order Number, the Sales Order Line Number and the Sales Amount for each transaction in the Internet Sales fact table – basically the values for each individual item that the customer bought from the Adventure Works online store.

The next and final step in the control flow is a data flow task which runs this query against the SQL Server database and uses the results to populate an Excel spreadsheet using OfficeWriter. I then save this to my local hard drive. If I wanted to then distribute these files somehow it would be very easy to add a File System Task, an FTP task or a Send Mail task to copy them to a network share, an FTP site or email them. Here’s a screenshot of the data flow:

The query is run in an OLE DB Source Adapter and the spreadsheet generation done in a Script component destination. Here’s the VB.Net code used inside that Script component:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports SoftArtisans.OfficeWriter.ExcelWriter
Imports System.Collections

Public Class ScriptMain
    Inherits UserComponent
    Dim xlt As ExcelTemplate
    Dim SalesOrderNumbers As ArrayList
    Dim SalesOrderLineNumbers As ArrayList
    Dim SalesAmounts As ArrayList
    Dim CustomerID As String

The first thing that I needed to do here was to add a reference to the relevant OfficeWriter component, SAXWNET.dll (this is a .Net dll but there’s also a COM equivalent). In order to reference a dll in an Integration Services script component it needs to be copied to the following system folder:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.nnnn

It also needs to be deployed to the .NET Global Assembly Cache (GAC), which you can do with the gacutil.exe utility.

I then had to create a template spreadsheet. OfficeWriter includes an Excel addin that makes this process very user-friendly, but I just typed the necessary data markers in manually. Here’s what it looked like:

The cells containing values starting with %%=$ are the data markers; on the top row there’s a marker which indicates where the Customer ID will be displayed, and on the fourth row three markers for each of the three columns in my query.

Once that was done I could get on with the coding and there were only three methods that needed to be implemented. First there was AcquireConnections, which is fired before the data starts flowing. Here I create a new ExcelTemplate object, point it to the template spreadsheet I created on my local hard drive (I hard-coded the path in this example for the sake of legibility but in real-life this would probably be stored in a variable so it could be passed into the package), create three new ArrayLists to be filled with the data from my query and also get the ID of the customer who I’m generating the report for from an Integration Services package variable.

I should probably own up at this point and admit that most of this code I pinched from the comprehensive set of examples available in the OfficeWriter documentation that can be found here:
http://support.softartisans.com/docs.aspx

Next there was the ProcessInputRow method, which is fired once for each row that comes through the data flow. All I do here is add the values from each column to the appropriate ArrayList.

Finally there was the PostExecute method, which is fired after the flow has finished. Now I’ve got all the data in my three ArrayLists. I turn each of them into arrays and then bind these arrays to the three data markers in my template spreadsheet which indicate where I wanted the Sales Order Numbers, the Sales Order Line Numbers and the Sales Amounts to appear using the SetColumnDataSource method. I also bind the single cell where I want the Customer ID to appear to my CustomerID variable using the SetCellDataSource method. Calling the Process method actually puts the data into the spreadsheet and the Save method saves it to disk with the Customer ID as the filename.

Here’s a sample of what one of the output spreadsheets looks like:

The entire package took around 11 seconds to create 100 Excel spreadsheets, running in debug mode on my laptop. I recreated the entire batch process in Reporting Services using a data driven subscription and it took exactly the same amount of time to execute. I didn’t take the time to pursue this comparison any further but my guess is that at worst Integration Services would always perform as well as Reporting Services at this task and with a bit of effort (for example through the introduction of parallelism, or replacing the script destination with a custom component) would outperform it.

This paper has only just scratched the surface of what is possible with OfficeWriter, but hopefully it has given you an idea of how powerful it is. As well as generating Excel spreadsheets, I could just as easily have generated Word documents or used some of the Enterprise Edition functionality I mentioned earlier.

Equally, anyone who has used Integration Services will know just what a great platform for manipulating and moving data it is -- with many more features than I’ve demonstrated here for things like text mining, aggregating numeric data, calculating values, pivoting, sorting and performing lookups against heterogeneous data sources. Put the two together and you’ve got an unbeatable platform for creating batch reporting solutions.

Chris Webb works as an independent consultant specializing in the Microsoft BI toolset. He is the co-author of the book “MDX Solutions with Microsoft SQL Server 2005 Analysis Services” and blogs on BI matters at http://cwebbbi.spaces.live.com.