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)
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("com.sap.epm.FPMXLClient")
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"
dctProps.Add "EVDESCRIPTION", "EVDESCRIPTION"
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
Else
'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 SubThe 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!
References:
BPC NW 10: VBA function to get dimension members list by Property value
- SAP Managed Tags:
44 Comments
