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

GetMemberCaption(strConn, strDim & ":" & strMemShortName)

to get member strMemShortName description of the dimension strDim

GetPropertyValue(strConn, strMemFullName, strProperty)

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

There is some specifics of GetPropertyValue – full member name have to be in the proper hierarchy to get properies like PARENTH1…

The code of the procedure is attached in the text file.

The result of the procedure execution:

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

B.R. Vadim

The attached text file can be found here: https://answers.sap.com/storage/attachments/3793-bpc-nw-10-vba.txt

To report this post you need to login first.

20 Comments

You must be Logged on to comment or reply to a post.

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

    (0) 
    1. Vadim Kalinin 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

      (0) 
      1. 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

        (0) 
        1. Vadim Kalinin 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

          (0) 
  2. 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

    (0) 
    1. Vadim Kalinin 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

      (0) 
      1. 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.

        (0) 
        1. Vadim Kalinin 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

          (0) 
  3. Jaco de Kock

    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

    (0) 
    1. Vadim Kalinin 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

      (0) 
      1. Jaco de Kock

        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

        (0) 
        1. Vadim Kalinin 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

          (0) 
  4. sushant patil

    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.

    (0) 
      1. Wagner M. Sobiesiak

        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 ?

        (0) 

Leave a Reply