Category Archives: 12622

Cycling Through The Fog

Dynamic Analysis

Excel 2007 provides the capability to have a pivot table connected to an OLAP cube. Furthermore, detailed analysis can be built by using various CUBE functions. One of the great things about Excel 2007 pivots and CUBE functions is that you can use a value from the pivot as a member value within a CUBE function. For instance, a CUBEVALUE might be getting the net gas sales for a particular company and period with


                       “[Transaction Company].[Company Drilldown].&[17]”,

                       “[Activity Date].[Date Drilldown].[Month].&[1]&[1]&[2009]”),


If you also have a pivot that is filtering the company drilldown, the current selection can be substituted in that formula and achieve the same results,


                       ‘Pivot Sheet’!B10,

                       “[Activity Date].[Date Drilldown].[Month].&[1]&[1]&[2009]”),


Obviously, this gives us a dynamic capability, our analysis can reflect the currently selected pivot value(s).

Next/Previous Function

I have a spreadsheet that has built a comprehensive analysis using this technique.  This all works well, but I was looking for another useful function. I needed to be able to provide Next/Previous buttons that would enable cycling the analysis through each of the top-level members of the Company Drilldown without the user having to go back to the pivot and reselect the Next/Previous item. The Company Drilldown was a PageField in my pivot.

As far as I am aware, there is no built-in way to get the next or previous item in a pivot filter, this would have been the most obvious and simplest way, but nt to be.

My next thought was that if I could somehow obtain the MDX for the each item, I could create a CUBEMEMBER function for that item, and point all of my CUBEVALUE functions at that cube member. But how to get the MDX?

My first thought was to query the cube directly using the drilldown dimension, but that would get the values, but not the MDX.

The MDX must be somewhere within the pivot, otherwise I cannot see how a reference to the pivot field works when embedded in a CUBE function, but with all of the delving into the properties of the pivot object, I could not see where.

Retrieveing PageField members

I did figure out how to get details of all members of the PageField by iterating through the cube as in the example below.


Private Function IterateCubeToGetMembers()
Dim mpConn As ADODB.Connection
Dim mpCat As ADOMD.Catalog
Dim mpDim As ADOMD.Dimension
Dim mpHier As ADOMD.Hierarchy
Dim mpLevel As ADOMD.Level
Dim mpMember As ADOMD.Member
Dim mpData As Range
Dim mpCell As Range
Dim i As Long, j As Long

    Set mpData = wsApp.Range(NAME_REF_LIST_HIERARCHY)
    Set mpConn = New Connection
    mpConn.Open “Provider=MSOLAP;” & _
                “Initial Catalog=<;” OLAP_SERVER_NAME & _
                “Data Source”, OLAP_DB_NAME)
    Set mpCat = New ADOMD.Catalog

    With mpCat.CubeDefs(OLAP_CUBE_NAME)

        ReDim appVecHierarchies(1 To mpData.Rows.Count)
        i = 0

        For Each mpCell In mpData.Columns(1).Cells

            Set mpDim = .Dimensions(“[Company Drilldown]”)
            Set mpHier = mpDim.Hierarchies(mpCell.Offset(0, 1).Value)
            Set mpLevel = mpHier.Levels(mpCell.Offset(0, 2).Value)
            ReDim appVecMembers(1 To mpLevel.Members.Count)
            j = 0

            For Each mpMember In mpLevel.Members

                j = j + 1
                appVecMembers(j) = mpMember.Name
            Next mpMember

            i = i + 1
            appVecHierarchies(i) = appVecMembers
        Next mpCell
    End With

    Set mpDim = Nothing
    Set mpHier = Nothing
    Set mpLevel = Nothing
    Set mpMember = Nothing
    Set mpCat = Nothing
    Set mpConn = Nothing
    Set mpData = Nothing
End Function


Note, mpData.Columns(1).Cells is a list of different dimensions that I load into the PageField depending upon user selection.

This is a nice technique, but still only gets me the member values, it does not get the associated MDX.

Getting The MDX

Somewhere along my investigations, I recalled seeing that the pivotitem was showing the MDX in the SourceName property.

Getting the PivotItems on a PageField was problematical, so I couldn’t use that directly. But what if the drilldown dimension was not a PageField, but was in the rows area for example?

Obviously, I am not at liberty to change the original pivot, but I could create another one, load the drilldown dimension into the rows area, then loop through the PivotItems in PivotField(1) (we set it up with just the single pivot field). These PivotItems have the MDX in the SourceName property.

Final Solution

The following function builds arrays of the member and MDX values. It does it by

-          adding
a sheet

-          setting
up the pivot table

-          loading
the dimensions one by one into the Rows area

-          retrieving
all of the PivotItem values.


Private function LoadHierarchies()
CONST PIVOT_TEMP as string = “pvtTemp”
Dim appVecHierarchies As Variant
Dim appVecMembers As Variant
Dim mpSheet As Worksheet
Dim mpData As Range
Dim mpCell As Range
Dim mpPivotItem As PivotItem
Dim i As Long, j As Long

    Set mpSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    ThisWorkbook.PivotCaches _
        .Create(SourceType:=xlExternal, _
                SourceData:=ThisWorkbook.Connections(CONNECTION), _
                Version:=xlPivotTableVersion12) _
        .CreatePivotTable TableDestination:=mpSheet.Name & “!R5C4″, _
                          TableName:=PIVOT_TEMP, _

    Set mpData = wsApp.Range(NAME_REF_LIST_HIERARCHY).Columns(1)
    ReDim appVecHierarchies(1 To mpData.Cells.Count)
    For Each mpCell In mpData.Cells
        With mpSheet.PivotTables(PIVOT_TEMP)
            ‘store the next value in the pivot rows
            With .CubeFields(mpCell.Value)
                .Orientation = xlRowField
                .Position = 1
            End With

            With .PivotFields(1)

                j = 0
                ReDim appVecMembers(1 To .PivotItems.Count, 1 To 2)
                For Each mpPivotItem In .PivotItems

                    j = j + 1
                    appVecMembers(j, 1) = mpPivotItem.Caption
                    appVecMembers(j, 2) = mpPivotItem.SourceName
                Next mpPivotItem

                ‘we save the member details as an array within an array
                i = i + 1
                appVecHierarchies(i) = appVecMembers
            End With

            ‘remove the pivotfield ready for next
            .CubeFields(mpCell.Value).Orientation = xlHidden
        End With
    Next mpCell


    Set mpPivotItem = Nothing
    Set mpSheet = Nothing
    Set mpCell = Nothing
    Set mpData = Nothing
End Function

appVecHierarchies and appVecMembers are global arrays for storing the member values and MDX.

One Oddity

On a slight tangent, I found one oddity in this technique.

When the company drilldown was loaded into Page area, all of the companies in the cube showed up. When loaded into the Rows area, only some of the companies showed. This was due to the fact that some of the companies had no associated data.


I have a solution, but it is slow to load, and I cannot say I am happy with it.

It seems to me that the MDX must be held somewhere in the pivot so that the link from cube functions work, but I cannot find it.

I would welcome any thoughts on this, a better technique, or anything else.