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.

Last edited Jan 3, 2007 at 4:43 PM by JohnTunnicliffe, version 2

Comments

VishalMohan Jan 7, 2010 at 8:02 AM 
Hi,
Can i know how to use the web service, please respond its very urgent.

Thanks in advance.

Vishal Mohan

rbll Jun 18, 2007 at 9:27 PM 
Hi
Can you demonstrate, how you did it, please?

JohnTunnicliffe Jun 14, 2007 at 8:38 AM 
As you understand, it is not a good idea to install Excel on a production server. So I achieved Excel 2007 to 2003 conversion using a web service hosted on an ordinary PC which does have Excel installed. The main server app uses ExcelPackage to create the Excel2007 file and then calls the web service to simply open the Excel 2007 from a shared folder and then saves it in 2003 format. However, the PC running the web service does need rebooting on a regular basis (approx every 3 weeks). But this is better than having your main server crashing!

jancow Mar 12, 2007 at 7:36 PM 
"Of course users can download the file for off-line viewing in Excel 2007 or even Excel 2003 file format." - How did you go about providing an Excel 2003 format file to your end users? I've been looking and couldn't find any conversion utilities. Are you using excel automation to save as this file type?
Thanks!