Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
Showing results for 
Search instead for 
Did you mean: 
Active Contributor

Updated on 2019.06.18: Complete code redesign for better performance.

Updated on 2019.06.14: Code corrected to support proper case for ID column.

Updated on 2019.06.13: Fast code to get property value and late binding to work on EPM or AO.

EPM Excel Add-In provides number of methods to get members and properties. I decided to write some procedure to get all members of the specified dimension with all properties including hierarchies parents. The result is written to the selected Excel sheet the same way as in Administration client of BPC NW 7.5.

Methods of FPMXLClient.EPMAddInAutomation used:
GetPropertyList(strConn, strDim)

to get all properties of the dimension strDim
GetHierarchyMembers(strConn, "", strDim)

to get all members of all hierarchies ("") of the dimension strDim (base members in different hierarchies will be duplicated!)
GetPropertyValue(strConn, strMemFullName, strProperty)

to get member strMemFullName ([...].[...].[...]) property strProperty

epm.GetPropertyValue function was replaced by Application.Run(“EPMMemberProperty”… to improve performance based on evgenij.samardak finding in comment to my other blog!
Application.Run("EPMMemberProperty", "", strMemFullName, strProperty)

to get member strMemFullName ([...].[...].[...]) property strProperty

There is some specifics of getting property values - full member name have to be in the proper hierarchy to get properties like PARENTH1...

The following references are required in Tools -> References:

Reference to FPMXLClient is not required, universal late binding is used in the code!

The code of the procedure:
Option Explicit

Public Sub ShowDimMembers()
' To test DimMembersProperties

DimMembersProperties "Your_Connection_Name", "Dimension_Name", ThisWorkbook.ActiveSheet

End Sub

Public Sub DimMembersProperties(strConn As String, strDim As String, wsh As Worksheet)
' References required: Microsoft Scripting Runtime
' Will fill wsh sheet starting from the first row, first column
' Developed by Vadim Kalinin 2014.06.04
' Redeveloped by Vadim Kalinin 2019.06.18

Dim objAddIn As COMAddIn
Dim epm As Object
Dim AOComAdd As Object
Dim blnEPMInstalled As Boolean

Dim strProps() As String
Dim strMem() As String
Dim strMemID As String

Dim lngTemp As Long
Dim lngTemp1 As Long

Dim lngMemUCount As Long
Dim lngPropCount As Long

Dim dctMembers As New Scripting.Dictionary
Dim dctProps As New Scripting.Dictionary

Dim varKey As Variant
Dim varItem As Variant
Dim varResult() As Variant

'Universal code to get FPMXLClient for standalone EPM or AO
For Each objAddIn In Application.COMAddIns
If objAddIn.progID = "FPMXLClient.Connect" Then
Set epm = objAddIn.Object
blnEPMInstalled = True
Exit For
ElseIf objAddIn.progID = "SapExcelAddIn" Then
Set AOComAdd = objAddIn.Object
Set epm = AOComAdd.GetPlugin("")
blnEPMInstalled = True
Exit For
End If
Next objAddIn

If Not blnEPMInstalled Then
MsgBox "EPM is not installed!"
Exit Sub
End If

'Get all properties of dimension strDim
strProps = epm.GetPropertyList(strConn, strDim)

'Fill dictionary with properties
dctProps.Add "ID", "ID"

For lngTemp = 0 To UBound(strProps)
If strProps(lngTemp) <> "47932f46-b7b1-4207-b693-d9f7a18aaaed" And _
strProps(lngTemp) <> "CALC" And _
strProps(lngTemp) <> "HLEVEL" And _
strProps(lngTemp) <> "HIR" And _
Not dctProps.Exists(strProps(lngTemp)) Then
dctProps.Add strProps(lngTemp), strProps(lngTemp)
End If
Next lngTemp

lngPropCount = dctProps.Count

'Get all members with possible duplicates due to multiple hierarchies
strMem = epm.GetHierarchyMembers(strConn, "", strDim)

'Fill dictionary dctMembers with unique member ID's
For lngTemp = 0 To UBound(strMem)
lngTemp1 = InStrRev(strMem(lngTemp), "[", -1) '"[DIM1].[PARENTH1].[MEM1]"
strMemID = Mid(strMem(lngTemp), lngTemp1 + 1, Len(strMem(lngTemp)) - lngTemp1 - 1)
'strMemID will be in upper case!
If Not dctMembers.Exists(strMemID) Then
dctMembers.Add strMemID, strMem(lngTemp)
End If
Next lngTemp

lngMemUCount = dctMembers.Count

ReDim varResult(1 To lngMemUCount + 1, 1 To lngPropCount)

'Fill header row - list of properties
lngTemp = 1
For Each varKey In dctProps.Keys
varResult(1, lngTemp) = varKey
lngTemp = lngTemp + 1
Next varKey

'Fill table of members and properties
lngTemp = 2
For Each varItem In dctMembers.Items
lngTemp1 = 1
For Each varKey In dctProps.Keys
If Left(varKey, 7) = "PARENTH" Then
'For PARENTx properties
strMemID = Left(varItem, InStr(2, varItem, "[")) & varKey & Mid(varItem, InStrRev(varItem, ".", -1) - 1)
varResult(lngTemp, lngTemp1) = Application.Run("EPMMemberProperty", "", strMemID, varKey)
If varResult(lngTemp, lngTemp1) = "The member requested does not exist in the specified hierarchy." Or _
varResult(lngTemp, lngTemp1) = 0 Then
varResult(lngTemp, lngTemp1) = ""
End If
'For other properties
varResult(lngTemp, lngTemp1) = Application.Run("EPMMemberProperty", "", varItem, varKey)
End If
lngTemp1 = lngTemp1 + 1
Next varKey
lngTemp = lngTemp + 1
Next varItem

Set dctMembers = Nothing
Set dctProps = Nothing

wsh.Range(wsh.Cells(1, 1), wsh.Cells(lngMemUCount + 1, lngPropCount)).Value = varResult

Set wsh = Nothing

End Sub

The result of the procedure execution:

You can modify this code to create custom member selection pop-ups, etc...

B.R. Vadim

P.S. If you implement Excel Add-In for BPC 7.5 to test Dimension then you can view hierarchy on the result of this code!


BPC NW 10: VBA function to get dimension members list by Property value

Labels in this area