hen it comes to analysis and reporting, managers love Excel. Just give them the raw data and they have a field day. For enterprise-level reporting, however, you want everybody to have the same data and the same interpretation of that data. With some effort you can achieve this without having to say goodbye to Excel.
Excel is a very powerful tool for data manipulation, analysis, and reporting. The key is that Excel holds not only the raw data, but also the aggregations, calculations, charts, and so on. So you can both get the big picture and drill into the data. For example, if you put detailed sales data into an Excel sheet, you can calculate the revenue of your organization, a department, or single employees. You can also get information about what products sold best, the average invoice amount for a customer, and much more. It just depends how you look at the data. Due to the relative ease of use, any manager can go ahead and create his/her own report. There is no risk of corrupting the data source because Excel runs on the desktop and all the data is stored locally. Also, managers can use Excel at any time and in any place, even while on a plane headed to the corporate head office.
Realistically however, managers will not have access to every data source and be able to get any data they desire. In addition, an enterprise needs to agree on what the data is telling them, so having department managers create their own reports isn't very helpfuland comparing two departments is next to impossible! Still, being able to drill into the data is very useful, so you don't want to strip away all the power Excel offers. You just need to channel its power so that everybody can agree on what the reports are saying. So how do you do this?
Harnessing the Power of Excel
You can create reports in many ways, with or without Excel. If you look only at reporting solutions that use Excel, the list roughly gets narrowed down to three:
- SQL Reporting Services
- Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System (VSTO)
- Server-side Excel Automation
Of course you can imagine more solutions, but in essence they will not deviate much from the above list. In this article, I'll first look at SQL Reporting Services and VSTO to examine their plusses and minuses, and then I'll take a detailed look at why and how you would build a server-side solution.
SQL Reporting Services
SQL Reporting Services is a Microsoft extension of SQL Server that enhances its reporting capabilities. It primarily goes through the Reporting Services portal, which is basically a Web application with links to the reports. You can place these reports as files in the portal or build reports on-the-fly based on some pre-defined query. In the latter case, users can modify the query with parameters defined by the report developer, such as a filter by city.
This parameterization is actually very important because it lets you break down the data into meaningful chunks by which you can compare the performance of different products, branch offices, etc. However, to make use of SQL Reporting Services, you need to have a connection with the server, so it's not very helpful when you are not connected to the corporate network. You could get around this by providing the reports in different formats, such as HTML, PDF, or Excel. That way, users can get the data in an Excel sheet and analyze it offline. The downside is that SQL Reporting Services basically outputs tabular data, which in Excel output manifests itself as one or more sheets of data in a workbook, depending on the type of query. Unfortunately, this isn't very useful because this is just the raw data as it is queried from the data store. Even if that data has been pre-processed for reporting purposes, it still needs to be properly formatted to have any kind of meaning on a higher level.
Another problem is that Excel sheets are limited to 65,535 rows, which is potentially a limiting factor because if not all data fits in the sheet, the report may be inaccurate (which is often worse than not having a report). Filtering the data and downloading smaller chunks as you would do when you're always connected to the server does offer a workaround, but it is not very handy. This is especially true if you're in a field office in Africa with nothing more than a 56k modem connection. In that case, you'll prefer a single download with all the data.
Visual Studio Tools for Office
|SQL Reporting Services can provide reports in several formats including HTML, PDF and Excel.|
VSTO is, in many ways, exactly the opposite of SQL Reporting Services. In a nutshell, you can use VSTO to create managed extensions on Excel that resemble macros but are much safer. That means with VSTO you can use all of the power Excel has to offer, including charting, PivotTables, and so on. PivotTables are especially nice because they offer views into the data from multiple angles. What's more is that the underlying storage for a PivotTable, the so-called pivot cache, doesn't suffer from the 65,535 row limit of an Excel sheet. This really opens the door for full scale reports, nicely formatted, with charts and the whole nine yards. With VSTO you can create a template that only needs to be loaded with data. When users open the Excel file, they will receive a prompt to reload the data or use the file as is. When a user chooses to load the data, Excel pulls in the data from the data source. You could also have Excel aggregate the data from various sources, but that would require all users to have rights to do so. It is much easier to aggregate the data on the server using SQL Integration Services, Data Transformation Services, or similar tools. Then you can secure that one data source as needed.
|If you don't need PivotTables, you don't need to automate Excel. There are components available to manipulate Excel files.|
Using VSTO for enterprise reporting raises two major issues. First, a developer needs to build a VSTO template, whereas it's the business intelligence folks with domain-specific knowledge that really should create the reports. With some knowledge of SQL these users should be self-sufficient. Second, a VSTO solution may not scale very well. If several users need a report, having each refresh their own file isn't very efficient, and again this doesn't favor the guy in the field office in Africa. Also, if you need more reports, for instance about different regions, it takes longer to process them, in which case having all this data moving over the network is even worse. You should, of course, use data compression to move the data over the network. However, that's not something easily controlled from VSTO and could actually require a custom server application as well, making matters even more complicated.