cancel
Showing results for 
Search instead for 
Did you mean: 

How to export Hierarchy in excel or CSV format

former_member481715
Participant
0 Kudos

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)

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

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.

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Manny,

I don't think you can export the hierarchical view. But you can use the normal scv export and an Excel macro.

This is what I have used in the past

Public wsSrc As Worksheet, wsDst As Worksheet
Public lSrcFirstRow&, lChildCol&, lParentCol&, lSrcLastRow&, lDstFirstRow&, lDstFirstCol&
Public lDstRow&

Sub MakeTree()
Dim sNoParent As String

'*********************** Anpassen *************
Set wsSrc = ThisWorkbook.Worksheets("Sheet1") 'source table
lSrcFirstRow = 2 ' first row in source table
lChildCol = 1 ' colum with child ID
lParentCol = 2 ' colum with parent ID
sNoParent = "" ' marker in parent column for top level ids

Set wsDst = ThisWorkbook.Worksheets("Sheet2") ' destination table
lDstFirstRow = 1 ' first row in destination table
lDstFirstCol = 1 ' first column in destination table
'**********************************************

lSrcLastRow = wsSrc.Cells(Rows.Count, lChildCol).End(xlUp).Row
lDstRow = lDstFirstRow
Call ScanChilds(sNoParent, 0)
End Sub

Sub ScanChilds(sParent As String, iLevel As Integer)
Dim lRow&
For lRow = lSrcFirstRow To lSrcLastRow
If wsSrc.Cells(lRow, lParentCol) = sParent Then
wsDst.Cells(lDstRow, lDstFirstCol + iLevel) = wsSrc.Cells(lRow, lChildCol)
lDstRow = lDstRow + 1
Call ScanChilds(wsSrc.Cells(lRow, lChildCol), iLevel + 1)
End If
Next lRow
End Sub

former_member186338
Active Contributor
0 Kudos

Hi Arnold, unable to understand what do you want to achieve with this code? Can you show the result?

To my mind the same can be done without VBA...

Former Member
0 Kudos

Hi Vadim,

This code takes a table like this

and turns it into a view like this

Former Member
0 Kudos

An EPM report with indentation has all IDs in one column and uses formatting for the indentation. The result of the macro uses separate Excel columns.

former_member186338
Active Contributor
0 Kudos

Same can be done with local members...

former_member186338
Active Contributor
0 Kudos

Sorry, but not clear!

You can use epm report to have identation of members...

"but rather hierarchy view thats normally displayed in excel epm add in" - please show the expected result sample!