none
How to pass parameters into Excel Services worksheets?

    Question

  • Hi all,

    I have created an Excel Worksheet with an external data connection to a SQL Server table. The worksheets displays the info with a pivot table. We have the worksheet up and running in SharePoint though Excel Services.

    Now, we want to be able to pass on a few parameters to the worksheet though the query string. What we need to do is to pass some parameters to the worksheet and use these parameters to build a WHERE clause in the SQL query. It could also be ok for us to use any build-in filter web parts that can be used to pass filter values.

    What's the steps required to do this?  I guess we need to 1. Find a way to pass the query strings as parameters into the excel web access web part and 2. configure the worksheet to accept those parameters and use it in the SQL server query.

    We have been using the "From SQL Table" option when connecting the data source to Excel.

    Thanks a lot!

    /Chris

    Monday, November 01, 2010 3:43 PM

Answers

All replies

  • I'm not sure about number 2 since that is very excel specific but you can using Excel REST Services to easily pass in value to an excel spreadsheet and have it perform the calculation there.
    Blog: www.sharepointanalysthq.com Twitter: @michalpisarek
    Monday, November 01, 2010 10:17 PM
  • Hi Chris,

     

    Thanks for your post.

     

    It’s not a good idea to pass the parameters to build a where clause in SQL query. You are changing the data source of the Excel Services.

    You can easily filter the data in Excel Service using the Filter Web Part.

     

    More information about this:

    http://blogs.msdn.com/excel/archive/2006/09/25/770961.aspx

    http://office.microsoft.com/en-us/sharepoint-server-help/connect-filter-web-parts-to-excel-web-access-HA010105470.aspx


    SharePoint 2010
    Wednesday, November 03, 2010 2:51 AM
  • Hi Wayne and thanks for your reply,

    In the example that you provided, a SSAS Filter is used. But our Excel file goes against an ordinary SQL table, not a SSAS cube. Is there any other kind of filter web part that can be used?

    Our problem is that our SQL tables can grow to pretty large sizes, so we need to be able to filter the query on the data source. It seems that Excel Services does not support parameter queries, which is a really bad limitation.

    Any thoughts on how to achieve this?

    Thanks,
    Christoffer

     

    Wednesday, November 10, 2010 9:25 AM
  • Hi Christoffer,

    Glad to receive your reply.

    I think the SSRS (SQL Server Reporting Service) is more suitable for your. You can pass parameters to it.

    Here is a similar issue:

    generating excel from sql view

    Share your views.


    SharePoint 2010
    Friday, November 12, 2010 3:20 AM