Forum Discussion

JasmineH's avatar
JasmineH
New Contributor
3 years ago

Member Filter Builder - Member Expansion Functions

When building a Cube View its helpful and best practice to build a report dynamically so that less maintenance is needed.  There is a Member Expansion Function "First" and "Last" but nothing in between (i.e., second, third etc.)

For example, if you use X#MyMember.Children.First.Children it will display all the children of your member as well as the grandchildren of the first child.  Last will do the same but give you the grandchildren of the last child instead.  It would be helpful to be able to reference different positions other than first and last in a hierarchy so that the member that you need grandchildren of doesn't need to specifically be called out in the member filter.

This would help my client tremendously with being able to have the most dynamic report possible in the event that members are added to the dimension hierarchy.

  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor

    Hello,
    Usually I would not recommend relying on the order of child members in the hierarchy. The order of member is often a forgotten admin task, and these may easily be moved around in an unexpected way if the application has some kind of metadata automation in the future. 

    Back to the question though: If you want to do a [ Get N-th Child ]  where N=1,2,3 etc then you could use an XFBR to do this.
    Example in GolfStream , CorpAccounts dimension:

    16999 - Net PP&E
          16099 - Machinery
          16199  - Land
                  16100 - Land at Cost
                  16105 - Land Depreciation   


    Let's say we wanted to get the 2nd child of 16999, and then get the children of that. ( which are A#16100,A#16105)

    Then you would do this in the row member filter in your CubeView or QuickView
    A#XFBR(CubeViewLists,GetNthChild,Parent=[16999],DimName=[CorpAccounts],N=2).Children

    Output:

    Below is the code that you would put into a Dashboard XFBR business rule.
    Don't be put off by the code, every line except for the last line is doing error checking.

    If args.FunctionName.XFEqualsIgnoreCase("GetNthChild") Then
      ' -- Example use in GolfStream app
      ' -- A#XFBR(CubeViewLists,GetNthChild,Parent=[16999],DimName=[CorpAccounts],N=2).Children
    
      Dim strParent As String = args.NameValuePairs.GetValueOrEmpty("Parent")
      Dim strDimName As String = args.NameValuePairs.GetValueOrEmpty("DimName")
      Dim strN As String = args.NameValuePairs.GetValueOrEmpty("N")
      If String.IsNullOrEmpty(strParent) _
          OrElse String.IsNullOrEmpty(strDimName) _
          OrElse String.IsNullOrEmpty(strN) Then
        Throw New XFException(si, "Error with arguments supplied to GetNthChild", _
          "Usage: XFBR(CubeViewLists,GetNthChild,Parent=[Parent Member Name],DimName=[Dimension Name],N=[Position 1,2,3 etc])")
      End If
    
      Dim myDimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, strDimName)
      If myDimPk Is Nothing Then
        Throw New XFException(si, "Error with arguments supplied to GetNthChild", _
          "Invalid dimension Name:" & strDimName)
      End If
    
      Dim iRequestedPosition As Integer
      If Not Integer.TryParse(strN,iRequestedPosition) _
          OrElse iRequestedPosition=0 Then
        Throw New XFException(si, "Error with arguments supplied to GetNthChild", _
          "Parameter N must be a non-zero integer")
      End If
    
      Dim iParentId As Integer = BRApi.Finance.Members.GetMemberId(si, myDimPk.DimTypeId, strParent)
      If iParentId.Equals(DimConstants.Unknown) Then
        Throw New XFException(si, "Error with arguments supplied to GetNthChild", _
          "Supplied Parent Name is invalid:" & strParent)
      End If
    
      Dim lstMbrs As List(Of Member) = BRApi.Finance.Members.GetChildren(si, myDimPk, iParentId)
      If lstMbrs.Count = 0 Then
        Throw New XFException(si, "Specified parent has no child members in this dimension", _
          "Parent Name:" & strParent)
      End If
    
      If lstMbrs.Count < iRequestedPosition Then
        Throw New XFException(si, _
          "Specified parent has less children than the position requested","Parent Name:" _
          & strParent & ", NumChildren=" & lstMbrs.Count)
      End If
    
      Return lstMbrs(iRequestedPosition-1).Name
    End If