Filtering on static choice prompt

[eddiesvoicebox]

Hi everyone, apologies if this has been answered elsewhere but i havent managed to find the solution to my problem so far.

I have a list of Countries in a multi select prompt '?Country?':

• UK
• Ireland
• Canada

I am then filtering my query based on the selection ([Country]=?Country?) and have added a data item with a calculation to assign a target value based on the selection:

Code Select Expand

case
when [Country] = 'UK' then 0.75
when [Country] = 'Ireland' then 0.65
else 0
end

However i want to expand this logic to pick up multiple selections from the prompt (i.e. when [Country] in ('UK, Ireland') then 0.65). but ive been unable to succefully find the right syntax that matches whatever the parameter is when more than one Country is selected, every method ive tried only recognises the first selection (UK). I dont need anything calculated with regards to the target, just to return a hardcoded value that i set in my expression.

After a lot of googling i was unable to find a solution to the problem, but i did come across an alternative angle to approach the problem. A solution posted here combined with one on IBM support (https://www-01.ibm.com/support/docview.wss?uid=swg21335756) suggested building my prompt from static choices (currently just pulling Country from the data table).

With this solution i can include 'Europe' in my prompt and it will return UK & Ireland only, which would work. Based on this i have set up the new prompt for a test query and added the below detail filter in my query:

Code Select Expand

if ((?Test?) = 'Europe') then ([Country] in ('UK', 'Ireland'))
else ([Country] = (?Test?))

This works perfectly when i 'View Tabular Data' on the query, i get the filtered list i need. In addition when i display the Parameter (?Test?) on the report page it does give me my selection. However running the report normally doesnt return any data to a simple list.

Whilst i realise these two methods address slightly different problems, essentially what i am looking to do is find a way to identify the multiple countries being selected so i can add a clause to a query expression. What ive actually done is google a lot of things and come up with 2 half solutions. Any help would be greatly appreciated.

Thanks

EV

[bi4u2]

Instead of supplying values based on a prompt selection, if you want to select more than 1 country you would need to add a custom data item based on all the available options you have. Put the case statement in the data item, then the prompt controls the countries pulled only and not the calculation.

[eddiesvoicebox]

Thanks for coming back on this. So if i understand your suggestion correctly, i think thats what i am already doing (or trying to). I probably just explained it badly  :-[.

My query filters the list based on the prompt and then i do have a data item with the case statement. If i show my results at a country level everything is fine as each row picks up the appropriate target value from the case statement, its just when i summarize it to an overall level that im having the problem. Is there another way of achieving this without referencing the prompt results? I just cant seem to identify the combination of Countries (stored in the parameter) so that i can add the appropriate clause.

The 2nd solution (with the static choices) feel like a closer solution to me as i can set all of the combinations in the prompt, and i can use the same logic for assigning the target values in the case statement.

Im happy to switch up the method of doing this if i am over-complicating things. This seemed like the most logical way of doing it when i started but has now had me scratching my head a lot over the last week or so.

Thanks
EV