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 Data\Microsoft\Excel\XLSTART".
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!