Partager via

Query Tables work-around for Excel Services

  • Article

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.

ConvertToPivot.xlam

Comments

  • Anonymous
    November 06, 2006
    The comment has been removed

  • Anonymous
    November 07, 2006
    OK, I figured out the problem. The addin uses OLEDB connections (as seen above in previous post). I created my data connection (.odc) by using the data connection wizard in excel and when i opened it up in notepad it's odc:Type="ODBC". Seeing this, I updated the code to be ODBC connections:    Set pivotConnection = ActiveWorkbook.Connections.Add _        (tableConnection.Name, tableConnection.Description, tableConnection.ODBCConnection.Connection, _        tableConnection.ODBCConnection.CommandText, tableConnection.ODBCConnection.CommandType) This seems to work and i can open it thru excel services! You could probably modify your .odc file's connection string to be an OLEDB type and keep add in as is. thanks Shahar

  • Anonymous
    November 10, 2006
    Hi, I'm new to this and somewhat frustrated.  How can I create an Xxcel file that will load its data from SQL for use on a page with Excel services?  Is there a step by step set of instruction anywhere that makes no assumptions about the end users level of knowledge?  I'm really in a bind here the best I can get is a query tables rejection.

  • Anonymous
    November 10, 2006
    Alan, SQL tables are not supported in Excel Services as they are in Excel. One of the workarounds is to use the add-in in this post to do it. The instructions are in the post. If you have any questions specific to that, feel free to ask them.

  • Anonymous
    May 07, 2007
    Is there any source code for this workaround available?

  • Anonymous
    May 07, 2007
    Yes, if you download it, you will be able to access the VBA code in it.

  • Anonymous
    October 06, 2007
    SQL tables are not supported in Excel Services as they are in Excel.

  • Anonymous
    March 27, 2008
    Update: I have added some links to tools that should help you overcome this limitation. We were told

  • Anonymous
    May 26, 2008
    En mi experiencia, he visto que este es uno de los temas poco desarrollados por el momento en los portales

  • Anonymous
    September 24, 2008
    is there an updated version of the add-in?  I followed the instructions and when i open up excel i receive the following:  This workbook was created in an earlier beta version of Excel 2007.  Excel will convert the workbook to the most recent version of the Excel 2007 file format when you save it.  Before opening this workbook, verifty that it is from a trusted source. Will i get this message everytime i open up Excel?