Skip to Content
0

How to export Hierarchy in excel or CSV format

Oct 02, 2017 at 01:36 PM

91

avatar image
Former Member

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)

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Vadim Kalinin Oct 02, 2017 at 05:23 PM
0

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.


hi.png (68.1 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Oct 02, 2017 at 03:05 PM
0

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

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

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

0
Former Member

Hi Vadim,

This code takes a table like this

and turns it into a view like this

table.png (5.7 kB)
hierarchie.png (5.1 kB)
0
Former Member

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.

0

Same can be done with local members...

0
Vadim Kalinin Oct 02, 2017 at 01:47 PM
0

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!

Share
10 |10000 characters needed characters left characters exceeded