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 ...
Forum Discussion
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 NamespaceReport back if this is working for you.
Cheers, -db
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 NamespaceI 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!
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
