Skip to Content
Technical Articles
Author's profile photo Vadim Kalinin

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

Assigned Tags

      44 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Benjamin Tan
      Benjamin Tan

      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

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      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

      Author's profile photo Benjamin Tan
      Benjamin Tan

      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

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      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

      Author's profile photo Asad Ahmed
      Asad Ahmed

      This can be achieved using Pivot Table on the Flat Export from Dimension (.csv)

      Author's profile photo Gajendra Moond
      Gajendra Moond

      Good stuff Vadim!

      Author's profile photo Axel LEMAITRE
      Axel LEMAITRE

      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

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      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

      Author's profile photo Axel LEMAITRE
      Axel LEMAITRE

      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.

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      Super Good work. I need a guy like you in my team

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      Sorry, but not clear!

      To select filtered by property list of members use VBA: OpenFilteredMemberSelector

      Vadim

      Author's profile photo Former Member
      Former Member

      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 ?

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      No, only loop! But you will have to get only specific property, not all...

      Author's profile photo Pratyush panda
      Pratyush panda

      Hi Vadim,

      Can you please send  https://archive.sap.com/documents/docs/DOC-55622 VBA code text attachment to email id itsmaniever@gmail.com

      Thanks in Advance
      Mani

      Author's profile photo Joseph Tuawin
      Joseph Tuawin

      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

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      Have you tested my code?

      Author's profile photo Joseph Tuawin
      Joseph Tuawin

      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.

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

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

      Author's profile photo Joseph Tuawin
      Joseph Tuawin

      Hi Vadim,

      This is the error after checking the said references.

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      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

       

      Author's profile photo Joseph Tuawin
      Joseph Tuawin

      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!

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      "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

       

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      P.S. I have updated the code to improve performance and compatibility with AO.

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      P.P.S. Found error in my code - incorrect case (only upper case) in ID column. Code corrected.

      Author's profile photo Steven Rider
      Steven Rider

      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
      
      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      The code is completely rewritten! Please test.

      Author's profile photo Joseph Tuawin
      Joseph Tuawin

      The initial code worked but the processing time is a bit long.

       

      Tested the updated code and this is the error.

      Author's profile photo Joseph Tuawin
      Joseph Tuawin

      Hi Vadim,

       

      I removed that line and it worked. Few seconds for 21k members.

      Thank you so much.

       

      Joseph

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      Yes, this line has to be removed!

      Author's profile photo Joseph Tuawin
      Joseph Tuawin

      Thanks a lot. I'll be checking your other blogs to see if I can use it also. 🙂

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      You can also ask questions on EPM tag!

      Author's profile photo Joseph Tuawin
      Joseph Tuawin

      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

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

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

       

      Author's profile photo Joseph Tuawin
      Joseph Tuawin

      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!

       

       

       

       

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      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.

       

       

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      P.S. Do you know how to loop cells? Also range to array method can be used...

      Author's profile photo Joseph Tuawin
      Joseph Tuawin

      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

      Author's profile photo Vadim Kalinin
      Vadim Kalinin
      Blog Post Author

      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