Search code examples
sql-server-2005sharepointexcelsharepoint-2007

Creating and publishing excel file in MOSS 2007 using data from SQL Server

I need help in this matter:

We have a template of Excel file in which all calculations are already set. User can request a 'report'. Idea is to create a button on our site (SharePoint portal). After clicking on it a new Excel file is generated. This means to get actual data from database (SQL Server 2005 SP2), import them into template, let all calculations to generate proper data and then allow user to see this file.

For now it's enough to publish final Excel file in document library. I am quite new in WSS 3.0 and MOSS 2007 and I need some advice in what can be the best solution.

Looks like a quite complex task for me. Is there some direct way how to accomplish this? Or maybe I need one tool to get data from database and to import this data into Excel file (SSRS?) and other tool to publish it in document library (MOSS7 Excel services?). I heard something about PerformancePoint Server 2007, is this a way to follow?

Thanks forward for any advice!

Solution

  • OK, so after some time I was able to find this:

    You can use Excel Services included in MOSS 2007. With it and by using Excel Web Access Web Part you can actually view Excel files in a quite nice way. However if your excel sheets have some query tables in them (e.g. sql query to get some data from SQL database) they will not be published. This is feature of Excel that Excel Services do not support. For more info google "Differences between using a workbook in Excel and Excel Services"

    There is a way how to somehow solve this: Tool to run query tables/converting to PivotTables

    Problem we have with this tool is it just don't work, but it looks like others were more lucky. Also we don't want PivotTables. Our biggest problem was to have "normal" data refresh. Sometimes it worked immediately, sometimes not. Trying to change all possible properties didn't help (in DB connections inside excel sheets, SPP administration...). It's pity MS don't support such feature, it could help us a lot. So, if you want to run such solution, you'll have to forget about using native tools. You need to make you own webpart :)