Skip to Content
avatar image
Former Member

How to export Hierarchy in excel or CSV format

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)

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Oct 02, 2017 at 05:23 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 02, 2017 at 03:05 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 02, 2017 at 01:47 PM

    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!

    Add comment
    10|10000 characters needed characters exceeded