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

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

 

 

29 REPLIES 29

NaeemPathan
New Contributor III

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

NaeemPathan_0-1635782594158.png

 

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

NaeemPathan
New Contributor III

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

 

 

Regards,

Naeem

Gidon_Albert
Contributor

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

 

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

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

 

marisolbritton
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

Gidon_Albert
Contributor

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

Koemets
Contributor

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. 

amvillaruel
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!

RobbSalzmann
Valued Contributor

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

RobbSalzmann_3-1691271156729.png

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:

RobbSalzmann_4-1691272001435.png

 

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

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