Generate Pivot Tables via MDX

Question

The goal is to link Excel cells to SQL Analysis Server 2008.

Users dont want to use Excel Pivot Table's to connect to the cube as they need to do calculations on the data. This causes heaps of problems, primarily referenced cells turn into #Ref's when expanding and collapsing the pivot table hierarchy. I showed them how to turn off the +/-'s to make it read only, but it isn't ideal.

Secondly we do not like the Pivot Table generation tool, we all prefer the Cube Browser in SQL 2005, 2008 depreciated in sql2012 made with OWC that is also depriciated in Excel 2007 onwards.

So I am in a bit of a pickle. I've been researching heaps of tools to generate MDX. A couple of standouts were Mosha Pasumansky's awesome MDX Studio and http://silverlight.galantis.com's Ranet Olap Library Components

What I'm trying to achieve now is to set Excel cells with MDX's. Here's how:

Excel > Data tab > Connections > Connect to a Cube
Pivot Table, use existing connection
Add data to the Pivot Table
Double click a value in the Pivot Table to drill down, a new Sheet will be created with a 2d representation of the data.
Data tab > Connections > select the connection that was created by excel for the drill down.
Click Properties > Definition and the paste a MDX query in the Command Text field > OK

This populates the cells with the cube data but its only one row, I cant figure out how to make it multiple rows and hierarchical, see how behind the dialogs its only one line of data:

enter image description here

In a tool to generate the MDX query, it is multi-row:

enter image description here

I know its a long shot to try and get this working, I doubt its even possible but I am running out of idea's.

I tried the PowerPivot tool and it is terrible, its cube browser doesn't work the same way as the SQL Cube Browser, you can only add Columns! Its like a step backwards to the standard PivotTable.

So at this point I'm over trying to find a nice Pivot Table "Browser" in built in Excel - it doesn't seem like there is anything on the market? Thats why I'm resorting to 3rd party tools to get the MDX and insert into Excel (programmatically via VSTO).

One other thing I tried with no luck but thought I'd mention is Display the MDX query of an Excel 2007 PivotTable:

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

Unfortunaly the pvt.MDX property is ReadOnly!

If you got to here thanks for reading, any idea's most welcome:)

blacelle blacelle · Accepted Answer · 2012-06-22T22:08:23

You could build an Excel addin (see this other post).

This addin may be based on ADOMD.NET to connect a cube and query it with your own MDX queries.

Given a CellSet, you'll be able to extract a given cell and insert it wherever needed in your Excel sheet.

Generate Pivot Tables via MDX

2 Answers