Access Multiselect Parameters values in Formulate
Hi,
I’m trying to access multiselect parameters in Pyramid Analytics. Specifically, when using multiselect parameters in a dynamic caption, only the first selected value is shown, while the other selected values aren’t displayed.
I like to display all the selected values in Formulate and Discover using the dynamic caption option with PQL (Pyramid Query Language). I've tried using:
member.caption , caption(member)
But this only returns the first value. What would be the correct approach to display all selected values from the multiselect parameter?
ref:
following image shows first selected value..!
I’ve already seen this idea for measure as parameter, but would appreciate additional input or clarification.
Thank you!
Raja Sambasivam
2 replies
-
Hi ,
Unfortunately, there is no way currently to accurately retrieve the captions of selected members in a multiselect parameter. We can "manually" traverse the list of members in the parameter, but there is no PQL function to process a list an item at a time and stop when the list is processed.
Let me elaborate.
Here we have a multi select parameter based on first name, as in your example. We can use the .caption function to return the member name of the selected item for my dynamic caption but if more than one are selected, it only returns the first item in the selected list.
Using curly brackets, {}, we can turn our parameter into a list and them use the getelement() function to pick off the individual items. Here we're getting the first element's caption (the index starts at zero):
And it gives us the same result:
We can though "manually" pick off as many items as we want and concatenate their captions together. Note the increase by 1 of the getelement() index, 0, 1, 2, etc.
The problem is that there is no looping mechanism available in Semantic Calculations to traverse lists. So I can't say for example, count the number of elements then loop through each one, increasing the index by 1 each time until the count -1 is reached (-1 because index starts at 0).
Maybe a Product Idea post to ask for the list processing capability?
Hope that helps.
Ian
-
Hi
I have found a way to achieve this. It works, but you need to notice how it performs:
1. in the model (or in forlmulate): create a custom column which is the same as the column you are using (in this case - first name):
2. Define a Text measure to this column:
3. Define a calculation that aggregates your parameter (in this case with sales):
4. Go to Dynamic Caption and set it to aggregate the parameter with the Text Measure:
The result will be the total sales for the selected first names - with the first name in the title:
This approach can also be implied in other ways and other places in the platform, but this is the ide.
Cheers!
Moshe