Query Tables work-around for Excel Services
Both in the internal distribution lists and on my blog, the two most insistent question we seem to be getting these days seem to be:
1. My workbook has a Query Table (relational data source in the form of a table) in it and it doesn't load on the server. What gives?
2. What? You REALLY don't support Query Tables?
So the answer to (1) is that that no, we don't support Query Tables with this version. The answer to (2) is that no, we really do not support them.
With that said, let me present Boaz-I'd-rather-be-photographing-Chen who has been with the team since the first day of Excel Services. He wrote an Add-in that takes a Query Table and turns it into a PivotTable that looks and behaves almost the same.
In his words:
This is a little add-in to convert a query table to a pivot table I wrote after getting repeated feedback from customers about not being able to load their workbooks which contain query tables (aka External Ranges) on the server. Mostly we reply that they need to replace their query table with a pivot table, and then there is always the question of how to do it.
The add-in manages to give pretty nice results, and the auto created pivot table looks very similar in its layout to the original query table.
Notes
· Basically the same operations (Filter, Sort, Totals) are supported on the pivot table.
· When trying to sort the auto created pivot, only the left most column can be sorted. The reason is that the fields are kept in a hierarchy inside the pivot, and there is no option (As far as I know) to break this hierarchy when wanting to sort on a non top level field.
· The add-in optionally removes the original query table and its workbook connection at the end of the conversion (You will get a dialog to confirm this).
Installation
1. Save the attached xlam anywhere on your drive.
2. In Excel, Office button | Excel Options | Add-ins
3. Down at the bottom: Hit the Go… button
4. Browse to the saved add-in.
5. OK everything and reboot Excel. There will be an Add-in menu added to the ribbon with a Custom Toolbars chunk. The conversion button should appear there.