12-13-2021 12:04 PM - last edited on 05-24-2023 12:16 PM by JackLacava
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
Solved! Go to Solution.
12-15-2021 08:41 AM - edited 12-15-2021 09:29 AM
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
12-13-2021 01:27 PM
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:
12-13-2021 05:09 PM
This produced same results, but I think it can work together with other suggestion, will give it a try. Thanks for your comment.
12-13-2021 05:37 PM
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.
12-13-2021 04:49 PM
You can create dataset against WFProfileEntities and set the values order in it.
12-13-2021 05:08 PM
Thanks for your input, will try to work around it
12-14-2021 04:07 AM
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
12-14-2021 09:22 AM
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.
12-14-2021 09:36 AM
It does work in a parameter
12-14-2021 09:38 AM
Aha, I forgot that trick is there for Member select. Good one.
12-15-2021 08:41 AM - edited 12-15-2021 09:29 AM
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
12-15-2021 09:29 AM
We ended up resolving it with a variation of this solution. Thank you.