Hi There,

We are working with SQL 2000 analysis services and Excel
XP using several servers for staging and development.
When we create a pivot table report linked to a cube, the
only way we can find to reconnect the pivot table to
another server is to break it (take first server offline)
and then wait for Excel to realise there is a problem and
offer us an alternative data source.

There must be a simplier way. Help.

Thanks.

Pete Treadaway
Interdirect Ltd
01908 422242
pete (AT) interdirect (DOT) co.uk

Hi Lutz,

Thanks for that - I tried it, but my pivot tables would
not open up again once they had been saved from Excel to
XML and back again. They are very complex and theres 3
per sheet so maybe this is why ?

However, I got this reply from another chap who has
written an add-in that does the job perfectly. I cannot
get through to your e-mail address for some reason, but
will send it on if you give me another address.

Cheers Again

Pete
-----------------------------------------------------

Pete,

I saw your posting in microsoft.public.sqlserver.olap
regarging reconnecting
XL pivot tables. I put together an add-in that enables
you, besides some
other features (mainly drillthrough, writeback in test/ to
come) , to change
the datasource (server, catalog, cube) easily.

Just register the add in, select a field in a pivot table
and choose OLAP -
The add in works with Xl 200 and above. Drillthorugh does
not allow multiple
selection in page fields in Excel XP.


Hope to help you



Thomas

-----------------------------------------------------

You should also be able to modify the server name in the .oqy file
(connection file) that is created by Excel. This file is typically
located in:

c:\Documents and Settings\<user name>\Application
Data\Microsoft\Queries

In this folder you should see all of the query connection files, open
the file in Notepad and change the server name.

Note, that you may not see the Application Data folder so you may need
to go to the Tools | Folder Options menu item in Windows Explorer and
then select the view tab. From there select "Show hidden files and
folders".

HTH,

Barry