Dynamic Member List

[miloszmalecki]

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 parameter (XFBR). I am having trouble to achieve that... What I created is parameter supported by simple business rule which also use information coming from another parameter. This is working well but only if second parameter which i am using is not part of POV parameters. Looks like I can not use parameter inside used parameter. where this second is also used in CubeView POV... Let me try to describe you on example what is my problem, maybe some of you will be able to help. 

What i would like to achieve is to have some connection between entity selected in the cubeview POV and UD1 list of members which then will be presented as a list of members to choose inside parameter and CubeView POV.

Example:

I have a CubeView with POV where parameters are used for below dimensions :
Entity
UD1
UD2
UD3
UD4

But based on my selection in Entity i would like to have a specific list of members possible to select in UD1.

Lets say that in Entity I am selecting Spain unit (ES_123) what I would like to do then is to use only two initial letters "ES" which will be used as a information what kind of members I should be able to see in the UD1 parameter:

Either by some function that name of the UD1 member needs to contain ES (CUESXXXX) or TEXT8 property in UD1 is equal to ES.  

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

This is my solution which works only if i am using this parameter in rows and entity is in POV:

I was able to have a parameter like that:
U1#CU003900.Base.Where(Name Contains XFBR(CKA_ParamHelper, returnLeft2, Ent=|!ParamFormPromptEntity!|))
CKA_ParamHelper just returning entity name:
Return EntName.Substring(0,2)

[db_pdx]

I'd advise the TEXT8 property as you can incorporate this into your metadata management processes. As well, you'll have audit history on changes to the property.  That's how we manage the majority of our dependent lists. We use the full entity names rather than a shorthand/trimmed version, but I would think the trimmed version would work as well.

Your member filter should be:

U1#CU003900.Base.Where(Text8 Contains |CVEntity|)

or if using your custom function

U1#CU003900.Base.Where(Text8 Contains XFBR(...))

[miloszmalecki]

It is not working I was trying that. But the problem is that this CVEntity is taking entity name from :

And i need from here :

i have no idea how to do connection between what i am selecting in Entity and based on that i will get specific list of members possible to select in Customer 

[db_pdx]

My apologies, I didn't pick up the fact that you have these as all Form based parameters. I don't believe you can create the dependent checks with Form based parameters. Probably need someone to check me on that, @JackLacava ?

You could have the dependent member in the row or column and it will resolve correctly; I just tested that. Although this would display all members that meet your contains criteria, it would not limit it to one selection.

Alternatively, you could move these to dashboards where you have control over component refreshes which will ultimately allow you to achieve your goal of dependent parameter selections. This is what we do and we utilize combo boxes with dependent parameter selections.

I'd welcome others input on this thread in the event there are ways to achieve your goal while still utilizing Form based parameters.

[miloszmalecki]

No worries, thanks for your input, really appreciate to have a possibility to do the "brain storm".

Yes, I am using this functionality in rows for some Forms. But for rest this option is very inconvenient to put this in rows/columns section as it will be very unreadable and not user friendly.

[JackLacava]

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 😊

[miloszmalecki]

@JackLacava  Thanks for this solution. But still i have got the same problem :

I know how to use this functionality in Rows or Columns. I was using more primitive way: 

U1#CU003900.Base.Where(Name Contains XFBR(CKA_ParamHelper, returnLeft2, Ent=|!ParamFormPromptEntity!|))

CKA_ParamHelper: 
If args.FunctionName.XFEqualsIgnoreCase("returnLeft2") Then
Dim EntName As String = args.NameValuePairs.XFGetValue ("Ent")
Return EntName.Substring(0,2)
End If

But my problem is different. Both dimensions Entity and U1# i am using in Parameters. if i am using in U1# parameter of entity then i am getting this error as attached above. And this is my problem. If there is a way where i can use a parameter for U1# which will be looking at entity which were selected.