Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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?

share|improve this question
    
Do you have to accomplish this using Excel services? –  RJ Cuthbertson Sep 28 '12 at 14:23
    
I'd prefer to, just because our analytics team has used excel for a few years now, so we have a decent amuont of users that depend on excel for reports. So it'd be ideal if we can "migrate" the workbooks into SharePoint –  ewitkows Sep 28 '12 at 14:26
add comment

4 Answers 4

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.

share|improve this answer
    
OK, thanks Jon, appreciate the guidance on this, neat approach, but I dont see how this can help me "query" my database, by updating an excel spreadsheet value.. –  ewitkows Oct 3 '12 at 17:58
add comment

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.

share|improve this answer
    
Thanks Bernd, but thats only half the battle.. we need the query to refresh and update the spreadhseet based on that parameter value, so I can parameterize the spreadsheet.. now i just need to query based on that data –  ewitkows Oct 3 '12 at 17:56
    
Sorry, now I follow. I was reading a bit too quick over your text. Is there any special reason that you want your users to type in the value? Because in terms of OOTB functionality I would use a slicer. –  Bernd Rickenberg Oct 3 '12 at 20:29
    
Otherwise it is as Jon points out with a small modification. Add the desired column to the Report Filter in the pivot diagram and name the cell with the filter value in the diagram. You must pass in the parameter via a SharePoint filter web part on web part page. –  Bernd Rickenberg Oct 3 '12 at 21:22
add comment

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)

share|improve this answer
add comment
up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.