Hur ändrar man datakälla i en excel pivottabell?

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

Joozh,

Excel uses OLEDB for OLAP and you can choose between a non specific version and version 8.0, I have always used the one called

Microsoft OLE DB provider for OLAP services 8.0.

Please note that this is Excel XP. XP has a far superior version of pivot table services to Excel 2000 and on our machines here, people who need to use the OLAP reports have Office 2000 installed for their normal work and Excel XP installed on top. (N.B. it has to be on top as fixing or reinstalling Office 2000 messes up Excel XP)

The connection is controlled by the PivotCache object with properties such as

CommandText
Connection

and assorted methods

e.g. Refresh

The Connection is the connection string such as
OLEDB;Provider=MSOLAP.2;Persist Security Info=True;Data Source=SERVERNAME;Initial Catalog=OLAPDATABASE;Client Cache Size=25;Auto Synch Period=10000

where you'd replace SERVERNAME & DATABASENAME (actually I meant OLAPDATABASE!) with the relevant info

I think that CommandText may be the cubename although I can't quite remember

You can see this information in Excel, using VBA by doing something like

Sub PTinfo()
MsgBox ActiveSheet.PivotTables(1).PivotCache.CommandText

MsgBox ActiveSheet.PivotTables(1).PivotCache.Connection
End Sub

Which will pop up message boxes with the information about the cubes. I have used this reasonably effectively for management of our reports and also by using the replace function I can change the servers that the reports point at thereby relatively easily rolling out the changed reports from Development to Live.

Hope this helps,

Regards,

Robert.

Det här verkar vara ett seriöst problem. Hittar en massa trådar om folk som frågar efter en lösning men inte några lösningar.

Vem vill vara först ?! HJÄLP :)

Mycket intressant problem. Verkar dock vara fixat i office 2003. Har du skapat dokumentet i en tidigare version så har du en guide för att fixa problemet här.