Pivot Tables in Excel 2008 with MSSQL Server Connection

[jens]

Hi,

we are using Excel 2008 and have an ODBC connection running. We have
created several Excel Pivot Charts using stored SQL queries on the
MSSQL server. This used to work fine. Now, we have changed the
structure of the queries a little and on Excel 2007 the system
recognizes the changes and keeps the relations correctly. In Excel
2008 the relations mess up totally. E.g. a product becomes after the
update a customer, and so on...

Do you have any idea to fix this problem?

Best Regards,

Jens

 

[Mike Middleton]

Jens -

I don't know anything about MSSQL Server Connections or ODBC, so the
following comments may not be helpful, but ...

(1) Regarding "We have created several Excel Pivot Charts...," there has
never been a version of Mac Excel that creates pivot charts.

(2) Regarding "we have changed the structure of the queries a little and on
Excel 2007 the system recognizes the changes and keeps the relations
correctly. In Excel 2008 the relations mess up totally," it's possible that
Mac Excel pivot tables lag behind Windows Excel, so you have to be careful
not to use any "advanced" features.

(3) Regarding "Do you have any idea to fix this problem?," I recommend the
general approach of developing on the oldest system (or the system with the
fewest features) that you wish to support and then testing on more recent
systems.

- Mike

http://www.MikeMiddleton.com

 

[jens]

Jens  -

I don't know anything about MSSQL Server Connections or ODBC, so the
following comments may not be helpful, but ...

(1) Regarding "We have created several Excel Pivot Charts...," there has
never been a version of Mac Excel that creates pivot charts.

(2) Regarding "we have changed the structure of the queries a little and on
Excel 2007 the system recognizes the changes and keeps the relations
correctly. In Excel 2008 the relations mess up totally," it's possible that
Mac Excel pivot tables lag behind Windows Excel, so you have to be careful
not to use any "advanced" features.

(3) Regarding "Do you have any idea to fix this problem?," I recommend the
general approach of developing on the oldest system (or the system with the
fewest features) that you wish to support and then testing on more recent
systems.

-  Mike

http://www.MikeMiddleton.com

Hi Mike,

sorry, of course I ment Pivot Tables and not charts
Hmm... Unfortunately the answer doesn't help me... But thanks a lot
anyways...

Jens

 

[google]

we are using Excel 2008 and have an ODBC connection running. ....

Now, we have changed the
structure of the queries a little and on Excel 2007 the system
recognizes the changes and keeps the relations correctly. In Excel
2008 the relations mess up totally. E.g. a product becomes after the
update a customer, and so on...

MS Query understands a limited subset of the SQL syntax. If your SQL
query uses the JOIN syntax, it will not be able to draw the
relationships between tables when you use MS Query in "Query View".

However, your SQL query that was created using Excel 2007 / MS Query
on Windows is still valid for SQL Server. Therefore, you should be
able to execute the query associated with the pivot table (stored in
the spreadsheet file) by control-clicking on the pivot table and
selecting "Refresh Data" from the contact menu.

This will send the query directly to SQL Server, which will execute
the query and return the results. You shouldn't need to invoke MS
Query at all. And if you do need to edit the query using MS Query,
you should be able to edit it using the "SQL View" (and avoid the
"Query View").

Jonathan Monroe
Actual Technologies - ODBC for Mac OS X
http://www.actualtechnologies.com