Retrieve MDX query from Excel 2

[pabloj]

Hi, is there any way to retrieve the MDX statement generated by Excel while you are browsing a cube?
Thanks in advance for any help!

Stick to your guns

 

[MDXer]

Not that I know of, but it shouldn't be to hard to figure out what the query looks like based upon the datagrid layout in Excel.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[pabloj]

It is not so hard, but it's extremely slow .... it's all about productivity you know.

Stick to your guns

 

[pabloj]

For those interested in a more useful answer I have found out that the PivotTable object has an MDX property that shows the actual query.

Stick to your guns

 

[gaperry]

Hi pabloj,

Could you tell me exactly where to look in Excel to find the above Pivot table object and it's MDX properties ? I am some what new to using this in Excel. Thanks.

 

[pabloj]

In a macro you have to pass reference to the current cell (like on right click on the cell), then

....
Set pcell = ActiveCell.PivotCell
Set pt = pcell.PivotTable
qry = pcell.PivotTable.MDX
'this contains the MDX query
MsgBox qry

Stick to your guns

 

[MDXer]

pabloj,

Digging in code usually is where an answer lives. For your efforts enjoy a star.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck