Forum Discussion

New Contributor II

2 years ago

Report Books - If Statement

Hi There, I am trying to create a book with a loop on our UD4 (project). I have it working but I want to add an IF statement to filter out tabs that contain no data. Referencing a previous post, I ...

Extensible Documents

Reports

db_pdx

Valued Contributor

2 years ago

Ha! An old post coming back to haunt me.

The way we do this now is through the use of a custom member list function that pulls a databuffer (which will only return where records exist). It's setup to work for Account and UD1-UD8. I know this method works since we actively use it in Report Books, although it can be deployed in CV's or QV's.

The custom function is below. Note, you'll need to set your sane defaults for the dimensions. I recommend you test how it works in a very basic QV before using it in your report book. When you're ready to use it in your report book, your loop type is Member Filter.

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
'************************************************************************************************************
'Reference Code:   XFR_MemberList_ByDataBuffer
'Description:      Member List Expansions that use a Data Buffer as a method to determine result lists
'Usage:            Cube View Reports / Report Books as a custom member list
'
'MemberListHasData Function
'    Descriptions:      Generates a list of members that ~have data~. Useful for limiting expansions in CV/QV ~AND~ BOOKS (key!)
'	 Works with:		Account, UD1, UD2, UD3, UD4, UD5, UD6, UD7, UD8
'	 Notes:				X#Member is used as the starting point that will be expanded based upon the provided Member Expansion
'						You only need to populate the parameter if you don't want to use the default value for that paremeter
'						Review the selectedMembers section for setting default values
'	 Report Book Notes: Recommend providing Cube, Entity, Cons, Scenario, Time as part of the Member List function to ensure consistent results
'
'    Parameter Example:
'		X#Member.CustomMemberList(BRName=XFR_MemberList_ByDataBuffer, MemberListName=MemberListHasData,
'		MemberExpansion=[Base|Children],
'		Sort=[True|False],
'		MaxNumOfMembers=[],
'		Cube=[],
'		Entity=[],
'		Cons=[],
'		Scenario=[],
'		Time=[],
'		Flow=[],
'		View=[],
'		Account=[],
'		UD1=[],
'		UD2=[],
'		UD3=[],
'		UD4=[],
'		UD5=[],
'		UD6=[],
'		UD7=[],
'		UD8=[])
'
'Created By:       db_pdx
'Date Created:     2023-11-09
'************************************************************************************************************
' CHANGELOG
' 2024-01-30 | Added UD1 and UD8 expansions to generalize for public consumption per OS-Community request.
'************************************************************************************************************
Namespace OneStream.BusinessRule.Finance.XFR_MemberList_ByDataBuffer
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
			Try
				Select Case api.FunctionType
					
					Case Is = FinanceFunctionType.MemberListHeaders
						Dim objMemberListHeaders As New List(Of MemberListHeader)
						objMemberListHeaders.Add(New MemberListHeader("MemberListHasData"))
						Return objMemberListHeaders
						
					Case Is = FinanceFunctionType.MemberList
						
						#Region "MemberListHasData"
						If args.MemberListArgs.MemberListName.XFEqualsIgnoreCase("MemberListHasData") Then
							'Return object is: (MemberListHeader, List(Of MemberInfo))
							Dim objMemberListHeader As New MemberListHeader(args.MemberListArgs.MemberListName)
							
							'Note! DUCS members will be pulled from the UserPOV if they are not provided in this MemberList Function
							Dim maxNumberOfMembers As Integer =  Integer.Parse(args.MemberListArgs.NameValuePairs.XFGetValue("MaxNumOfMembers", "0")) 'default of 0 will not apply any member limiting
							Dim sortList         As Boolean = Boolean.Parse(args.MemberListArgs.NameValuePairs.XFGetValue("Sort", "True"))
							Dim memberExpansion  As String = args.MemberListArgs.NameValuePairs.XFGetValue("MemberExpansion", "Base")
							Dim selectedCube     As String = args.MemberListArgs.NameValuePairs.XFGetValue("Cube", api.Pov.Cube.Name)
							Dim selectedEntity   As String = args.MemberListArgs.NameValuePairs.XFGetValue("Entity", api.Pov.Entity.Name)
							Dim selectedCons     As String = args.MemberListArgs.NameValuePairs.XFGetValue("Cons", api.Pov.Cons.Name)
							Dim selectedScenario As String = args.MemberListArgs.NameValuePairs.XFGetValue("Scenario", api.Pov.Scenario.Name)
							Dim selectedTime     As String = args.MemberListArgs.NameValuePairs.XFGetValue("Time", api.Pov.Time.Name)
							Dim selectedFlow     As String = args.MemberListArgs.NameValuePairs.XFGetValue("Flow", "SetYourDefaultFlow")				'SET A DEFAULT HERE
							Dim selectedView     As String = args.MemberListArgs.NameValuePairs.XFGetValue("View", "SetYourDefaultView") 				'SET A DEFAULT HERE
							Dim selectedAccount  As String = args.MemberListArgs.NameValuePairs.XFGetValue("Account", "SetYourDefaultAccount")		'SET A DEFAULT HERE
							Dim selectedUD1      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD1", "SetYourDefaultUD1")		'SET A DEFAULT HERE
							Dim selectedUD2      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD2", "SetYourDefaultUD2")		'SET A DEFAULT HERE
							Dim selectedUD3      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD3", "SetYourDefaultUD3")		'SET A DEFAULT HERE
							Dim selectedUD4      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD4", "SetYourDefaultUD4")		'SET A DEFAULT HERE
							Dim selectedUD5      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD5", "SetYourDefaultUD5")			'SET A DEFAULT HERE
							Dim selectedUD6      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD6", "SetYourDefaultUD6")			'SET A DEFAULT HERE
							Dim selectedUD7      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD7", "SetYourDefaultUD7")		'SET A DEFAULT HERE
							Dim selectedUD8      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD8", "SetYourDefaultUD8")				'SET A DEFAULT HERE
							
							'Common parameters for the DataBuffer call
							Dim fixedFilter As String = String.Empty	'stores the fixed members used in the data buffer
							Dim fullFilter  As String = String.Empty	'stores the full filtermembers() used in GetDataBufferWithFormula
							
							'Determine which member we are going to expand upon.  It should be based on the MemberDim we are dealing with
							Select Case args.MemberListArgs.DimPk.DimTypeId
								Case DimType.Account.Id
									'Override whatever was provided in Account args with the initial ("top") member of the MemberList selection
									selectedAccount = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [A#{selectedAccount}.{memberExpansion}])"
								Case DimType.UD1.Id
									'Override whatever was provided in UD1 args with the initial ("top") member of the MemberList selection
									selectedUD1 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U1#{selectedUD1}.{memberExpansion}])"
								Case DimType.UD2.Id
									'Override whatever was provided in UD2 args with the initial ("top") member of the MemberList selection
									selectedUD2 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U2#{selectedUD2}.{memberExpansion}])"
								Case DimType.UD3.Id
									'Override whatever was provided in UD3 args with the initial ("top") member of the MemberList selection
									selectedUD3 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U3#{selectedUD3}.{memberExpansion}])"
								Case DimType.UD4.Id
									'Override whatever was provided in UD4 args with the initial ("top") member of the MemberList selection
									selectedUD4 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U4#{selectedUD4}.{memberExpansion}])"
								Case DimType.UD5.Id
									'Override whatever was provided in UD5 args with the initial ("top") member of the MemberList selection
									selectedUD5 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U5#{selectedUD5}.{memberExpansion}])"
								Case DimType.UD6.Id
									'Override whatever was provided in UD6 args with the initial ("top") member of the MemberList selection
									selectedUD6 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U6#{selectedUD6}.{memberExpansion}])"
								Case DimType.UD7.Id
									'Override whatever was provided in UD7 args with the initial ("top") member of the MemberList selection
									selectedUD7 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U7#{selectedUD7}.{memberExpansion}])"
								Case DimType.UD8.Id
									'Override whatever was provided in UD8 args with the initial ("top") member of the MemberList selection
									selectedUD8 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U8#{selectedUD8}.{memberExpansion}])"
								Case Else
									Throw New Exception("The dimension used is NOT valid for this custom member list.")
							End Select
							
							'Get the Data Buffer
							Dim dataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula(fullFilter, DataApiScriptMethodType.Calculate, False)				
							
							'Create a dictionary so that we can sort the buffer
							'As well as limit the number of records returned
							'Finally return a List(Of Member) [or MemberInfo]
							Dim dataBufferDictionary As New Dictionary(Of MemberScriptBuilder, Decimal)
							Dim objMembers As New List(Of Member)
							
							If dataBuffer IsNot Nothing Then
								'Move to dictionary
								For Each dbCell As DataBufferCell In dataBuffer.DataBufferCells.Values
									dataBufferDictionary.Add(dbCell.DataBufferCellPk.CreateMemberScriptBuilder(api), dbCell.CellAmount)
								Next
								'Sort
								If sortList Then
									dataBufferDictionary = (From entry In dataBufferDictionary Order By entry.Value Descending).ToDictionary(Function(x) x.Key, Function(x) x.Value)
								End If
								'Limit
								If Not (maxNumberOfMembers = 0) Then
									dataBufferDictionary = dataBufferDictionary.Take(maxNumberOfMembers).ToDictionary(Function(x) x.Key, Function(x) x.Value)
								End If
								'Move to List of Members
								For Each key In dataBufferDictionary.Keys
									'Again, need to toggle based on what Dimension we are expecting to return
									Select Case args.MemberListArgs.DimPk.DimTypeId
										Case DimType.Account.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.Account))
										Case DimType.UD1.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD1))
										Case DimType.UD2.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD2))
										Case DimType.UD3.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD3))
										Case DimType.UD4.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD4))
										Case DimType.UD5.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD5))
										Case DimType.UD6.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD6))
										Case DimType.UD7.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD7))
										Case DimType.UD8.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD8))
									End Select 'DimType
								Next 'key
							Else
								Return Nothing
							End If 'dataBuffer is Nothing
							
							'Return the final object (MemberListHeader, List(Of Member/(Info)))
							Dim objMemberList As New MemberList(objMemberListHeader, objMembers)
							Return objMemberList
						#End Region
						End If
					
					#Region "Not used"
