WFProfileEntities Dropdown Menu Order

Hi everyone,

I have a cube view that uses an entity parameter with member filter E#Root.WFProfileEntities for users to select their respective workflow entities when opening the cube view. However, the list of entities depending on the workflow chosen is quite large and the order of entities in the dropdown seems to be random, when I was hoping the order would mirror the entity assignment order in the workflow section. Would anyone have any suggestions on how to organize entity dropdown list as per workflow entity assignment?

Thank you,

Royce

Parameters

NickKroppe

4 years ago

Hi,

This question is answered in an existing KB article that recently expired and is in the final stages of being re-published. Here is a possible solution pulled from the KB article that uses a dashboard dataset rule to execute the WF method query and sort the results. This concept can be applied any time you want to sort the results of a OneStream method query or really any data table for that matter. Lastly, this same topic is covered in the OneStream Foundation Handbook in the rules chapter and can be found on page 230.

Namespace OneStream.BusinessRule.DashboardDataSet.WFAssignedEntityQuery
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As Object
			Try
				Select Case args.FunctionType					
					Case Is = DashboardDataSetFunctionType.GetDataSet
						If args.DataSetName.XFEqualsIgnoreCase("SortedWFEntities") Then
							'Open DB connection and execute the method command
							Dim ds As DataSet = Nothing
							Using dbConnApp As DbConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)				
								'Execute the Method Command
								ds = BRAPi.Database.ExecuteMethodCommand(dbConnApp, XFCommandMethodTypeId.WorkflowProfileEntities,"","WF", Nothing)
							End Using					
							'massage the data table
							If (Not ds Is Nothing) Then
								If ds.Tables.Count > 0 Then
									'Get the data table
									Dim dt As DataTable = ds.Tables(0).Copy()
									'Create new data view to sort the results by a given field in the table
									Dim dataView As New DataView(dt)
									'sort by the name field in an ascending manner
									dataView.Sort = "Name Asc"
									Dim sortedDT As DataTable = DataView.ToTable()
									sortedDT.TableName = "SortedAssignedEntities"
									Return sortedDT
								Else
									BRAPi.ErrorLog.LogMessage(si, "There are no entities assigned to the workflow")
								End If
							End If		
						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

Nick Kroppe

OneStream Software

NickKroppe

Contributor

4 years ago

Hi,

Namespace OneStream.BusinessRule.DashboardDataSet.WFAssignedEntityQuery
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As Object
			Try
				Select Case args.FunctionType					
					Case Is = DashboardDataSetFunctionType.GetDataSet
						If args.DataSetName.XFEqualsIgnoreCase("SortedWFEntities") Then
							'Open DB connection and execute the method command
							Dim ds As DataSet = Nothing
							Using dbConnApp As DbConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)				
								'Execute the Method Command
								ds = BRAPi.Database.ExecuteMethodCommand(dbConnApp, XFCommandMethodTypeId.WorkflowProfileEntities,"","WF", Nothing)
							End Using					
							'massage the data table
							If (Not ds Is Nothing) Then
								If ds.Tables.Count > 0 Then
									'Get the data table
									Dim dt As DataTable = ds.Tables(0).Copy()
									'Create new data view to sort the results by a given field in the table
									Dim dataView As New DataView(dt)
									'sort by the name field in an ascending manner
									dataView.Sort = "Name Asc"
									Dim sortedDT As DataTable = DataView.ToTable()
									sortedDT.TableName = "SortedAssignedEntities"
									Return sortedDT
								Else
									BRAPi.ErrorLog.LogMessage(si, "There are no entities assigned to the workflow")
								End If
							End If		
						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

Nick Kroppe

OneStream Software

royceBPSC
New Contributor II
4 years ago
We ended up resolving it with a variation of this solution. Thank you.

ajslack
New Contributor II
4 years ago
You could create a Bound List parameter with Method command type similar to what's used in the GolfStream app parameter ParamWFProfileEntities. See screenshot below:
- royceBPSC
  New Contributor II
  4 years ago
  This produced same results, but I think it can work together with other suggestion, will give it a try. Thanks for your comment.
  - ckattookaran
    VIP
    4 years ago
    If you want to order it, you'll have to use a Dashboard Data set rule and then order the resulting data table using the Name.
    
    I won't use a dataset since that will be an unnecessary step.
Koemets
Contributor
4 years ago
You can create dataset against WFProfileEntities and set the values order in it.
- royceBPSC
  New Contributor II
  4 years ago
  Thanks for your input, will try to work around it

Sai_Maganti

Contributor II

4 years ago

You may also use this custom sort in your member filter. However, there is a slight performance issue here.

E#Root.CustomMemberList(BRName =XFR_MemberListAlphabetical, MemberListName = MemberSort, MemberList = [E#Root.WFProfileEntities])

This is the Finance BR I have used from the GolfStream application (modified version)

Imports System
Imports System.Data
Imports System.Data.Common
Imports System.IO
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Linq
Imports Microsoft.VisualBasic
Imports System.Windows.Forms
Imports OneStream.Shared.Common
Imports OneStream.Shared.Wcf
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Database
Imports OneStream.Stage.Engine
Imports OneStream.Stage.Database
Imports OneStream.Finance.Engine
Imports OneStream.Finance.Database

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#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#Root.[XFR_MemberListAlphabetical, EntityAlphabetical]
				
				Dim Memberlistname As String = args.MemberListArgs.MemberListName
				Dim MemberListstart As String = args.MemberListArgs.NameValuePairs("MemberList")
				
				api.LogError(xferrorlevel.Information, MemberListstart)
				
				Select Case api.FunctionType      
					Case Is = FinanceFunctionType.MemberList
						If MemberListname.Equals("MemberSort", StringComparison.InvariantCultureIgnoreCase) 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

ckattookaran
VIP
4 years ago
That is only going to work in a CubeView, I believe OP is looking for a parameter, I don't think this will work in that case.
- Sai_Maganti
  Contributor II
  4 years ago
  It does work in a parameter

Forum Discussion

WFProfileEntities Dropdown Menu Order

Related Content

TXP_BusinessRules Execution Order

Create a dependent dropdown list in SQL Table Editor

XFSetCell order of upload

Transformation Rules - Order of Operations

BI Viewer - Bar Chart Order by Time Dimension