Skip to content
September 29, 2011 / Erik Svensen

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.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: