on 10-02-2017 2:36 PM
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)
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.