GetChildrenFromMember API Function

[Former Member]

Hey BPC'ers

I'm working with the EPM VBA functions and I can't seem to get children associated with a specific dimension member using the provided functionality. I'm trying to get the following code to work:

***CODE***

Sub NonWorkingCode()

    Dim EPMObj As Object

    Set EPMObj = CreateObject("FPMXLClient.EPMAddInAutomation")

    'Dim EPMObj As New FPMXLClient.EPMAddInAutomation

    Dim conn As String

    Dim Children() As String

    conn = EPMObj.GetActiveConnection(ActiveSheet)

    Children = EPMObj.GetChildrenFromMember("DEPT878", "PARENTH1", 1, 10, conn, True)

End Sub

***/CODE***

The function only ever returns an empty string array. A similar VBA function works just fine to return the entire hierarchy:

***Other Code***

Sub WorkingCode()

    Dim EPMObj As Object

    Set EPMObj = CreateObject("FPMXLClient.EPMAddInAutomation")

    'Dim EPMObj As New FPMXLClient.EPMAddInAutomation

    Dim conn As String

    Dim Hier() As String

    conn = EPMObj.GetActiveConnection(ActiveSheet)

    Hier = EPMObj.GetHierarchyMembers(conn, "PARENTH1", "DPC")

End Sub

***/CODE***

My hierarchy has thousands of members, and I would rather get just the subset I need from the selected parent member. DEPT878 is a member in my "DPC" dimension that has a hierarchy labeled "PARENTH1".  Does anyone have any suggestions or have had success with the GetChildrenFromMember function?

Thanks!,

Ryan

[former_member186338 · Accepted solution]

Hi Ryan,

If you look on help for this function you will see:

42.4.8 GetChildrenFromMember

Applies to: Local and SAP BusinessObjects connections

Not for Planning and Consolidation connections...

Vadim

[Former Member · Accepted solution]

Well that's a bummer.  Thanks Vadim.

[former_member186338 · Accepted solution]

P.S. You can read the whole hierarchy and process it like in my document:

Vadim

[former_member186338 · Accepted solution]

For sure the code just to get children will be simple:

Public Sub GetChildren(strConn As String, strDim As String, strHier As String, strParentMem as String)

' References required: FPMXClient

' Result is stored in the string array strChildMem

    Dim epm As New FPMXLClient.EPMAddInAutomation

    Dim strMem() As String

    Dim strChildMem() As String

    Dim lngTemp As Long

    Dim lngTemp1 As Long

    Dim lngTemp2 As Long

    strMem = epm.GetHierarchyMembers(strConn, strHier, strDim)

    ReDim strChildMem(0 To UBound(strMem))

    lngTemp2 = 0

    For lngTemp = 0 To UBound(strMem) ' loop all members of strDim and strHier

       IF epm.GetPropertyValue(strConn, strMem(lngTemp), strHier) = strParentMem Then

           lngTemp1 = InStrRev(strMem(lngTemp), "[")

           strChildMem(lngTemp2) = Mid(strMem(lngTemp), lngTemp1 + 1, Len(strMem(lngTemp)) - lngTemp1 - 1)

           lngTemp2 = lngTemp2 + 1

       End If

    Next lngTemp

    ReDim strChildMem(0 To lngTemp2 - 1)

End Sub

Vadim

P.S. I haven't tested it, but the idea is simple...