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


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

                       “[Activity Date].[Date


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,

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


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

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.

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.


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 =
    Set mpConn = New Connection
“Provider=MSOLAP;” & _
Catalog=<;” OLAP_SERVER_NAME & _
Source”, OLAP_DB_NAME)
    Set mpCat = New ADOMD.Catalog

    With mpCat.CubeDefs(OLAP_CUBE_NAME)

        ReDim appVecHierarchies(1 To
        i = 0

        For Each mpCell In

            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
            j = 0

            For Each mpMember In mpLevel.Members

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

            i = i + 1
            appVecHierarchies(i) =
        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?

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

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

a sheet

up the pivot table

the dimensions one by one into the Rows area

all of the PivotItem values.


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 =
    ThisWorkbook.PivotCaches _
        .Create(SourceType:=xlExternal, _
SourceData:=ThisWorkbook.Connections(CONNECTION), _
Version:=xlPivotTableVersion12) _
TableDestination:=mpSheet.Name & “!R5C4”, _
TableName:=PIVOT_TEMP, _

    Set mpData =
    ReDim appVecHierarchies(1 To
    For Each mpCell In mpData.Cells
the next value in the pivot rows
                .Orientation = xlRowField
                .Position = 1
            End With

            With .PivotFields(1)

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

                    j = j + 1
                    appVecMembers(j, 1) =
                    appVecMembers(j, 2) =
                Next mpPivotItem

‘we save the member details as an array within an array
                i = i + 1
                appVecHierarchies(i) =
            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.


2 thoughts on “Cycling Through The Fog”

  1. On a tangent in Excel 2010 if you build a PowerPivot it gets treated as a pivot on an olap source and you can then covert the pivot table in a “formula table” by clicking on OLAP Tools – convert to formulas.

    Each element of the pivot gets converted in to a formula based on Cube functions.

    Slicers continue to work on the “formula” pivot table… its a really nice improvement

Leave a Reply

Your email address will not be published. Required fields are marked *