How to export Hierarchy in excel or CSV format

[former_member481715]

Hello,

This is for BPC 10.1, is there a way to export the dimension hierarchy in excel (not the normal dimension view with parenth1 as property but rather hierarchy view thats normally displayed in excel epm add in)

[former_member186338 · Accepted solution]

Sample without VBA (using local members):

Local members:

LM001 (after column axis): =IF(EPMDIM_CURRENT_MEMBER(P_ACCOUNT)="MIN","",EPMMemberProperty(,EPMMemberID(EPMDIM_CURRENT_MEMBER(P_ACCOUNT)), "HLEVEL")*1)

LM002 (before row axis): =MIN(OFFSET(EPMALLMEMBERS,0,1))

LM003 (after LM001): =IF(EPMPOSITION(2)<>"",EPMPOSITION(2)-$B$2,"")

LM004 (after LM003): =IF(COLUMN()-COLUMN(E1)=EPMPOSITION(3),EPMDIM_CURRENT_MEMBER(P_ACCOUNT),"")

LM005 (after LM004): =IF(COLUMN()-COLUMN(E1)=EPMPOSITION(3),EPMDIM_CURRENT_MEMBER(P_ACCOUNT),"")

...

same up to:

LM012 (after LM011): =IF(COLUMN()-COLUMN(E1)=EPMPOSITION(3),EPMDIM_CURRENT_MEMBER(P_ACCOUNT),"")

If more levels required - add more local members with the same formula.