Pivot Table based on SQL Server query

[Ben Rum]

I have a pivot table based on a SQL Server view. The data from the view
(basic select with no where clauses) which is stored in "Sheet1"

I created the Pivot table, selecting the "Create New Worksheet" putting the
Pivot Table on Sheet4.

If I select "Refresh Data" on the Pivot Table (Sheet4), it doesn't actually
refresh the data in Sheet1 (i.e. re-running the select from the database) it
only refresh's the Pivot Table from the data already contained in Sheet1,
effectively doing nothing.


The workaround is to go to Sheet1, select "Refresh Data" which querys the
database, then go to Sheet4 & select "Refresh Data" which refresh's the
Pivot Table contents.

I *used* to be able to refresh directly from Sheet4 which re-ran the db
query & updated the Pivot Table in one go..

How is this done?

 

[OJ]

Dunno but you could try this in Sheet4

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Sheet1.QueryTables(1).Refresh
End Sub

Hth,
Oli

 

[keepITcool]

hmm..

you could have created a pivottable on a sql query directly.
However you made a query table and made a pivot on the query table's
results.

the first option may have been be smarter. NO intermediate excel range
taking up space and refreshing would directly query the databse.

In your situation you have to refresh the QUERYtable first.
(in theory this SHOULD refresh the PivotCache too...)


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ben Rum wrote :

 

[Ben Rum]

hmm..

you could have created a pivottable on a sql query directly.
However you made a query table and made a pivot on the query table's
results.

Bingo! Thanks.