Skip to Content
avatar image
Former Member

How can I pull hierarchy data (parents) for each member of a dimension into Excel?

I have two dimensions (Entity and Account) that each have multiple hierarchies. I would like to be able to generate a list of all members in the dimensions along with their respective parents (PARENTH1, PARENTH2, PARENTH3, etc) in Excel. I do not want to have to manually export the dimensions via the web interface since there are so many environments.

I can pull the dimensions, members, and properties from the BW back end using VBA with the exception of the Hierarchies/parents of each member. I can also generate a list using the methods described in the article http://www.vbforums.com/showthread.php?538998-Can-t-create-dictionary-in-Excel-VBA

However, I'm still having trouble getting the relationships between the Dimension members and the parents for each hierarchy.

Any help would be appreciated.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Feb 22, 2017 at 07:06 AM
    Add comment
    10|10000 characters needed characters exceeded

  • Feb 22, 2017 at 07:23 AM

    Alternative:

    Create EPM report with ALL members selected in the row axis and add local members for each hierarchy:

    =EPMMemberProperty(,EPMMemberID(EPMDIM_CURRENT_MEMBER(DIMENSIONNAME)),"PARENTH1")

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 23, 2017 at 05:44 PM

    Thanks so much Vadim! Both answers work. The link you provided gives me the best answer, but the line of code to pull a single property will definitely be useful in the future.

    Best Answer:

    https://blogs.sap.com/2014/06/04/bpc-nw-10-vba-to-get-dimension-members-list-and-properties/

    Add comment
    10|10000 characters needed characters exceeded