Business Analyticspublic

Essbase

Get Involved. Join the Conversation.

This is a public Forum  publicRSS

Topic

    Gilbert Lindsay
    Formula to retrieve the top ancestor for an entity member
    Topic posted June 20, 2019 by Gilbert Lindsay, tagged Essbase, Smart View 
    50 Views, 2 Comments
    Title:
    Formula to retrieve the top ancestor for an entity member
    Summary:
    Trying to run a report that identifies the parent or top ancestor for all entity members
    Content:

    Trying to run a report that identifies the parent or top ancestor for all entity members through Smartview function or query. 

    Any thoughts?

    Comment

     

    • Mike Larimer

      There is a Smart View function for getting the ancestor of a member:

      Sub RunGetAncestor()
          Dim sts As Long
          Dim vArray As Variant

          Sheet1.lstMemberInfo.Clear
          
          RunConnect
          
          Dim vMbr As Variant
          sts = HypGetAncestor(Empty, "Diet Cola", "Gen", 2, vMbr)
          If (sts <> 0) Then
              MsgBox GetReturnCodeMessage(sts)
          End If
          Sheet1.lstMemberInfo.AddItem vMbr

          RunDeleteMetaData
          ActiveSheet.UsedRange.Clear

      End Sub

      There is a Smart View function which will give you the dimensions names associated with a data cell:

      Sub RunGetDimMbrsForDataCell()
          Dim sts As Long
          Dim vDims, vMbrs As Variant
          
          ActiveSheet.Cells.ClearContents

          RunConnect
          
          Range("B1").Value = "Sales"
          Range("A2").Value = "Feb"
          sts = HypRetrieve(Empty)
          If (sts <> 0) Then
              MsgBox GetReturnCodeMessage(sts)
          End If
          
          sts = HypGetDimMbrsForDataCell(Empty, Range("B2"), Host$, AppName$, DbName$, Empty, vDims, vMbrs)
          If (sts <> 0) Then
              MsgBox GetReturnCodeMessage(sts)
              GoTo discon
          End If
          If (IsArray(vDims)) Then
              Sheet1.lstMemberInfo.List = vDims
          End If
          MsgBox "Dimensions"
          If (IsArray(vMbrs)) Then
              Sheet1.lstMemberInfo.List = vMbrs
          End If
          MsgBox "Members"

      discon:
          RunDeleteMetaData
          ActiveSheet.UsedRange.Clear

      End Sub

       

    • Tim Faitsch

      Since you are using OAC, you can use Cube Designer to Export a cube to local workbook. That should give you parent child in Excel.

     

    community forum