March 25th, 2009
First of all, let me start by appologizing for not blogging for xxx weeks.
Having done that I want to blog about the use of pivottables for reporting to management.
Recently I started working at a new company and discovered a giant heap of workbooks.
A lot of them are used for reporting to managent. For example there is one workbook which
contains 10 pivottables based on basically two recordsets. The size of this monster is
almost 100 MB because each pivottable has its own pivotcache.
The same workbook is refreshed every day and saved with new data on the network with
a new name. This means 2G of storage space every month. The refreshing is done by
changing a cell with the dateparameter in it and updating the pivotcaches through a macro
called by a button on the sheet.
What I dislike about this aproach is the button on the sheet, the size of the workbook(s)
and the dependancy on odbc links. So I started thinking on a new setup for this workbook.
The first abvious thing was changing to 2 main pivotcaches this would reduce the size to less
than 8 MB whitch means a 90% reduction of disk space.
Secondly I wanted to refresh the data by connecting a new pivotcache to the pivottables.
In the past I wrote some code to switch pivotcaches between pivottables and to produce a
pivotcache from a sql statement and a connectionstring. It looked liked I was almost there
but there were a couple of pitfalls. Apperently you must create a pivottable first from a
pivotcache to be able to switch the pivotcache. Also when the your pivottable uses calculated
fields in the value section switching the cache results in a blank pivottable.
After realizing these things the coding didn’t take along time. Altough everything works I would like a answer from microsoft or anybody else about the problem around using a pivotcache without pivottable and the calculated field issue. Below the code without error handling for the obvious reasons. Comments a very welcome.
Dim sSQL1 As String: Dim sSQL2 As String: Dim sDate As String
sDate = InputBox(”Enter the date (yyyymmdd)”)
sSQL1 = “SELECT *, (new-old) AS change FROM table1 WHERE date=’” & sDate & “‘”
sSQL2 = “SELECT * FROM table1 WHERE date = ‘” & sDate & “‘”
SwapPivotCaches sSQLServer, sSQL1, “PivotTable1″
SwapPivotCaches sSQLServer, sSQL2, “PivotTable2″
Sub SwapPivotCaches(sConnection As String, sSQL As String, sPivotTable As String)
Dim pcTemp As PivotCache: Dim ptTemp As PivotTable
Dim ws As Worksheet: Dim pt As PivotTable
Application.ScreenUpdating = False
Sheet0.Visible = xlSheetVisible
Set pcTemp = PivotCacheFromSQL(sSQL, sConnection)
Set ptTemp = pcTemp.CreatePivotTable(TableDestination:=Sheet0.Range(”B5″))
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
If pt.Name = sPivotTable Then pt.CacheIndex = ptTemp.CacheIndex
Set ptTemp = Nothing
Set pcTemp = Nothing
Sheet0.Visible = xlSheetVeryHidden
Application.ScreenUpdating = False
Function PivotCacheFromSQL(sSQL As String, sConnection As String) As PivotCache
Dim cConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Dim pcPivotCache As PivotCache
Dim iErrorCounter As Integer
iErrorCounter = 0
Set cConnection = New ADODB.Connection
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cConnection
.CommandText = sSQL
.CommandType = adCmdText
Set rsRecordset = New ADODB.Recordset
Set rsRecordset.ActiveConnection = cConnection
Set pcPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set pcPivotCache.Recordset = rsRecordset
Application.StatusBar = False
Set PivotCacheFromSQL = pcPivotCache
Set rsRecordset = Nothing
Set cmdCommand = Nothing
Set cConnection = Nothing