How can I display the MDX generated by Excel 2007 when using a pivot table?

Pretty much what the title says. Is it possible to show the MDX that is 开发者_JAVA百科sent to OLAP source when using a pivot table in Excel 2007?

You can download the OLAP Pivot Table Extensions from the codeplex site( http://olappivottableextend.codeplex.com/)

Install it.

You can then right click and see the OLAP query.

Regards, Ambarish

I was curious to see the MDX generated by a pivot table in Excel also. Apart from imputing MDX manually I couldn't see a way to see the MDX after a user has selected and filtered etc.. on the pivot table itself.

But I found a quick vba snippet here PivotTable.MDX

All I did was ALT + F11 to bring up the VBA window

Insert a userform like so

Click inside the textbox inside the userform and change these two settings in the properties window

• Multiline - True
• Wordwrap - True

Paste in the code below

Sub CheckMDX()
     
Dim pvtTable As PivotTable
 
Set pvtTable = ActiveSheet.PivotTables(1)
 
UserForm.TextBox.Value = pvtTable.MDX
UserForm.Show

End Sub

Make sure your Pivot Table is the active sheet (i.e have the worksheet open on the pivot table clicked in a cell).

Press the run button and a userform should pop up like so where you can copy out the MDX text.

This was on Excel 365. Hope this helps.

If you have profiler you could set up an Analysis Services trace and catch it on the way, or do you need to see it without executing it?

While the OLAP Pivot Table Extensions are still available for Excel 2013, you can no longer right-click on the table and view the MDX. This is yet another example of MIcrosoft going out of their way to make life more difficult for end-users and developers. The ability to view MDX for the current PivotTable selections should be built into Excel. It's not because Microsoft's developers either were too careless to give such a critical thing priority or because the powers that be saw a potential for an add-on product. Regardless, it's an example of why they are often hated. Over the years I can't even count all the hours of life I've lost outside the normal workday because the incompetent or malevolent behavior of Microsoft's manager, developers... But what other toolset can do what Excel and SSAS do? It's not an easy thing to replace so we have to deal with their crippled tools.

There is a free web based pivot table tool called WebPivotTable which can connect to SSAS cube directly and display each MDX statement for teh current PivotTable selections. It has all functions as Excel but with more intuitive user interface. In MDX windows, you can even test your own MDX statement and get results displayed in table and charts.

Here is Demo and Documents.