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

N

NaeemPathan

Newcomer

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

J

Jerry

Newcomer

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!

Mike_Sabourin

Contributor

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.

N

NaeemPathan

Newcomer

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

G

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

M

marisolbritton

Newcomer

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

Mike_Sabourin

Contributor

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.

M

marisolbritton

Newcomer

Thanks Michel! Will this work for quick views too?

M

marisolbritton

Newcomer

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

Mike_Sabourin

Contributor

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

M

marisolbritton

Newcomer

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

M

marisolbritton

Newcomer

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

Mike_Sabourin

Contributor

Yes. that should work.

M

marisolbritton

Newcomer

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

G

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

K

Koemets

Newcomer

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.

Mike_Sabourin

Contributor

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

K

Koemets

Newcomer

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

A

amvillaruel

Newcomer

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.

A

amvillaruel

Newcomer

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!

Show more replies

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded