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

Else

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.