Member Filter Builder - Member Expansion Functions

[JasmineH]

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]

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