Forum Discussion

miloszmalecki's avatar
miloszmalecki
New Contributor
11 months ago

Dynamic Member List

Hi OS Community, I am just wondering if any of you had a chance to build dynamic member list or some similar functionality which allows you to have a specified member list which will be used in a p...
Cubeview
Finance
XFBR
JackLacava's avatar
JackLacava
Honored Contributor
11 months ago

Your solution kinda works, but tbh I think you need to get acquainted with the wonderful world of Custom Member Lists. Once you understand how they work, they put you in total control. There is an introduction at the beginning of this blog post, although the post goes on to show an advanced usage; so here's a version that I believe covers your needs. It will have to be implemented in a Business Rule of type Finance; you don't need to attach it to the cube.

I added some comments that hopefully clarify what each call is doing.

Case Is = FinanceFunctionType.MemberListHeaders
	' technically this bit is optional but best practice. 
	' See the blog for explanation.
	Dim headers As New List(Of MemberListHeader)
	headers.Add(New MemberListHeader("ByEntityPrefix"))
	Return headers
	
Case Is = FinanceFunctionType.MemberList
	' Example usage: 
	'	U8#MyTopMember.CustomMemberList(BRName=MyBR, MemberListName=ByEntityPrefix, Entity=ES_123)
	If args.MemberListArgs.MemberListName.XFEqualsIgnoreCase("ByEntityPrefix") Then
		' get the dimension type we're working with
		Dim dimTypeCode As String = dimtype.GetItem(args.MemberListArgs.DimPk.DimTypeId).Abbrev
		' get the prefix from passed parameter
		Dim param As String = args.MemberListArgs.NameValuePairs.XFGetValue("Entity")
		Dim prefix As String = param.Substring(0,2).ToUpper
		' retrieve members
		' Note in the filter we use the member that "started" the list, i.e.
		' in U8#MyTop.CustomMemberList(...) it would be "MyTop".
		' Note also we use StartsWith, which should be faster than Contains
		Dim memberInfos As List(Of MemberInfo) = api.Members.GetMembersUsingFilter( _
			args.MemberListArgs.DimPk,  _
			$"{dimTypeCode}#{args.MemberListArgs.TopMember.Name}.Base.Where(Text8 StartsWith {prefix}) ",  _
			Nothing)
		' MemberList object needs a header containing the list name, 
		' so let's create it 
		Dim header As New MemberListHeader(args.MemberListArgs.MemberListName)
		' put it all together and return the list
		Dim theList As New MemberList(header, memberInfos)
		Return theList
	End If

 

You would then use it like this in CV rows or columns:

U8#YourTopMember.CustomMemberList(BRName=YourBRName, MemberListName=ByEntityPrefix, Entity=[|!YourEntityParam!|])

Note that, the way we've done it, this list can be applied to any dimension, not just U8. You could customize it further to make the prefix length dynamic, i.e. passing another parameter to the list and using that in the Substring call.

Let me know if there is any question, hopefully this opens a new world of possibilities 😊