BPC NW 10: VBA to get dimension members list and properties

[former_member186338]

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("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 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!

---

References:

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