## 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

CUBEVALUE(CubeName,

“[Transaction

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

“[Activity Date].[Date

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

“[Measures].[Gas_Amt_Net]”)

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,

CUBEVALUE(CubeName,

‘Pivot Sheet’!B10,

“[Activity

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

“[Measures].[Gas_Amt_Net]”)

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, _

DefaultVersion:=xlPivotTableVersion12

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

mpsheet.Delete

Set mpPivotItem = Nothing

Set mpSheet = Nothing

Set mpCell = Nothing

Set mpData = Nothing

End Function

** appVecHierarchies** and

**are global**

*appVecMembers*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.

## Summary

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.

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

install OLAP PivotTable Extensions and then look at the MDX tab for the Excel specific MDX behind a pivot table.

http://olappivottableextend.codeplex.com/