Why create Excel spreadsheets on the server?
Excel has long been recognized as the de facto standard when it comes to presenting management reports. The unique combination of great calculation engine, excellent charting facilities and the possibility to perform “what if” analysis, make it the “must have” business intelligence tool.
So when we came to replace our aging management reporting infrastructure, we set one key requirement: the new system must be Web-based and provide a download in Excel option. For our business intelligence project we built the data warehouse using SQL Server 2005 populated from our PeopleSoft and Novient implementations using SQL Server 2005 Integration Services (SSIS). The OLAP cube was built on SQL Server 2005 Analysis Services (SSAS). SQL Server 2005 Reporting Services (SSRS) provides the Web-based access to management reports and the all important download in Excel option. So why do we need to create Excel on the server?
The problem with SQL Server 2005 Reporting Services is that it the Excel spreadsheets it generates are dumb. They contain no formula – just the raw data. So the recipient cannot perform a what if analysis by changing a few values and expecting the spreadsheet to recalculate.
We considered a number of ways to overcome this issue, but by far the most attractive is to create the Excel spreadsheet on the server, straight from the OLAP cube data. So we created a web-part for SharePoint Server 2007 so the user could enter their criteria and view the report on-line via Excel Services. Of course users can download the file for off-line viewing in Excel 2007 or even Excel 2003 file format.