Category Archives: 12353

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.


Styles AIn’t What They Used To Be


Everybody knows that if you are going to create a meaningful document in Word, you use Styles. Excel has styles as well, but how many of us use these built-in styles, or add to them with our own custom styles?

I have been building a styles utility recently, so I have delved deeply into this functionality. Clearly, one of the principal aims of such a utility would be the ability to edit styles. Being a rational coder, I don’t want to re-invent the wheel, so I decided to use the built-in styles editor. This is easily called using dialogs




From here, the style can be tailored to your needs, a new style can be added, or a style can be deleted. This dialog defaults to the Normal style, but you can pre-load the style by passing it as an argument


Application .(xlDialogApplyStyle).Show “myParticularStyle”


So far so good. This all looks ideal.

Unfortunately, things are never that simple. The following is a code snippet that should create a new style called NFx1Dec and open the styles edit dialog for that style.


Const StyleName As String = “NFx1Dec”

    On Error Resume Next
    On Error GoTo 0

    Activecell.Style = “Normal”
    ActiveWorkbook.Styles.Add StyleName, Activecell
    Application.Dialogs(xlDialogApplyStyle).Show ActiveWorkbook.Styles(StyleName)

If you call this dialog from VBA as shown above, select your style to work with, and then click the Modify button, you are presented with a series of tabs to adjust the particular style properties. From here, select the Patterns tab and you see a colour palette, a pattern dropdown, and a sample preview box.

So, you want to change the fill colour. Simple, just select a colour from the colour palette. Of course, it is not simple, otherwise I wouldn’t be writing this. Select the colour, and nothing happens. You do not see that colour in the Sample box (as you would if you were modifying this style in Excel), and when you exit, that style does not have that fill colour.

You can force it by selecting the pattern option to say solid, but you don’t have to do this in Excel, so you shouldn’t have to just because it is being invoked from VBA. You can also force it by selecting your colour, then select No Colour, then select you desired colour again (what!).

I tried various things to try and force the pattern to be solid before invoking the tab, such as


    ActiveWorkbook.Styles(StyleName).Interior.Pattern = xlSolid
    ActiveWorkbook.Styles(StyleName).Interior.PatternColorIndex = xlAutomatic

but no joy. So, a  real failing on using what is a useful function of Excel from within VBA.

I was discussing this with Jan Karel Pieterse, a Dutch Excel MVP. Jan Karel also has an interest in styles; he has a page about using styles on his website Jan Karel came up with the following workaround


Const StyleName As String = “NFx1Dec”
Dim CB As CommandBar
Dim sMenuKeys As String
Dim sMenuCap As String
    Set CB = Application.CommandBars(“Worksheet Menu Bar”)
    sMenuCap = CB.FindControl(Id:=30006, recursive:=True).Caption
    sMenuKeys = “%” & Mid(sMenuCap, InStr(sMenuCap, “&”) + 1, 1)
    sMenuCap = CB.FindControl(Id:=254, recursive:=True).Caption
    sMenuKeys = sMenuKeys & Mid(sMenuCap, InStr(sMenuCap, “&”) + 1, 1)
    On Error Resume Next
    On Error GoTo 0

    Activecell.Style = “Normal”
    ActiveWorkbook.Styles.Add StyleName, Activecell
    SendKeys sMenuKeys & ActiveWorkbook.Styles(StyleName)

Although this uses the dreaded SendKeys, it does seem to work. As a stand-alone procedure, it does work, but when embedded in an application, it had some serious problems.

… to be continued