'					Case Is = FinanceFunctionType.DataCell
'					Case Is = FinanceFunctionType.FxRate
'					Case Is = FinanceFunctionType.Calculate
'					Case Is = FinanceFunctionType.ConditionalInput
'					Case Is = FinanceFunctionType.CustomCalculate
'					Case Is = FinanceFunctionType.ReadSourceDataRecords
					#End Region
				End Select

				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
	End Class
End Namespace

Report back if this is working for you.

Cheers, -db

anilpersaud

New Contributor II

2 years ago

Thank you for the reply!

I might need a little help - I wasn't able to get it to fully work.

First, I have the business rule setup as follows:

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
'************************************************************************************************************
'Reference Code:   XFR_MemberList_ByDataBuffer
'Description:      Member List Expansions that use a Data Buffer as a method to determine result lists
'Usage:            Cube View Reports / Report Books as a custom member list
'
'MemberListHasData Function
'    Descriptions:      Generates a list of members that ~have data~. Useful for limiting expansions in CV/QV ~AND~ BOOKS (key!)
'	 Works with:		Account, UD1, UD2, UD3, UD4, UD5, UD6, UD7, UD8
'	 Notes:				X#Member is used as the starting point that will be expanded based upon the provided Member Expansion
'						You only need to populate the parameter if you don't want to use the default value for that paremeter
'						Review the selectedMembers section for setting default values
'	 Report Book Notes: Recommend providing Cube, Entity, Cons, Scenario, Time as part of the Member List function to ensure consistent results
'
'    Parameter Example:
'		X#Member.CustomMemberList(BRName=XFR_MemberList_ByDataBuffer, MemberListName=MemberListHasData,
'		MemberExpansion=[Base|Children],
'		Sort=[True|False],
'		MaxNumOfMembers=[],
'		Cube=[],
'		Entity=[],
'		Cons=[],
'		Scenario=[],
'		Time=[],
'		Flow=[],
'		View=[],
'		Account=[],
'		UD1=[],
'		UD2=[],
'		UD3=[],
'		UD4=[],
'		UD5=[],
'		UD6=[],
'		UD7=[],
'		UD8=[])
'
'Created By:       db_pdx
'Date Created:     2023-11-09
'************************************************************************************************************
' CHANGELOG
' 2024-01-30 | Added UD1 and UD8 expansions to generalize for public consumption per OS-Community request.
'************************************************************************************************************
Namespace OneStream.BusinessRule.Finance.XFR_MemberList_ByDataBuffer
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
			Try
				Select Case api.FunctionType
					
					Case Is = FinanceFunctionType.MemberListHeaders
						Dim objMemberListHeaders As New List(Of MemberListHeader)
						objMemberListHeaders.Add(New MemberListHeader("MemberListHasData"))
						Return objMemberListHeaders
						
					Case Is = FinanceFunctionType.MemberList
						
						#Region "MemberListHasData"
						If args.MemberListArgs.MemberListName.XFEqualsIgnoreCase("MemberListHasData") Then
							'Return object is: (MemberListHeader, List(Of MemberInfo))
							Dim objMemberListHeader As New MemberListHeader(args.MemberListArgs.MemberListName)
							
							'Note! DUCS members will be pulled from the UserPOV if they are not provided in this MemberList Function
							Dim maxNumberOfMembers As Integer =  Integer.Parse(args.MemberListArgs.NameValuePairs.XFGetValue("MaxNumOfMembers", "0")) 'default of 0 will not apply any member limiting
							Dim sortList         As Boolean = Boolean.Parse(args.MemberListArgs.NameValuePairs.XFGetValue("Sort", "True"))
							Dim memberExpansion  As String = args.MemberListArgs.NameValuePairs.XFGetValue("MemberExpansion", "Base")
							Dim selectedCube     As String = args.MemberListArgs.NameValuePairs.XFGetValue("Cube", api.Pov.Cube.Name)
							Dim selectedEntity   As String = args.MemberListArgs.NameValuePairs.XFGetValue("Entity", api.Pov.Entity.Name)
							Dim selectedCons     As String = args.MemberListArgs.NameValuePairs.XFGetValue("Cons", api.Pov.Cons.Name)
							Dim selectedScenario As String = args.MemberListArgs.NameValuePairs.XFGetValue("Scenario", api.Pov.Scenario.Name)
							Dim selectedTime     As String = args.MemberListArgs.NameValuePairs.XFGetValue("Time", api.Pov.Time.Name)
							Dim selectedFlow     As String = args.MemberListArgs.NameValuePairs.XFGetValue("Flow", "EndBal")				'SET A DEFAULT HERE
							Dim selectedView     As String = args.MemberListArgs.NameValuePairs.XFGetValue("View", "Periodic") 				'SET A DEFAULT HERE
							Dim selectedAccount  As String = args.MemberListArgs.NameValuePairs.XFGetValue("Account", "NetInc")		'SET A DEFAULT HERE
							Dim selectedUD1      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD1", "Top")		'SET A DEFAULT HERE
							Dim selectedUD2      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD2", "Top")		'SET A DEFAULT HERE
							Dim selectedUD3      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD3", "Top")		'SET A DEFAULT HERE
							Dim selectedUD4      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD4", "TotProject")		'SET A DEFAULT HERE
							Dim selectedUD5      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD5", "Top")			'SET A DEFAULT HERE
							Dim selectedUD6      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD6", "Top")			'SET A DEFAULT HERE
							Dim selectedUD7      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD7", "None")		'SET A DEFAULT HERE
							Dim selectedUD8      As String = args.MemberListArgs.NameValuePairs.XFGetValue("UD8", "None")				'SET A DEFAULT HERE
							
							'Common parameters for the DataBuffer call
							Dim fixedFilter As String = String.Empty	'stores the fixed members used in the data buffer
							Dim fullFilter  As String = String.Empty	'stores the full filtermembers() used in GetDataBufferWithFormula
							
							'Determine which member we are going to expand upon.  It should be based on the MemberDim we are dealing with
							Select Case args.MemberListArgs.DimPk.DimTypeId
								Case DimType.Account.Id
									'Override whatever was provided in Account args with the initial ("top") member of the MemberList selection
									selectedAccount = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [A#{selectedAccount}.{memberExpansion}])"
								Case DimType.UD1.Id
									'Override whatever was provided in UD1 args with the initial ("top") member of the MemberList selection
									selectedUD1 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U1#{selectedUD1}.{memberExpansion}])"
								Case DimType.UD2.Id
									'Override whatever was provided in UD2 args with the initial ("top") member of the MemberList selection
									selectedUD2 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U2#{selectedUD2}.{memberExpansion}])"
								Case DimType.UD3.Id
									'Override whatever was provided in UD3 args with the initial ("top") member of the MemberList selection
									selectedUD3 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U3#{selectedUD3}.{memberExpansion}])"
								Case DimType.UD4.Id
									'Override whatever was provided in UD4 args with the initial ("top") member of the MemberList selection
									selectedUD4 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U4#{selectedUD4}.{memberExpansion}])"
								Case DimType.UD5.Id
									'Override whatever was provided in UD5 args with the initial ("top") member of the MemberList selection
									selectedUD5 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U6#{selectedUD6}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U5#{selectedUD5}.{memberExpansion}])"
								Case DimType.UD6.Id
									'Override whatever was provided in UD6 args with the initial ("top") member of the MemberList selection
									selectedUD6 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U7#{selectedUD7}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U6#{selectedUD6}.{memberExpansion}])"
								Case DimType.UD7.Id
									'Override whatever was provided in UD7 args with the initial ("top") member of the MemberList selection
									selectedUD7 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U8#{selectedUD8}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U7#{selectedUD7}.{memberExpansion}])"
								Case DimType.UD8.Id
									'Override whatever was provided in UD8 args with the initial ("top") member of the MemberList selection
									selectedUD8 = args.MemberListArgs.TopMember.Name
									'Create our DataBuffer filter
									fixedFilter = $"Cb#{selectedCube}:E#{selectedEntity}:C#{selectedCons}:S#{selectedScenario}:T#{selectedTime}:V#{selectedView}:F#{selectedFlow}:O#Top:I#Top:A#{selectedAccount}:U1#{selectedUD1}:U2#{selectedUD2}:U3#{selectedUD3}:U4#{selectedUD4}:U5#{selectedUD5}:U6#{selectedUD6}:U7#{selectedUD7}"
									fullFilter  = $"FilterMembers(RemoveZeros({fixedFilter}), [U8#{selectedUD8}.{memberExpansion}])"
								Case Else
									Throw New Exception("The dimension used is NOT valid for this custom member list.")
							End Select
							
							'Get the Data Buffer
							Dim dataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula(fullFilter, DataApiScriptMethodType.Calculate, False)				
							
							'Create a dictionary so that we can sort the buffer
							'As well as limit the number of records returned
							'Finally return a List(Of Member) [or MemberInfo]
							Dim dataBufferDictionary As New Dictionary(Of MemberScriptBuilder, Decimal)
							Dim objMembers As New List(Of Member)
							
							If dataBuffer IsNot Nothing Then
								'Move to dictionary
								For Each dbCell As DataBufferCell In dataBuffer.DataBufferCells.Values
									dataBufferDictionary.Add(dbCell.DataBufferCellPk.CreateMemberScriptBuilder(api), dbCell.CellAmount)
								Next
								'Sort
								If sortList Then
									dataBufferDictionary = (From entry In dataBufferDictionary Order By entry.Value Descending).ToDictionary(Function(x) x.Key, Function(x) x.Value)
								End If
								'Limit
								If Not (maxNumberOfMembers = 0) Then
									dataBufferDictionary = dataBufferDictionary.Take(maxNumberOfMembers).ToDictionary(Function(x) x.Key, Function(x) x.Value)
								End If
								'Move to List of Members
								For Each key In dataBufferDictionary.Keys
									'Again, need to toggle based on what Dimension we are expecting to return
									Select Case args.MemberListArgs.DimPk.DimTypeId
										Case DimType.Account.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.Account))
										Case DimType.UD1.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD1))
										Case DimType.UD2.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD2))
										Case DimType.UD3.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD3))
										Case DimType.UD4.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD4))
										Case DimType.UD5.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD5))
										Case DimType.UD6.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD6))
										Case DimType.UD7.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD7))
										Case DimType.UD8.Id
											objMembers.Add(api.Members.GetMember(args.MemberListArgs.DimPk.DimTypeId, key.UD8))
									End Select 'DimType
								Next 'key
							Else
								Return Nothing
							End If 'dataBuffer is Nothing
							
							'Return the final object (MemberListHeader, List(Of Member/(Info)))
							Dim objMemberList As New MemberList(objMemberListHeader, objMembers)
							Return objMemberList
						#End Region
						End If
					
					#Region "Not used"
