DCL Problem
I'm having a problem getting my external data provider to work in Excel Services. I just went through this process (following a video tutorial)
* Created a DCL in Sharepoint
* Set the DCL up as trusted for Excel services
* Made sure my doc library was trusted for Excel Services, and that the "Allow External Data" for it was set to "Trusted data connection libraries only"
* Created an ODC
* Exported the ODC to the DCL
* Created a data pull in my workbook using the ODC from the DCL
* Published the workbook to the doc library
But when I finished all that, I received the following error on trying to load the workbook:
The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services:External data ranges (also called query tables)
Contact the workbook author.Any help on what I'm doing wrong would be appreciated.
I thought maybe the problem here was that I was trying to publish out a spreadsheet that had its data drawn from a data table instead of a pivot table, so I decided to try again, this time using a data-driven pivot table. What a nightmare.
I went to the Insert tab on the ribbon and selected a pivot table. Then I selected my existing data connection, and received the following error message:
The type of connection selected cannot be used to create a PivotTable.
That bugged me a bit, since the "type of connection" was listed as Office Data Connection (which, I believe, is what you have to have to use with Excel services). But I pressed onward, creating a completely new connection, copying-and-pasting my SQL statement and connection details from the previous connection, and it let me build a new connection and create a pivot table based on that.
Now my problem is that I can't go back into that new connection and change some of the items in my where clause to parameters... the parameters button on the form is greyed out, and if I replace the values with question marks, I get an error.
This process can't conceivably be this complicated. I've been working in IT for a decade, and if I can't figure this out, how in the heck are my users ever going to be able to figure it out?
Any hints as to what I'm doing wrong would be greatly appreciated.
Excel services is at V1 stage so there is a number of unsupported features that will catch people out. This posting gives a full list of these and explains your initial problem using Query Tables.
The Plan external data connections article on technet provides a lot of help in setting this up. I do agree however that this can be complicated although once the SharePoint environment is setup and the supported dataconnections defined the users only need to be educated in what is/isn't supported.
HTH.
Andrew
