OneStream Employee

How can I use a Business Rule to sort a Member List in alphabetical order?

Forum|Forum|4 years ago
October 21, 2021
11 replies
0 views

Namespace OneStream.BusinessRule.Finance.XFR_MemberListAlphabetical
	Public Class MainClass
		'---------------------------------------------------------------------------------------------------
		'Reference Code: 	XFR_MemberListAlphabetical
		'
		'Description:		Use a business rule to sort a member list in Alphabetical order
		'
		'Usage:				This will put a member list of a dimension in Alphabetical order. 
		'					Use the following on the cube view:
		'                      E#Member.[Name of Business Rule, Name of List in Business Rule]
		'					e.g. E#Root.[XFR_MemberListAlphabetical, EntityAlphabetical]
		'
		'Created By:		Robert Powers (put in XF Ref by John Von Allmen)
		'
		'Date Created:		5-24-2013
		'---------------------------------------------------------------------------------------------------	
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, _
		                     ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
			Try

				'This will put a member list of a dimension in Alphabetical order. 
				'Use the following on the cube view:
				'        E#Member.[Name of Business Rule, Name of List in Business Rule]
				'     e.g. E#Root.[XFR_MemberListAlphabetical, EntityAlphabetical]								
				Dim Memberlistname As String = "Ent_Sort"
				Dim MemberListstart As String = "E#[Total GolfStream].base"
			
				Select Case api.FunctionType      
					Case Is = FinanceFunctionType.MemberList
			            If args.MemberListArgs.MemberListName = Memberlistname Then
		                    Dim objMemberListHeader = New MemberListHeader( _
															args.MemberListArgs.MemberListName)
		                    
		                    'Read the members
		                    Dim objMemberInfos As List(Of MemberInfo) = api.Members.GetMembersUsingFilter( _
                            							args.MemberListArgs.DimPk, MemberListstart, Nothing)

		                    'Sort the members
		                    Dim objMembers As List(Of Member) = Nothing
		                    If Not objMemberInfos Is Nothing Then
		                    	objMembers = (From memberInfo In objMemberInfos _
								              Order By memberInfo.Member.Name Ascending _
											  Select memberInfo.Member).ToList()
		                    End If
		                    
		                    'Return
		                    Return New MemberList(objMemberListHeader, objMembers)
			            End If
				End Select
				Return Nothing
				
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
	End Class
End Namespace

Finance

Show previous replies

RobbSalzmann

Legend

The sorting posted so far relies on standard lexical ordering which produces an order that can be a bit strange for a business user:

This code will help sort things in a human, alphanumeric order:

Public Class MainClass
  Public Function Main(si As SessionInfo, globals As BRGlobals, api As Object, args As ExtenderArgs) As Object
    Try
      Dim mbrLst As New MemberList()
      Dim mbrDisplayOptions As MemberDisplayOptions = New MemberDisplayOptions() _
         With{ .IncludeAllProperties = True, .IncludeMemberInfoForProperties = True}
      Dim lstMembers As LIst(Of MemberInfo) = 
         BRApi.Finance.Metadata.GetMembersUsingFilter(si, "CorpAccounts", "A#[GAAP Account Structure].Base", True, Nothing, mbrDisplayOptions)
      lstMembers.Sort(New AlphaNumericMemberInfoComparer())
      mbrLst.AddMemberInfosToList(lstMembers)
      Return mbrLst
    Catch ex As Exception
      Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
    End Try
  End Function   
End Class

Public Class AlphaNumericMemberInfoComparer
   Implements IComparer(Of MemberInfo)

    Public Function Compare(x As MemberInfo, y As MemberInfo) As Integer Implements IComparer(Of MemberInfo).Compare
        If x.Member.Name = y.Member.Name Then Return 0

        Dim xParts() As String = Regex.Split(x.Member.Name, "(\d+)")
        Dim yParts() As String = Regex.Split(y.Member.Name, "(\d+)")

        For i As Integer = 0 To Math.Min(xParts.Length, yParts.Length) - 1
            If xParts(i) <> yParts(i) Then
                Return PartCompare(xParts(i), yParts(i))
            End If
        Next

        Return xParts.Length.CompareTo(yParts.Length)
    End Function

    Private Shared Function PartCompare(x As String, y As String) As Integer
        Dim a, b As Integer
        Dim isNumericX As Boolean = Integer.TryParse(x, a)
        Dim isNumericY As Boolean = Integer.TryParse(y, b)

        If isNumericX And isNumericY Then
            Return a.CompareTo(b)
        End If
        Return String.Compare(x, y, StringComparison.OrdinalIgnoreCase)
    End Function
End Class

Alternate Sorting Result:

J

JackLacava

OneStream Employee

That's neat. It could be further generalized by accepting a parameterized switch (SortOrder=<human/lcg>) and another one to switch between ordering by name or description (SortBy=<name/desc>).

RobbSalzmann

Legend

Or, if you want something quick and LINQ-y, these will work to sort lists of MemberInfo:

''VB.net
Public Sub AlphaNumericSort(ByRef members as List(Of MemberInfo)
	Dim sortedMembers As List(Of MemberInfo) = members _
	    .OrderBy(Function(memberInfo) _
		If(Regex.Match(memberInfo.Member.Name, "\d+").Success, _                    'Evaluation, starts with a number?
			Integer.Parse(Regex.Match(memberInfo.Member.Name, "\d+").Value), _  'True Part
			Integer.MaxValue)) _                                                'False Part,  put after all that start with a number
	    .ToList()
End Sub	    
   
// C#:
public void AlphaNumericSort(ref List members)
{
	List sortedMembers = members
	    .OrderBy
	    (
		memberInfo => Regex.Match(memberInfo.Member.Name, @"\d+").Success ? //Evaluation, starts with a number?
		    int.Parse(Regex.Match(memberInfo.Member.Name, @"\d+").Value) :  //True
		    int.MaxValue                                                    //False, put after all that start with a number
	    ).ToList();
}

Used like this:

Public Class MainClass
  Public Function Main(si As SessionInfo, globals As BRGlobals, api As Object, args As ExtenderArgs) As Object
    Try
      Dim mbrLst As New MemberList()
      Dim mbrDisplayOptions As MemberDisplayOptions = New MemberDisplayOptions() _
         With{ .IncludeAllProperties = True, .IncludeMemberInfoForProperties = True}
      Dim lstMembers As List(Of MemberInfo) = 
         BRApi.Finance.Metadata.GetMembersUsingFilter(si, "CorpAccounts", "A#[GAAP Account Structure].Base", True, Nothing, mbrDisplayOptions)

      '' Sort the members:
      AlphaNumericSort(lstMembers)

      mbrLst.AddMemberInfosToList(lstMembers)
      Return mbrLst
    Catch ex As Exception
      Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
    End Try
  End Function

	Public Sub AlphaNumericSort(ByRef members as List(Of MemberInfo)
		Dim sortedMembers As List(Of MemberInfo) = members _
		    .OrderBy(Function(memberInfo) _
			If(Regex.Match(memberInfo.Member.Name, "\d+").Success, _                    'Evaluation, starts with a number?
				Integer.Parse(Regex.Match(memberInfo.Member.Name, "\d+").Value), _  'True Part
				Integer.MaxValue)) _                                                'False Part,  put after all that start with a number
		    .ToList()
	End Sub       
End Class

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded