Archive for the ‘Pivot table’ Category

Random results after PivotTable refresh.

March 8th, 2012 admin No comments

A funny thing happend to me lately…

A user (manager) came to me with a PivotTable his predecessor created that produced random results after refreshing.
The PivotTable was based on a PivotCache connected to a query on a SQL Server.
When the query was run in Management Studio it was complaining about a conversion and returned a error.
This query didn’t produce a error in excel but just returned the records upuntil the error.
As the query wasn’t sorted the number of records returned to excel was random as so the results in the PivotTable.

If after the first refresh the data was almost complete nobody would have noticed the corrupt results

Hope I this helps anybody having the same issue….

p.s. Self Service BI is not for everybody …

Categories: Excel, Pivot table, SQL Tags: , ,

Great article about using MDX in OLAP Pivottables (PowerPivot)

July 12th, 2011 admin No comments


I found this great article Quartile, Percentile and Median in PowerPivot. It’s about using MDX in OLAP Pivottables and the ability to perform COM interop calls through the ExcelMDX assembly.
It’s not to long but I’m sure I will spend a lot of time putting it to use…

thnx Javier Guillen

Using MDX for ranking in a PowerPivot solution

June 29th, 2011 admin No comments


I found this great article about using a combination of MDX and DAX in a pivottable based on a PowerPivot model. It explains a real world Ranking problem using this approach. It is definitely worth reading…

PowerPivot is ‘Rank’


Slicers and Filters in Excel 2010 Pivottables

May 18th, 2011 admin No comments

May 28th, 2010

I’m not sure if what I experienced is a bug or a feature….
I created a pivottable in Excel 2010 with slicers and made a selection via the slicers.
So far so good but when I wanted to see the records for a specific datapoint and double click on this point it will give me the complete recordset not taking into account the slicer settings.
But when I put in pivottablefilters for the same fields as the slicers it all works like good old pivottables doubleclicking.

Does anybody know if this is a feature or a bug….?

Update Pivottable through swapping the pivotcache

May 18th, 2011 admin No comments

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.

Sub UpdateWorkbook()

   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″

End Sub
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
      Next pt
   Next ws

   Set ptTemp = Nothing
   Set pcTemp = Nothing

   Sheet0.Visible = xlSheetVeryHidden
   Application.ScreenUpdating = False

End Sub
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
   cConnection.Open sConnection

   Set cmdCommand = New ADODB.Command
   Set cmdCommand.ActiveConnection = cConnection

   With cmdCommand
      .CommandText = sSQL
      .CommandType = adCmdText
   End With

   Set rsRecordset = New ADODB.Recordset
   Set rsRecordset.ActiveConnection = cConnection

   rsRecordset.Open cmdCommand

   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

End Function
Categories: Excel, Pivot table Tags: ,

Pivot Table based on CSV file.

May 17th, 2011 admin No comments

July 31st, 2008

It’s been a while since my last post.
Due to my new job and rebuilding my house free time was scarce.
In my line of business we work a lot with data from numerous suppliers.
Some of the data is provided in csv files which can be imported in excel to base a pivot table on.
But what if the number of records / lines exceeds 65536 in excel 2003 or 1048576 in excel 2007.
You can cut the csv file in parts and base the pivot table on multiple consolidation ranges.
This was too much of a hassle for me and I started thinking about a more structured way to
create a pivot table on a csv file, with this result…
What is does is in fact simple but beautiful.
Select CSV File, create a recordset from it and attach it to the pivot cache to base a new
pivot table on this cache.

Option Explicit

'Set Reference to Microsoft ActiveX Data Objects 2.7 Library

Const sConnStrP1 = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="
Const sConnStrP2 = ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
Const sFilter = "CSV File, *.csv"

Sub CreatePivotTableFromCSV()

  Dim sFileName As String
  Dim sFilePath As String

  sFileName = Application.GetOpenFilename(sFilter, 1, "Select File", , False)
  sFilePath = Left(sFileName, InStrRev(sFileName, "\"))
  sFileName = Replace(sFileName, sFilePath, "")

  TestCSV sFilePath, sFileName

End Sub
Sub TestCSV(ByVal sFilePath As String, ByVal sFileName As String)

  Dim cConnection As ADODB.Connection
  Dim rsRecordset As ADODB.Recordset
  Dim pcPivotCache As PivotCache
  Dim ptPivotTable As PivotTable
  Dim SQL As String

  Set cConnection = New ADODB.Connection
  cConnection.Open sConnStrP1 & sFilePath & sConnStrP2

  SQL = "SELECT * FROM " & sFileName

  Set rsRecordset = New ADODB.Recordset
  Set rsRecordset = cConnection.Execute(SQL)

  'For Excel 2003 Use
  'Set pcPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
  'For Excel 2007 Use

  Set pcPivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
  Set pcPivotCache.Recordset = rsRecordset
  Set ptPivotTable = pcPivotCache.CreatePivotTable(TableDestination:=Range("B5"))


  Set rsRecordset = Nothing
  Set cConnection = Nothing

End Sub


Comments are welkom

Categories: CSV, Excel, Pivot table Tags: , ,