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

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:

A Cube view example of this also available in the GolfStream app, in case you wanted to see an illustration.

Keep in mind that a parameter must reference a Dashboard Data Set BR. Of course, the Dashboard Data Set BR can reference a Finance BR, but you will have to convert the member list to a data table before returning it to the parameter. Here's an example of a Dashboard Data Set BR that is calling a function in a Finance BR. Note that the Finance BR returns a List (of Member) that is then converted into a data table at the bottom section of the rule (starting at the 'Create the data table to return to the parameter section')

		Private Function Get_Non_Zero_Members(ByVal si As SessionInfo, ByVal api As Object, ByVal args As DashboardDataSetArgs) As DataTable 	
			Try
				'Example of how to call this from a parameter: 
				'{ADU_HelperQueries}{Get_Non_Zero_Members}{Member_Filter=E#Tot_USG.Base, Value_Filter=A#USG_PPE, Sort_By=Name, Sort_Order= Ascending}
				
				'Get Workflow Scenario ID
				Dim wfScenarioName As String = ScenarioDimHelper.GetNameFromID(si, si.WorkflowClusterPk.ScenarioKey)
				Dim wfScenarioId As String = ScenarioDimHelper.GetIdFromName(si, wfScenarioName).ToString
				'Get Time from current Workflow
				Dim myWorkflowUnitPk As WorkflowUnitPk = BRApi.Workflow.General.GetWorkflowUnitPk(si)
				Dim wfTime As String = BRApi.Finance.Time.GetNameFromId(si, myWorkflowUnitPk.TimeKey)
						
				Dim memberFilter As String = args.NameValuePairs.XFGetValue("Member Filter", String.Empty)					
				Dim valueFilter As String = "CB#NVL_Consol:C#Local:S#" & wfScenarioName & ":T#" & wfTime & ":V#YTD:F#End_Bal:O#BeforeAdj:I#Top:U1#Tot_USG_Data_Types:U2#Tot_Departments:U3#Tot_Product_Groups:U4#None:U5#None:U6#None:U7#None:U8#None:"
				valueFilter = valueFilter & args.NameValuePairs.XFGetValue("Value Filter", String.Empty)					
				Dim sortBy As String = args.NameValuePairs.XFGetValue("Sort By", String.Empty)					
				Dim sortOrder As String = args.NameValuePairs.XFGetValue("Sort Order", String.Empty)			
				Dim rank As String = args.NameValuePairs.XFGetValue("Rank", "None")										
				Dim memListHeader As New MemberListHeader(args.DataSetName)
				'********* debug info *********	
				If debugSwitch = True Then brapi.ErrorLog.LogMessage(si,"ADU_HelperQueries.Get_Non_Zero_Members" & vbCrLf & 
																	"args.DataSetName: " & args.DataSetName & vbCrLf &
																	"memberFilter: " & memberFilter & vbCrLf &
																	"valueFilter: " & valueFilter & vbCrLf &
																	"sortBy: " & sortBy & vbCrLf &
																	"sortOrder: " & sortOrder & vbCrLf &
																	"rank: " & rank)		
				'****************************** 				
				'Create lists for ranking
'				Dim membersAndValues As New List(Of MemberAndCellValue)
				Dim memberList As New List(Of Member)
				Dim memberInfoList As New List(Of MemberInfo)
'				Dim cubeName As String = New MemberScriptBuilder(valueFilter).Cube
				
				'Define the Advanced Developer Utility rule
				Dim fsArgs As New FinanceRulesArgs
				Dim ADU As New OneStream.BusinessRule.Finance.ADU_Advanced_Developer_Utility.MainClass		
				'Get the list of members from the Finance BR
				Dim topNMems As List(Of Member) = ADU.Get_Non_Zero_Members(si, api, fsArgs, memberFilter, valueFilter, SortBy, sortOrder, Rank)
				'Dim topNMemList As New MemberList(memListHeader, topNMems) '<<< This works in the Finance BR, but must be converted into a data table in the Dashboard Data Set BR
				
				'Create the data table to return to the parameter
				Dim dt As New DataTable("topNMemsTbl")
			
				Dim nameCol = New DataColumn
    	        nameCol.ColumnName = "Name"
        	    nameCol.DataType = GetType(String)
            	nameCol.DefaultValue = ""
            	nameCol.AllowDBNull = False
            	dt.Columns.Add(nameCol)

				Dim descriptionCol = New DataColumn
    	        descriptionCol.ColumnName = "Description"
        	    descriptionCol.DataType = GetType(String)
            	descriptionCol.DefaultValue = ""
            	descriptionCol.AllowDBNull = False
            	dt.Columns.Add(descriptionCol)
				
				'loop through the items in topNMems and add them to the dt table
				For Each nMem In topNMems
					'Create a new row and append it to the table
					Dim row As DataRow = dt.NewRow()
					row("Name") = nMem.Name
					row("Description") = nMem.Description
				
            		dt.Rows.Add(row)
				Next nMem
				
				Return dt				
								
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try			
		End Function

Is there a way to use this to sort a parameter? We want to sort entities in alpha/numerical order, but because we start all entities with E it doesn't appear to work. It would also be nice if we can use in the parameter instead of having to individually add to each cube view. Thanks!

Hi Jerry,

Not sure if you had a response on this, but what you could do is by creating the parameter as a 'Member list' and in your Member filter reference a business rule which sorts your entities. I have attached a screenshot of an example which you could use, you can change the entity filter variable to suit your requirements. 

Regards,

Naeem

What if the members are numerical and I want to sort them in numerical order?

Tried this and it works!! Question though, what if I want to be able to sort on different hierarchies within the same dimension.  Does it mean I have to create separate BRs for each of them or can I incorporate them into one BR?  Thanks in advance

Hi!  If I want to sort on the default description do I just change it from Memberlistname to Memberdefaultdescription?

The code is configured to sort by member name or description. The parameter sortBy controls which one to use. That parameter is being populated by the name value pair "Sort By".

Any particular reason we might think of, that prevents the display in alphabetical order from XFBR (not Finance) business rule? Members' name format is XXX1111. Essentially I use logic similar to the OP, but the alphanumeric values returned are not listed in alphabetical order as prescribed by linq select, but in rather random order.