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

Community Manager
Community Manager



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

				'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( _
		                    '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 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




New Contributor II

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



New Contributor

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, you can call the above as part of your member list in the parameter, but in your case, you would need to adapt the code to not look at the name as a whole, but to extract out the "E" so it's sorting on the rest. One other note to add to the code above... often in parameters, you're using descriptions instead of member names. These often aren't the same alphabetically. I create two of these rules, one for name and one looking at description, depending on where/how I'm using it.

New Contributor II

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. 


Business rule - Alphabetical order entities.png






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

				Dim descriptionCol = New DataColumn
    	        descriptionCol.ColumnName = "Description"
        	    descriptionCol.DataType = GetType(String)
            	descriptionCol.DefaultValue = ""
            	descriptionCol.AllowDBNull = False
				'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
				Next nMem
				Return dt				
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try			
		End Function


New Contributor III

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

Same methodology should work there. Alphabetical looks at both alphanumeric and numeric characters. If you have a blend, the numeric will come first. And what's nice about the code from Gidon, is that you can sort by either name or description.

Thanks Michel!  Will this work for quick views too? 

Excellent question. I've never tried to use an xfbr in a quick view. I'd be curious to hear if it does.

New Contributor III

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

You should be able to pass any dimension/hierarchy as a parameter to the rule to sort anything you want.

Thanks will give it a shot!  Btw, the BR does work in a quick view.  I tried it. 

HI again Michel!

Can I use the same BR to sort on a calculated column?  If so, how do I accomplish that?  Thanks in advance. 

I would imagine so. I assume you want to create like a top ten or sort by value? It would require a bit more work, because essentially you would need to poll every account and get the intersectional cell amount and store the values in memory and return the dimension members based off that data.


New Contributor III

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

Yes. that should work.

Hi Michel!  


I changed this to Memberdefaultdescription but it's giving me an error about it not being included in MemberListArgs.  Any ideas on what I can try?  Thanks

Sorry. Memberlistname is just the name of the list you're creating. What you'd want to do is change the .Name in the below part of the code to .Description...


 If Not objMemberInfos Is Nothing Then
		                    	objMembers = (From memberInfo In objMemberInfos Order By memberInfo.Member.Name Ascending Select memberInfo.Member).ToList()
		                    End If


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.

Hi Koemets,


I can't think of a particular reason off-hand why it wouldn't return the correct order, but if you want to post your code, I'd be happy to look and see if anything jumps out




Dim objMembers As List(Of Member) = Nothing
If Not masterCL Is Nothing Then
       objMembers = (From memberInfo In masterCL Order By memberInfo.Member.Name Ascending Select memberInfo.Member).ToList()
End If

I've never tried it in an XFBR before, but it should be doable. However, the methodology might have to be changed. I think you might have to cycle through the alphabetical list you created in this and add it to a text string to return the order back to the cube view (result being like A#XXX111, A#XXX112, etc). If I get a chance, I'll play around with it and see if I can get it to work.

I thought about stripping the leading letters (they are all the same) and sort it by resulting integer.


Thank you. 

New Contributor II

Hello, do we have an example where this is used in account dimension? I tried it but it would only show the parent account. For example, I use A#ParentAccount.Base as the Memberliststart, the cube view will only show the ParentAccount and not the base members.

I then tried to switch the dimension to Entity in the cube view and also the Memberliststart to E#TopEntity.Base, and it works just fine so I'm wondering if this only works for Entity dimension.

Hi All,

I was able to make it work for the Account Dimension by using the Finance Metadata function called GetMembersUsingFilter with this syntax:
BRApi.Finance.Metadata.GetMembersUsingFilter(si, dimensionName, memberFilter, removeDuplicates, dimDisplayOptions, memberDisplayOptions)

The above worked better than the Members function with syntax:

api.Members.GetMembersUsingFilter(args.MemberListArgs.DimPk, MemberListstart, Nothing).

Hope this helps!

Valued Contributor

Something to note is the sorting posted so far relies on framework-based lexical ordering.  This produces results that are sometimes a bit strange for a business use case:


Presented here is yet another way of solving for the original post using an approach that focuses on sorting flexibility options. 

The code below demonstrates how to use the efficient Array.Sort and a little polymorphism to sort alphanumerically in a way that may better suit a business use case.  If different type of sorting is needed, its a simple matter of implementing the desired logic in the function Compare :

Public Class MainClass
  Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
      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 AlphaNumericMemberInfoComparator())
      Return mbrLst
    Catch ex As Exception
      Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
    End Try
  End Function   
End Class

Public Class AlphaNumericMemberInfoComparator
   Implements IComparer(Of MemberInfo)

   Public Function Compare(ByVal x As MemberInfo, ByVal y As MemberInfo) As Integer Implements IComparer(Of MemberInfo).Compare
      Dim regex As New Regex("([0-9]+)")
      Dim replacedX As String = regex.Replace(x.Member.Name, Function(match) match.Value.PadLeft(10, "0"c))
      Dim replacedY As String = regex.Replace(y.Member.Name, Function(match) match.Value.PadLeft(10, "0"c))

      Return String.Compare(replacedX, replacedY)
   End Function
End Class

Alternate Sorting Result: