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”
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.