Essbase

Get Involved. Join the Conversation.

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