THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

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 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!

Published Thursday, January 18, 2007 12:51 PM by Marco Russo (SQLBI)

Attachment(s): ExcelMacroDisplayMdx-2007.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Colin said:

one option for using the Clipboard is the following code.

( just put this in a separate module )

We normally use this in an Access Frontend.

Option Explicit

Declare Function SetClipboardData Lib "User32" (ByVal wFormat As Long, ByVal hMem As Long) As Long

Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long

Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long

Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long

Declare Function CloseClipboard Lib "User32" () As Long

Declare Function OpenClipboard Lib "User32" (ByVal hWnd As Long) As Long

Declare Function EmptyClipboard Lib "User32" () As Long

Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long

Public Const GHND = &H42

Public Const CF_TEXT = 1

Public Const MAXSIZE = 4096

Function ClipBoard_SetData(MyString As String)

   Dim hGlobalMemory As Long, lpGlobalMemory As Long

   Dim hClipMemory As Long, X As Long

   ' Allocate moveable global memory.

   '-------------------------------------------

   hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

   ' Lock the block to get a far pointer

   ' to this memory.

   lpGlobalMemory = GlobalLock(hGlobalMemory)

   ' Copy the string to this global memory.

   lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

   ' Unlock the memory.

   If GlobalUnlock(hGlobalMemory) <> 0 Then

       MsgBox "Could not unlock memory location. Copy aborted."

       GoTo AbortCopy

   End If

   ' Open the Clipboard to copy data to.

   If OpenClipboard(0&) = 0 Then

       MsgBox "Could not open the Clipboard. Copy aborted."

       Exit Function

   End If

    ' Clear the Clipboard.

   X = EmptyClipboard()

   ' Copy the data to the Clipboard.

   hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

AbortCopy:

   If CloseClipboard() = 0 Then

       MsgBox "Could not close Clipboard."

   End If

End Function

'Colin.

January 22, 2007 2:10 AM
 

lawryb said:

I find it easier (using VBA) to use the MS Forms library

Public Sub SetMyClipBoard(ByVal sT As String)

' Requires project reference to Microsoft Forms 2.0 Object Library

' (normally C:\Windows\System32\FM20.DLL - may have to browse to it if not in list)

Dim myDataObj As New DataObject

   myDataObj.SetText (sT)

   myDataObj.PutInClipboard

End Sub

January 23, 2007 8:20 PM
 

Marco Russo (SQLBI) said:

Both this option are not granted to work on any instance of Excel 2007.

The problem relies in trust for solution provided by Colin and even in possible missing ActiveX MS Forms component for solution provided by lawryb.

I think this is by design, but it is annoying to not be able to write a script that is granted to work on any PC with Excel 2007.

May be that Colin's solution can work easily once you trust the source of the macro... I already found one customer PC without Microsoft Forms Library.

Thank you for suggestions!

Marco

January 30, 2007 4:05 AM
 

Amy Pham said:

This is great! Thanks for sharing

November 11, 2008 1:28 PM
 

Dirk said:

Marco,

would it be possible to programmatically recreate the Excel pivot table from just the MDX code?

Where else could one use that code anyways?

Thanks for your answer in advance and regards,

Dirk

November 28, 2008 5:41 AM
 

Marco Russo (SQLBI) said:

Uhm, I would say no: the PivotTable contains much more settings than the data that you get from the server, and the MDX only get data.

Marco

November 28, 2008 6:30 PM
 

Marcelo said:

I have a problem with excel 2007, when i want navege an hierarchy for example employees with permisions of user with SSAS 2005 ,it excel i sent a message "The query did nor run or the datbase not could table opened"

do you know what is the proble with this?

June 2, 2009 12:21 PM
 

Marco Russo (SQLBI) said:

Marcelo, you should check your connection with SSAS. It's hard to help you in this way.

June 2, 2009 3:22 PM
 

Marcelo said:

Marco,this problem is only with excel 2007 with hierarchy employees parent-child permision,with reporting services i dont have this problem and other hierarchy neither

June 3, 2009 9:33 AM
 

Marcelo said:

i create a role with permision dimension data

Filter([Employee].[Employees].Members,[Employee].[Employees].CurrentMember.Properties("Login ID") =  UserName) for managent studio, visual studio browse ssasa & reporting run perfect, with excel not run("The query did nor run or the datbase not could table opened")

June 3, 2009 9:40 AM
 

Marcelo said:

you have a post in this blog http://davefackler.blogspot.com/2008/03/dimension-security-issues-with-excel.html

i have the same problem

June 3, 2009 10:13 AM
 

Marco Russo (SQLBI) said:

Ops! Thank you for the link to Dave Fackler blog, I forgot I had the same issue more than one year ago.

Reading in the comments, I've seen the issue seems have been resolved in AS 2008 - are you working with 2005 version?

June 3, 2009 10:23 AM
 

Marcelo said:

yes i'm working with 2005 version,too i worked with 2008 version i try with this with 2008 version. I have the solution thank blog ajajjaja with ssas 2005

June 3, 2009 10:58 AM
 

Marcelo said:

Marco

How are you?

I have 3 user(Domain/User) first user function perfect in dimension security, second usertest not function en dimention securty run de cube as administrator this user not have permision de admin in the server also in ssas only read definition.

June 4, 2009 12:25 PM
 

Bob Phillips said:

Marco,

I don't know if you are still watching this post, but here is trying ...

When I try to get a pivot MDX, just looking at the pivot object, I get an application error.I also get this with the VisibleItemsList and some other properties.

Any idea whay the reason might be?

The cube is just a standard AS cube, SQL Server 2008, Excel 2007 SP2.

Bob

January 1, 2010 8:24 AM
 

PCosta said:

Hi Marco,

Is there any way of disabling the subtotals in the MDX query generated by Excel 2007 over an Analysis Services Cube?

The problem is that for a result of 8000 records, the query returns over 200000.

I ran the query in Management Studio and its clear that the excess records refer to the calculation of subtotals.

This is crashing Excel with dried out resources.

Can you help me with this problem?

April 12, 2010 9:32 AM
 

bob ama said:

Doesn't seem to have any right click option as stated.

Doesn't error either

June 2, 2010 11:11 AM
 

bob ama said:

hmm works ok using the zip file though

June 2, 2010 11:13 AM
 

Woody said:

Thanks a bunch!  This worked great!

March 30, 2011 6:01 PM
 

tomerdg said:

Thanks a lot. This is a big help!

October 18, 2011 11:39 AM
 

tonyjoe said:

Brilliant! Thanks Marco.

February 14, 2012 5:02 PM
 

Ignace Van Buggenhout said:

Hi Marco!

Works like a charm even on Excel 2010! Thanks!

Best regards,

Ignace

November 18, 2013 5:22 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement