After running these scripts you can easily view the underlying MDX query for any pivot table that uses a SSAS cube for its datasource:
Use the following script to add a show MDX Query option when you right click a pivot table in Excel 2007:
|
Private Sub Workbook_Open()
Dim ptcon As CommandBar
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 put this in a seperate 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 |
Works great!
The code comes from http://sqljunkies.com/WebLog/sqlbi/archive/2007/01/18/26875.aspx