'					Case Is = FinanceFunctionType.DataCell
'					Case Is = FinanceFunctionType.FxRate
'					Case Is = FinanceFunctionType.Calculate
'					Case Is = FinanceFunctionType.ConditionalInput
'					Case Is = FinanceFunctionType.CustomCalculate
'					Case Is = FinanceFunctionType.ReadSourceDataRecords
					#End Region
				End Select

				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
	End Class
End Namespace

I added our default UDs. From Line 101, I was confused on what I needed to put there.

In addition, I'm not sure if I'm using the parameter string correctly. Here is one that I was testing in one of my Cube Views for UD4:

X#Member.CustomMemberList(BRName=XFR_MemberList_ByDataBuffer, MemberListName=MemberListHasData, MemberExpansion=[Base], Sort=[True|False], MaxNumOfMembers=[20], Cube=[00_CorpRpt], Entity=[|CVEntity|], Cons=[Local], Scenario=[Budget_V2], Time=[2024], UD4=[TotProject])

Any help would be appreciated. Thank you!

db_pdx
Valued Contributor
2 years ago
Hi anilpersaud: you're really close, just a few things to change and we can hopefully get this working for you.

Regarding the changes to the BR:

You only needed to modify the sections that indicated 'SET A DEFAULT HERE' which it looks like you've set correctly.

