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