Have you ever tried to reverse engineer an Excel pivot table? It’s not as easy as you would think! Whether you just want to find out the datasource details, or identify the query that was used, there is just no simple way of getting Excel to tell you.

The macro below will loop through every sheet in a workbook, and will document the datasources, SQL or MDX queries as well as the page, row, column and data fields.

To use it, add it into your macros, then select a starting cell where you want te report to be placed and run the macro. It’s pretty raw, and may need some tweaks to suit your requirements but it should give you a good starting point. I use it on Excel 2003 MDX pivots from SQL Server Analysis Services 2005, but I presume it will work on other versions of Excel as well.

Public Sub PivotDetails()
   Dim ws As Worksheet
   Dim qt As QueryTable
   Dim pt As PivotTable
   Dim pc As PivotCache
   Dim pf As PivotField 

   For Each ws In ActiveWorkbook.Sheets 

      For Each qt In ws.QueryTables
        ActiveCell.Value = "Sheet"
        ActiveCell.Offset(0, 1).Value = ws.Name 

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "Data Source"
        ActiveCell.Offset(0, 1).Value = qt.Connection 

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "Query"
        ActiveCell.Offset(0, 1).Value = qt.CommandText
      Next qt 

      ActiveCell.Offset(2, 0).Select 

      For Each pt In ws.PivotTables 

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "Pivot Table"
        ActiveCell.Offset(0, 1).Value = pt.Name 

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "Connection"
        ActiveCell.Offset(0, 1).Value = pt.PivotCache.Connection 

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "SQL"
        ActiveCell.Offset(0, 1).Value = pt.PivotCache.CommandText 

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = "MDX"
        ActiveCell.Offset(0, 1).Value = pt.MDX 

        For Each pf In pt.PageFields
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = "Page"
            ActiveCell.Offset(0, 1).Value = pf.Name
            ActiveCell.Offset(0, 2).Value = pf.CurrentPageName
        Next pf 

        For Each pf In pt.ColumnFields
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = "Column"
            ActiveCell.Offset(0, 1).Value = pf.Name
        Next pf 

        For Each pf In pt.RowFields
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = "Row"
            ActiveCell.Offset(0, 1).Value = pf.Name
        Next pf 

        For Each pf In pt.DataFields
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = "Data"
        Next pf 

      Next pt
   Next ws
End Sub

Tags: Datasource, Excel, MDX, Pivot, SQL

This entry was posted on Wednesday, January 23rd, 2008 at 9:59 am and is filed under Analysis Services, SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.