Hi ,
I have a countries dimension and it has members like
NAM,LATAM-North and LATAM South and countries under each .
Now I have a query which shows all countries under LATAM NORTH and LATAM South .
Descendants(set(LatamNorth,Latam South),Countries).
Now user requires a custom member to also be shown in prompt .
That is LATAM North - Mexico (Exclude Mexico from LATAM North)
So I wrote a custom item where
Except(LatamaNorth , Mexico )
And then tried to do a union to the query that feeds the prompt
Uninon(Descendants(set(LatamNorth,Latam South),Countries),Except(LatamaNorth , Mexico ) )
But I get a member to value coercion error .
Can anyone suggest me how to handle this .
Quote from: nithya1224 on 15 Aug 2018 11:13:40 AM
Hi ,
I have a countries dimension and it has members like
NAM,LATAM-North and LATAM South and countries under each .
Now I have a query which shows all countries under LATAM NORTH and LATAM South .
Descendants(set(LatamNorth,Latam South),Countries).
Now user requires a custom member to also be shown in prompt .
That is LATAM North - Mexico (Exclude Mexico from LATAM North)
So I wrote a custom item where
Except(LatamaNorth , Mexico )
And then tried to do a union to the query that feeds the prompt
Uninon(Descendants(set(LatamNorth,Latam South),Countries),Except(LatamaNorth , Mexico ) )
But I get a member to value coercion error .
Can anyone suggest me how to handle this .
Hi,
Your expression doesn't seem to make logical sense - at least not to me
If I'm understanding, you have three members on one level - [NAM], [LATAM-North] and [LATAM South]
The members at Countries level below this are your Country members?
If that is true, what are you trying to achieve with the part of your expression that is Except(LatamaNorth , Mexico ). These are two individual members at different levels? Are you trying to get all the Country members below [LATAM-North] other than the [Mexico] member? If so, wouldn't the expression for this be
Except(descendants([LATAM-North], [Countries]), [Mexico])
Can you elaborate?
MF.
sorry , you were correct , i changed the expression to except(Descendants(LatamNorth],Country),[Mexico]).
This works with out error .
But i want this as 1 member shown in propmt ,along with other countries, now its showing all the countries except mexico . I want this to be grouped as one member and shown as a value in prompt.
Thanks
The except function is set function that is creating a list of members. What you want to do is to create a calculated member that will merge the resulting set into a single member.
Use the following syntax:
member(aggregate(currentMeasure within set except(Descendants(LatamNorth],Country),[Mexico]))
,'LATAM North - Mexico'
,'LATAM North - Mexico'
,INSERT_HIERARCHY_HERE)
Add that as a static item to your prompt.
I have created that member under the name test .
And have the following expression .
member(aggregate(currentMeasure within set except(Descendants([LATAM-NORTH],[LATAM Sales].[Countries].[Countries].[Country]),[MEXICO])
,'LATAM North - Mexico'
,'LATAM North - Mexico'
,[LATAM Sales].[Countries].[Countries]))
But how do i know show all the countries and this one member in prompt .
If I do union , it will throw me an error as the member expression is not a set .
can I have static items , as well as query fed to a same prompt .
If its static then i can only add text values , should i do some calc when that text is selected .
You can absolutely have both query derived items and static items in a single prompt. Give it a try and see what happens.
how do i add the static member .
I have a member created in the query called test which has the calculation .
When i go to static choices add , it asks for use value and display value .
Use value should it be the member name in the query which is test .
Best would be to put the member() function in the use value and LATAM North - Mexico as the display.
If you have the [test] data item in your query, you could probably get away with having [test] as the use.
No Luck with it .Getting the below error:
Invalid format for prompt 'PrmCountry'. Expected format is unknown.
getting this error when that member is selected from prompt .
report filters on prompt value used as slicer below .Works fine if other members from the query are selected .
Slicer used : #prompt('PrmCountry','mun','[LATAM-NORTH]')#
Any help is appreciated
Use token instead of mun.