Find the member key in Excel

I use Excel as my main OLAP cube browser and sometimes need to retrieve the member key for a Dimension member that is visible in the Pivot table.

None of the Cube functions in Excel (CUBEVALUE, CUBEMEMBER etc.) is able to give me the information even though when I convert the pivot table to formulas it actually uses the member key in the formula – so how do I get to that information

In order to retrieve the member key I created a function in VBA in my personal macro workbook.

Function GetSourceName(Pivotcell As Range) As String

Dim pvi As PivotItem

‘In order to catch if the reference pivot cell is not within a pivottable

On Error Resume Next

Set pvi = Pivotcell.PivotItem

On Error GoTo 0

If Not pvi Is Nothing Then

GetSourceName = Pivotcell.PivotItem.SourceName


GetSourceName = “Range is not a pivotitem”

End If

End Function

This gives me the possibility to retrieve the member keys in a formula next to the pivot table.

This helps me a lot when creating custom mdx’s in Query design or report designer in BIDS, and also when setting up Report subscriptions.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s