Skip to Content
Technical Articles

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

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 Evgeniy 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

44 Comments
You must be Logged on to comment or reply to a post.
  • Hi Vadim,

    Good article!

    I’m wondering if there’s a way, either on Excel front-end or through VBA, to capture the number of base members below a Node.  For instance, if I select CC_Node1, I will be able to get a variable value of 3, which I can use in other calculation or vba. 

    Thanks in advance,

    Ben

    Member_Count.jpg

    • Hi Ben,

      The main question is – what for? 🙂

      In VBA it’s possible, but very complicated in general. Your goal is to get base members under the parent, not direct children!

      On the report in some cases you can use local member with Excel COUNT…

      Vadim

      • Hi Vadim,

        We have a very large cost centre dimension with more than 40000 members. Obviously it will take a long time to process if the users select the top 1/2/3 level of Nodes.  So I think it will be helpful to the user in some reports if they get a msgbox telling them that they have choosen a node that has more than #(threshod) of base cost centers and warn them of longer wait time if they decide to continue.  This is where a variable capturing COUNT of BAS member under any Node can be used.  (COUNT of ALL members under the node is also helpful)

        I saw that in the EPM Member Selector, there is a number in () indicating the # of all dependent members.  I was wondering if there is an easy way to capture that.

        Hope that clarifies.

        Ben

        • Unfortunately you have only GetChildrenFromMember that will return the list of direct children of the selected parent (to be checked). For other you have to use GetHierarchyMembers for the full list and analyze it…

          Or you can try recursively use GetChildrenFromMember…

          Nothing else.

          Vadim

  • Hi Vadim,

    Thank you for this great functionality.

    Do you think it is possible to get the dimension members list on BPC 7.5NW with this kind of VBA?

    Best Regards,

    Axel

    • Hi Axel,

      No, not possible for 7.5NW – no API at all 🙂 You can refresh the sheet with VBA having =EVEXP(… formula on this sheet.

      Vadim

      • This is a very interesting function! Only disadvantage is I have to execute an expand if I want to visualize a newly added member. Thank you very much for your help.

        • Yes, you have to execute expand… We use the sheet with this function to get members with properties, read result in VBA and perform some other activities in VBA.

          Vadim

  • Hi Vadim,

    Thank you so much!! This is super useful, but I would just like to add the following:

    Once all the ID’s (for my case I just needed the ID’s of +- 30 000 Cost Centers) have been gathered, it’s better to write (literately dump) the array after all Id’s have been gathered.

    I came to the conclusion by putting in brake points before the loop and after. I saw that the vba gets all the ID’s in 1 second for 30 000 members! The problem is writing cell by cell to the sheet, that takes more or less 2 mins for the 30 000 cost centers.

    I changed your code a bit, to get:

    1. Just the ID’s

    2. Dump the array outside the initial loop.

    This executes in 2 seconds!!

    Feel free to try this, here is my code:

    Sub Button1_START()

    DimMembersProperties “”, “COST_CENTRE”, Sheets(“CostCenter”)

    End Sub

    Public Sub DimMembersProperties(strConn As String, strDim As String, wsh As Worksheet)

    Sheets(“CostCenter”).Range(“A:A”).Clear

    Dim strMemArr() As String

    Dim objEPM As New EPMAddInAutomation

    Dim lngTemp As Long

    Dim lngTemp1 As Long

    strConn = objEPM.GetActiveConnection(Sheet1)

    strMemArr = objEPM.GetHierarchyMembers(strConn, “”, strDim)

    ‘Get all ID’s into Array

    For lngTemp = 0 To UBound(strMemArr)

        lngTemp1 = InStrRev(strMemArr(lngTemp), “[“) + 1

        strMemArr(lngTemp) = Mid(strMemArr(lngTemp), lngTemp1, Len(strMemArr(lngTemp)) – lngTemp1)

    Next lngTemp

    ‘Dump Array to sheet

    Dim Destination As Range

    Set Destination = Sheets(“CostCenter”).Range(“A1”)

    Set Destination = Destination.Resize(UBound(strMemArr), 1)

    Destination.Value = Application.Transpose(strMemArr)

           

    MSG = strDim & ” has been successfully updated!”    ‘ Define message.

    Style = vbOKOnly + vbInformation    ‘ Define buttons.

    Title = “Succeeded”    ‘ Define title.

    Help = “DEMO.HLP”    ‘ Define Help file.

    Ctxt = 1000    ‘ Define topic

            ‘ context.

            ‘ Display message.

    Response = MsgBox(MSG, Style, Title, Help, Ctxt)

    End Sub

    Credit must go to you for the top part of the code, but it is insanely fast to do the dump outside the loop. Hope this helps!

    Regards,

    Jaco de Kock

    • Hi Jaco,

      For sure if you need to fill a lot of cells on the Excel sheet you have to use array to range copy:

      Dim lngStartRow, lngStartColumn, lngRows, lngColumns As Long

      Dim lngTemp1, lngTemp2 As Long

      Dim rngDest As Range

      Dim varValueArr() As Variant ‘Variant!!!!

      lngStartRow = 3

      lngStartColumn = 2

      lngRows = 5

      lngColumns = 8

      With ThisWorkbook.Worksheets(“Sheet1”)

      Set rngDest = .Range(.Cells(lngStartRow, lngStartColumn), .Cells(lngStartRow + lngRows – 1, lngStartColumn + lngColumns – 1))

      End With

      ReDim varValueArr(1 To lngRows, 1 To lngColumns)

      For lngTemp1 = 1 To lngRows

          For lngTemp2 = 1 To lngColumns

              varValueArr(lngTemp1, lngTemp2) = lngTemp1 * 100 + lngTemp2

          Next lngTemp2

      Next lngTemp1

      rngDest.Value = varValueArr

      Test…

      Vadim

      • Hi Vadim,

        My code is working perfectly, I am just saying that pasting to an array is so much quicker.

        I saw you giving the code i used to a person and in the loop was the write to cell.

        I just pasted to the range outside the loop, and it takes 2 seconds witch is unbelievably quick.

        This is where I do it in my code

        ‘Dump Array to sheet

        Dim Destination As Range

        Set Destination = Sheets(“CostCenter”).Range(“A1”)

        Set Destination = Destination.Resize(UBound(strMemArr), 1)

        Destination.Value = Application.Transpose(strMemArr)

        I only have to use “A1” because I only use ID’s, and i want them top down in rows, not in columns.

        I just wanted to add the code for somebody else who might have a similar situation.

        Thanks,

        Jaco

        • Please, look again on the code from my previous message. The loop is used just to fill the array with some figures (for test purpose).

          The real assignment of array content to the range is here:

          rngDest.Value = varValueArr

          no Transpose etc…

          My code illustrates the following:

          1. You have some range with lngStartRow, lngStartColumn, lngRows, lngColumns

          2. You create array with the same size

          3. You fill array with some values

          4. Copy contents of array to Excel range

          This test will be executed in less then 50 msec…

          Vadim

  • Hi Vadim,

    Very  useful document.

    I have one query.

    I have used EPMDimensionPropertyValues function to populate ID’s but, for this function I am not able to pass any filtering criteria.

    I trying to populate member based on some condition. Like if user pass some parameter based on that condition only Id’s I have to populate.

    For example If filtering criteria is Level property for time dimension, If user pass month then I want to populate only those members having level property equal to month.


    I can not EPMSelectMultiMemberID because I am not able to pass condition to this function and can not use context member since i have to give multiple selection of member.

    Also i can not go with the page axis since there are some security concerns.

    please help me on same.

      • Hi Vadim,

        Is there any way, using VBA, to filter a set of members by property ?

        Sample:

        On the VBA code, I need to return all members that represent a node (CALC=Y).

        I know that I could get all of them,  and use a loop to filter it, but that way is too onerous for the report.

        Is there any way to get ONLY the members that attend the condition ?

  • Hi Vadim,

     

    Do you have a version of this for EPM 10.0 SP 32 on Windows 10? Also would like to take chance if there is a vba code to pull CC Dimension as I need to provide it to many people regularly that don’t have BPC access.

    Thanks,

    Joseph

      • Yes, but having compiler error. I honestly do not know VBA much and just trying to search how can I easlity pull CC Dimension with VBA so I can easily share to people without BPC access.

        • Sorry, but your error is the result of not referencing Microsoft Scripting Runtime – clearly stated in the comment in my code…

          There are other methods to get dimension member list (DM export master data)…

          • Sorry, but you didn’t changed the parameters related to your system:

            DimMembersProperties “INFILE – SIM”, “PERIODS”, ThisWorkbook.ActiveSheet

            instead of “INFILE – SIM” you have to use your connection name 🙂

            Instead of “PERIODS” – your dimension name

             

          • Hi Vadim,

             

            Wow!! It actually worked, so it is puling the property based on how it is shown in BPC Excel environment. This is a very big help.

            Just to confirm, it just pulls data right? nothing else? As I only tested it in test environment and would like to try in the production.

             

            Do you have a VBA to pull data directly from Dimension in the BPC Admin Web? 🙂

             

            Thank you very much!

          • “Just to confirm, it just pulls data right? nothing else?” – nothing else, just reading master data. The execution speed can be improved, but if it’s OK then don’t worry!

            “Do you have a VBA to pull data directly from Dimension in the BPC Admin Web?” – not clear what do you mean… VBA has no access to web admin…

            You can also use VBA to launch DM package with master data export to text file and then read this text file in VBA

             

          • Nice. I like the minimal edits to the actual Excel file.

            Check your email when you have a moment, I have a version of something similar that might interest you.

            Also, here’s a clean way to embed a timer into your tool and alert users when the process is done.

            Public Sub ShowDimMembers()
            ' To test DimMembersProperties
            
            'put in a variable to time
                Dim beginTIME As Date: beginTIME = Now
            
            DimMembersProperties "Your_Connection_Name", "Dimension_Name", ThisWorkbook.ActiveSheet
            
            'show the time results in the status bar
                Application.StatusBar = "Completed in " & DateDiff("S", beginTIME, Now) & " seconds."
            
            End Sub
            
  • Hi Vadim,

     

    I am now using this code on pulling Dimension members in BPC. I am a beginner in VBA and cannot create complex codes. Would like to check if okay to ask for help on how can this code download members based on particular properties only. I am using this more on cost center dimension. So sample, download only all with Status =  Active. Then also if possible to include in the code a column sorting based on the list below as currently arrange in alphabetical order.

    ID
    DESCRIPTION
    CC_COPY
    CC_STATUS
    CC_OWNER
    GLOBAL_OWNER
    BPC_EXEC
    HR_EXEC
    FINANCE_OWNER
    FUNCTION
    ENTITY
    LOCATION
    COUNTRY
    REGION
    ALLOCABLE
    PARENTH1
    PARENTH2
    PARENTH3
    PARENTH4
    PARENTH5
    BPF_OWNER
    MFG_OWNER
    SGA_OWNER
    A_AM
    CORP_BU
    EBU
    SBU
    PG_BASE
    SCALING
    DATE_CREATED

    Thanks,

    Joseph

    • In order to get only members with specific property – use my blog: https://blogs.sap.com/2017/09/21/bpc-nw-10-vba-function-to-get-dimension-members-list-by-property-value/

      To have special sorting of columns – add properties manually:

      instead of:

          '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

      Use:

          'Fill dictionary with properties
          dctProps.Add "ID", "ID"
          dctProps.Add "EVDESCRIPTION", "EVDESCRIPTION"
          dctProps.Add "PROP1", "PROP1"
          dctProps.Add "PROP2", "PROP2"
          dctProps.Add "PROP3", "PROP3"
          ...
      

       

      • Hi Vadim,

        It worked! Thanks! Is it possible to make it dynamic, like depending on which dimension I am puling? Sample, if Cost center it will refer to column A list, then column B for the entity. Thanks!

         

         

         

         

        • Easy!

          Loop cells in Row 1 comparing cell value with strDim to find column with the list of properties for required dimension.

          Then loop this column starting at row 2 adding properties to the dictionary.

           

           

          • Hi Vadim,

            I don’t know actually. 🙂 I will try to search about that and hope I can make it work. Thank you very much! You have been a great help.

             

            Joseph

          • Something like:

            Public Sub FindColumn()
            Dim wshTest As Worksheet
            Dim strDim As String
            Dim strDimensionName As String
            Dim lngColNum As Long
            
            strDim = "ENTITY"
            Set wshTest = ThisWorkbook.Worksheets("Sheet4")
            
            lngColNum = 1
            strDimensionName = wshTest.Cells(1, lngColNum) 'First row!
            
            Do While strDimensionName <> ""
                If (strDimensionName = strDim) Then
                    Exit Do
                End If
                lngColNum = lngColNum + 1
                strDimensionName = wshTest.Cells(1, lngColNum)
            Loop
            Debug.Print CStr(lngColNum)
            End Sub

            Will check Row1 for dimension name. lngColNum will contain column number