Excel 2010 SQL 2000 OLAP reporting – won't refresh
Environmental Set Up
Ptsfull.exe installs the PivotTable Service files and Microsoft Data Access Components (MDAC) that is required for PTS to work.
In this article http://support.microsoft.com/kb/290211/ you can find information about different PTS versions for SQL 2000. To be able to use DSP Portal Edition on a 64 bit platform it is mandatory to configure the Web Part Server to run in COM+ 64 bit platform COM+ configuration
For more information about this please see: http://support.dspanel.com/help4/Server_Configuration/Installation.htm
If ProClarity is installed, you should already have the pivot table service files installed.
Set Up the Connection
In Excel, Data > From Other Sources > From Analysis Services and set up as for SQL 2005 or 2008. See attached document.
But this will save the connection to an external file on the hard drive, under C:…..
So the Excel reports will only work if the user has access to that connection instructions file.
Control the Connection
If the connection works, but after the file is saved and reopened it cannot be refreshed, then adjusts the connection:
Select a cell in the pivot table. Go to Data > Connections and edit the connection information by checking Refresh data when opening the file. This makes it possible to change the filters and to refresh.
You could probably move the connection file to a central location that is mapped the same for everyone, so that multiple users could use the same Excel pivot table.