3 weeks ago - last edited 3 weeks ago
Hello OneStreamers,
I'm looking out for a potential solution to create a custom Entity Drop Down that displays Text2 property value inside the dropdown.
Eg:
Entity Name | Text 2 Property |
EntA | ABC |
EntB | DEF |
EntC | GHI |
Now, when I fetch the Entity List, the dropdown should show like:
ABC
DEF
GHI
And NOT the entity names.
Do we have a workaround to achieve this?
Thank you!
@JackLacava @OSAdmin
Solved! Go to Solution.
3 weeks ago
The best way to do this would be with a Dashboard Data Set Business Rule. This rule type allows you to return custom data sets that can be attached to a parameter. Below is the rule and set up:
Here is the rule used:
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 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.DashboardDataSet.Test_DataSet
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.GetDataSetNames
Dim names As New List(Of String)()
names.Add("EntityText2")
Return names
Case Is = DashboardDataSetFunctionType.GetDataSet
If args.DataSetName.XFEqualsIgnoreCase("EntityText2") Then
Dim memberFilter As String = "E#[All Orgs].Base.Where(Text2 = Mfg)"
Dim memberInfos As List(Of MemberInfo) = BRApi.Finance.Metadata.GetMembersUsingFilter(si,"CorpEntities",memberfilter,False,Nothing,Nothing)
Dim dt As DataTable = Me.CreateDataTable(si)
If Not memberInfos.Count = 0 Then
For Each memberInfo As MemberInfo In memberInfos
Me.WriteRow(si, dt, memberInfo.Member)
Next
Else
Return Nothing
End If
Return dt
End If
End Select
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Private Function CreateDataTable(ByVal si As SessionInfo) As DataTable
Try
'Create the data table to return
Dim dt As New DataTable("DisplayMembers")
Dim objCol = New DataColumn
objCol.ColumnName = "Name"
objCol.DataType = GetType(String)
objCol.DefaultValue = ""
objCol.AllowDBNull = False
dt.Columns.Add(objCol)
objCol = New DataColumn
objCol.ColumnName = "Value"
objCol.DataType = GetType(String)
objCol.DefaultValue = ""
objCol.AllowDBNull = False
dt.Columns.Add(objCol)
Return dt
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Private Sub WriteRow(ByVal si As SessionInfo, ByVal dt As DataTable, ByVal member As Member)
Try
'Create a new row and append it to the table
Dim row As DataRow = dt.NewRow()
Dim entityTextField As String = BRApi.Finance.Entity.Text(si, member.MemberId, 2, Nothing, Nothing)
row("Name") = member.Name
row("Value") = entityTextField
dt.Rows.Add(row)
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Sub
End Class
End Namespace
Note that line 35 will need to be modified to fit your use case.
Hope this helps!
3 weeks ago
The best way to do this would be with a Dashboard Data Set Business Rule. This rule type allows you to return custom data sets that can be attached to a parameter. Below is the rule and set up:
Here is the rule used:
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 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.DashboardDataSet.Test_DataSet
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.GetDataSetNames
Dim names As New List(Of String)()
names.Add("EntityText2")
Return names
Case Is = DashboardDataSetFunctionType.GetDataSet
If args.DataSetName.XFEqualsIgnoreCase("EntityText2") Then
Dim memberFilter As String = "E#[All Orgs].Base.Where(Text2 = Mfg)"
Dim memberInfos As List(Of MemberInfo) = BRApi.Finance.Metadata.GetMembersUsingFilter(si,"CorpEntities",memberfilter,False,Nothing,Nothing)
Dim dt As DataTable = Me.CreateDataTable(si)
If Not memberInfos.Count = 0 Then
For Each memberInfo As MemberInfo In memberInfos
Me.WriteRow(si, dt, memberInfo.Member)
Next
Else
Return Nothing
End If
Return dt
End If
End Select
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Private Function CreateDataTable(ByVal si As SessionInfo) As DataTable
Try
'Create the data table to return
Dim dt As New DataTable("DisplayMembers")
Dim objCol = New DataColumn
objCol.ColumnName = "Name"
objCol.DataType = GetType(String)
objCol.DefaultValue = ""
objCol.AllowDBNull = False
dt.Columns.Add(objCol)
objCol = New DataColumn
objCol.ColumnName = "Value"
objCol.DataType = GetType(String)
objCol.DefaultValue = ""
objCol.AllowDBNull = False
dt.Columns.Add(objCol)
Return dt
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Private Sub WriteRow(ByVal si As SessionInfo, ByVal dt As DataTable, ByVal member As Member)
Try
'Create a new row and append it to the table
Dim row As DataRow = dt.NewRow()
Dim entityTextField As String = BRApi.Finance.Entity.Text(si, member.MemberId, 2, Nothing, Nothing)
row("Name") = member.Name
row("Value") = entityTextField
dt.Rows.Add(row)
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Sub
End Class
End Namespace
Note that line 35 will need to be modified to fit your use case.
Hope this helps!
3 weeks ago
Also should note that is you want to show the Value and not the Name you can reference the parameter with double exclamation marks - |!!ParameterName!!|
3 weeks ago - last edited 3 weeks ago
Thanks! for the quick turnaround @TheJonG . Although, when I used the double exclamation marks, it still shows the Name column and not the Value column. Am I missing something?
2 weeks ago
Ok I think I had it reversed then - !! gives the Value Member and ! gives the display member. So you only need the single !.