Excel Services - Using parameter for query

Question

I have an excel spreadsheet that returns data based on a cell value. In essence, it's Select * from table where field = Sheet2!A1.

All works like expected, except when I publish it to SharePoint, where I'm told that "PivotTable reports that are set to refresh when the item in a report filter changes" are unsupported.

So I get that SP doesnt support this feature, as the error message cleverly explains. I've seen rumblings that ExcelREST services help work around this issue, but cant find anything specific. Can anyone point me in the right direction on this? Ultimately, I want the user to be able to supply a value, and have the spreadsheet perform it's queries based on that value.

Thx!

EDIT: Seeing some postings that a User Defined Function might get me over the hump, can anyone speak to that?

EDIT2:OK, lets try it this way. If I define my cell as a parameter, is there any way I can have the user enter that value via the "sharepoint gray bar", and then have them "refresh" the data in the worksheet, pulling relevant data based on the parameter value?

Answer 1

Really simple, but this should work. If you want to get user-entered parameters, do this:

1. First make the cell you want to treat as a parameter a named cell before publishing the workbook to SharePoint. It sounds like you have already done this.

2. Create a new web part page, add the Excel viewer web part to it, and configure it to display your workbook.

3. Add a SharePoint text filter web part to the page, and choose "Send values to..." choosing your excel web part. At this point I think you may have to manually enter the name of the named cell.

The nice thing about this approach is that you can "lock down" the excel web part so that users cannot interact with it (if desired). You can also specify a named range of cells for it to display from the workbook if you don't want the entire workbook to be visible.

Answer 2

Yes, what you describe in EDIT2 is possible in Excel Services (no need for setting a page up with a viewer web part).

You need two things to make it work:

1. Give the cell where you want to receive the parameter value a custom name, e.g. click on cell A1 and change the name from 'A1' to input_value in the upper left corner
2. Mark the input_value cell a parameter when publishing the workbook. Publish the workbook to a document library via File / Save & Send / Save to SharePoint / Save As / Publish Options / Parameters tab / select input_value and publish.

When you view the workbook in SharePoint it will display a grey bar with an input box for the input_value parameter.

Answer 3

Thats is what I have done in the past when facing a problem similar to your.

• Define a cell as a named cell in the excell workbook. This will allow you to send a value from a filter web part to the excell viewer web part and get that value in your cell inside the workbook.

• Create an User Defined Function that perform a query to the database. This UDF will use the named cell as a parameter. From the UDF return an xml value (select for xml).

• Use the XML that now you have in a field in your excell workbok in congiuntion with anoter UDF function to extract the value via XPATH. Notice that you can also extract columns of data by returning an array in your udf.

This should work (I was able to populate the excell file based on the data extracted from the database, all filtered for a value passed by a filter linked to the query string), but take notice that it may leave you in a world of pain while you configure permission for the udf to access the database and the data refresh to work as intended (my data was static, so I didn't work with refresh).

Also notice that you could be able to use the value from the named cell directly to update the workbook, but I don't know if this is allowed (never tried that, my problem won't allow for that).

(notice: i'm in an hurry now, will try to expand with further dettail soon. In the meantime, fell free to leave any question in a comment)

Answer 4

Took me a while to figure this out, but in the end, it was really simple for me to perform this. My report ended up using various cube functions that relied on a cell value. So in my proof of concept excel report, I was trying to use a cell value for a where clause, which was proving difficult. In my real world scenario, the value was just a parameter for excel function's looking to retrieve data. As such, all I had to do was name the field as a parameter, and Excel Services never minded performing any database lookups because of how the parameter was being used.

In other words, using a cell for a "Custom SQL" query was giving fits, but using a cell value for a parameter in data lookup functions caused this to work just fine. Thanks to everyone for the suggestions.