Modifications to make it work in your CV. For your member filter, try:

U4#TotProject.CustomMemberList(BRName=XFR_MemberList_ByDataBuffer, MemberListName=MemberListHasData, MemberExpansion=[Base], Sort=[True], MaxNumOfMembers=[20])

A few notes on the changes I made above:

The X#Member is meant to represent the dimension you are pulling and the initial starting member to expand from, in this case U4#TotProject

Sort, you'll want to pick either 'True' or 'False'

The rest of the dimensions can be removed when used in the CubeView as they will simply pull from what is defined in your Cube View POV. However, you can also retain them if you'd like the member list to generate from the unique dimensions you've referenced rather than following the Cube View POV.

General reminder, the rest of the dimensions will follow those defaults you've set, but can also be overridden as needed.

When you go to use this in a Report Book:

The Report Book does not have the concept of the Cube View POV, hence you will need to include those Data Unit dimensions in the syntax of the CustomMemberList

Usage in the Report Book Loop Member Filter will look like:

U4#TotProject.CustomMemberList(BRName=XFR_MemberList_ByDataBuffer, MemberListName=MemberListHasData, MemberExpansion=[Base], Sort=[True], MaxNumOfMembers=[20], Cube=[00_CorpRpt], Entity=[RealEntityHere-or-|!UseAnEntityParameter!|], Cons=[Local], Scenario=[Budget_V2], Time=[2024])

Let me know if that gets it going for you.

Cheers, -db
- jwagner
  New Contributor III
  11 months ago
  I have set up my defaults and cube view member filter in a simple cube view, but the Finance rule seems to not be returning anything from the data buffer. How could I debug this?

Forum Discussion

Report Books - If Statement

Related Content

OneStream Community Accessibility Statement

Book if statement

QuickTips: Reverse Engineering a Report Book Part 1

QuickTips: Reverse Engineering a Report Book Part 2

Reporting - Books - File (xfDoc)