Skip to Content
0

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

Feb 22, 2017 at 06:47 AM

118

avatar image

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.

10 |10000 characters needed characters left characters exceeded

What type of representation are you expecting? any sample? In one column itself or in a separate column....

0

I would like to see the complete list of Member IDs in one column with the corresponding parents in other columns. For clarification, I would like to see the Member IDs in column A with the corresponding values for ParentH1 in Column B (each parent on the appropriate row for the associated Member ID), ParentH2 in Column C, etc.

0

Then both options suggested by Vadim are suitable for your case....

0

Any issues with my answers?

0

Works great now! The only issue I had was the strConn value. I had to use a full connection name to get the correct connection, but it now works.

Thanks so much!

0
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Vadim Kalinin Feb 22, 2017 at 07:06 AM
0
Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Feb 22, 2017 at 07:23 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Paul Whittington Feb 23, 2017 at 05:44 PM
0

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/

Show 1 Share
10 |10000 characters needed characters left characters exceeded

You can adjust the code in my blog to your requirements...

1