A better way of running SPs from Excel

Hi All,

I didn't find an Excel forum here so I thought this might be the 'next best place'.

We have developed a 'packaged EDW' and the first version is all on the MSFT stack....though we do not want to use AS as we do want the product to be able to be back ended by other databases.....(heresy here, I know).

We have built what we are now called the 'Data Access Layer'.

The DAL is a suite of stored procedures.......the idea is simple.......ask a question via a SP with a set of parameters and it gives you a data stream in return. We are using Report Services first and it calls the DAL for its data.

But we have been thinking.....

The entire world has Excel on their desktops........and lots of those people love to have their reports in Excel (and not Report Services)....

We would like to find a really good way of using Exel on top of the Data Access Layer....so far, in looking into excel it seems we can really only run MS Query and return the data to a sheet and then go from there....this seems quite 'clunky'.

We are well aware of the idea of putting the data into AS and going from there...but we want to explore getting the data from SPs so that if we build some excel based reports and put the data into another database it will still work....

We have seen Oracle provides java add ins to Excel to get data directly from Oracle databases...

We are wondering if someone, somewhere, has developed 'the best' way of getting data from SPs into an excel workbook.

What we have are SPs to select options....such as time, product hierarchy, company reporting structure etc....and then the user selects from these options and the selections are passed to an SP to get the results for the selection..all the usual things needed for RS reports.....

We are wondering if someone has done something that is similar where the data is then delivered to Excel reports...

Our first big questions would be:

How to effectively select parameters for the report where the parameters are in the database?

1. How to effectively refresh the Excel reports on request or on a schedule?
2. How to most effectively imbed the calls to the stored procedures in Excel?

Sorry if I am in the wrong place....

Best Regards

OK, agreed that calling stored procedures from Excel is really clumsy. You will have to use VBA to do all the stuff behind, there is no easy way to implement that in Excel. Another thing would be to use an Analysis Source rather than a relational. This is far away from your original questions but can ive you the data in a much better and effective way than procedures can eventually do. But now to your questions:

1. You want tsomething like a dropdown box for selectable values ? You will have to populate them using VBA. A usable approach would be to fill some invisible excel sheets and point the filter boxes to the range of the extracted values.
2. You can use timers in VBA to refresh the code. Off the top of my head I cannot remember wehre the option in Excel was to refresh datasources on a regular basis. (By the way, using Reporting Services can do this all behind the scenes using a delivered report to a fileshare)
3. Well, all I know is that the most flexible way to do this is calling the procedures from VBA.

Hope my thoughts can help you, maybe you post your question also in one of the public newsgroups regarding Excel as there is none here in the MSDN ones.

Jens K. Suessmeyer

http://www.sqlserver2005.de