Display the MDX query of an Excel 2007 PivotTable

Often end users use Excel to navigate into the cube. Then they may ask you to produce a report using the same data. It would be useful to get the MDX query used by Excel. Moreover, Excel 2007 offers a good MDX quality of the produced query against Analysis Services 2005 (much better than Excel 2003).

I don’t know why Excel doesn’t have such a function. I created a simple Excel macro that add an item to the PivotTable menu. A code that add a button in the Ribbon would be very welcome!

If you want to add these macro for all Excel sheets, you have to modify the PERSONAL.XLS file contained into “Documents and Settings[Username]Application DataMicrosoftExcelXLSTART”.

This is the macro Workbook_Open that is executed when you open a workbook.

Private Sub Workbook_Open()
   Dim ptcon As CommandBar
  
   ‘See the following for list of menus in excel
   ‘http://support.microsoft.com/support/kb/articles/Q213/5/52.ASP
   ‘Title: XL2000: List of ID Numbers for Built-In CommandBar Controls
   Set ptcon = Application.CommandBars(“PivotTable context menu”)

insertDisplayMDX:
   Dim cmdMdx As CommandBarControl
   For Each btn In ptcon.Controls
       If btn.Caption = “MDX Query” Then GoTo doneDisplayMDX
   Next btn
  
   ‘ Add an item to the PivotTable context menu.
   Set cmdMdx = ptcon.Controls.Add(Type:=msoControlButton, temporary:=True)
  
   ‘ Set the properties of the menu item.
   cmdMdx.Caption = “MDX Query”
   cmdMdx.OnAction = “DisplayMDX”
      
doneDisplayMDX:

End Sub

And this is the DisplayMDX subroutine, that you can insert in a separate module.

Sub DisplayMDX()
    Dim mdxQuery As String
    Dim pvt As PivotTable
    Dim ws As Worksheet
  
    Set pvt = ActiveCell.PivotTable
    mdxQuery = pvt.MDX
   
    ‘ Add a new worksheet.
    Set ws = Worksheets.Add
    ws.Range(“A1”) = mdxQuery
End Sub

When you right-click the PivotTable, the last item of the menu will be “MDX Query”. If you click it, a new worksheet is created with the query in A1 cell. This is the fastest way to copy and paste it into other programs. I would like to be able copying directly into clipboard, but I still haven’t found a reliable way to do it. If someone has some good idea, please contact me or comment to this post.

I attached a copy of a PERSONAL.XLS for the lazy of you!