Create a custom drop down list (Entity) of Text 2 Property

[Bansal_P]

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 

[TheJonG · Accepted solution]

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:

• Create new Dashboard Data Set Business Rule
• Create Bound List Parameter and reference the business rule

• Parameter will return entity name and text 2

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!

[TheJonG]

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

[Bansal_P]

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?

[TheJonG]

Ok I think I had it reversed then - !! gives the Value Member and ! gives the display member. So you only need the single !.