How to select multiple values for a contain operator?

[pkrishnaq]

Hello,
I have a value prompt that displays names Taco,Chicken,Burrito

The parameter is designed to show values that contain any of these values .

So ,my main query is [Column] contains ?Name?

However, I can choose only a single value from the prompt page. How can I choose multiple names?
(I have my multiselect to yes. )

I cannot use 'like' here. That also allows only single values to be chosen

[pumccg]

When you create a prompt on the prompt page, it creates a filter back on the main query. If you change the prompt page to Multi you have to also change the filter to "in".

[pkrishnaq]

I don't want the 'in' operator here.

The values in the table are something like this

Column A:1,2,3
Column B: (Hawks,Heat) , (Spurs,Lakers,Heat), Hawks
Column C:W,L,W


If you have noticed from column B, a row item is a wm_concat of multiple values.

So, if in my prompt I have the values Spurs,Hawks,Heat and Lakers and the user selects 'Hawks' ... the 'IN' operator would only show Hawks in my report.

So the report result would be

Column A: 1,3
Column B:Hawks,Hawks


I would need to show the other values tied to this column
My expected result:
Column A: 1,3
Column B: (Hawks,Heat),Hawks

The 'contain' operator can achieve the above desired results. But in the prompt page , I can choose only one. How can I choose multiple values from the prompt?

[anaz]

Hi kratos,

I am new here, so I apologize if I am breaking any protocols.

I am wondering if you were able to find a solution for this. I have the exact same issue and would like to know your soution if you found one.

Thanks

[BigChris]

I think you'd need multiple prompts if you want multiple contains filters. So your filter would probably end up looking something like:

Code Select Expand


([Column] contains ?pPrompt1? and ?pPrompt1? is not missing)
or ([Column] contains ?pPrompt2? and ?pPrompt2? is not missing)
or ([Column] contains ?pPrompt3? and ?pPrompt3? is not missing)
etc.

[anaz]

Thank you BigChris for replying.

The solution you suggested wouldn't work, since I have one multi-select prompt from which the user is choosing different values (and its not ljimited to three values) and I want contains for each of those values. Would need a more dynamic solution.

So for example.

Mutliselect prompt has choices listed as such

A
B
C
D
E
F
G

In the report, however, the column has another data item, which can have any combination of those values.

so in the column, we can have any combination of the above:

A, B
A, C
C, B
A, B, D, E
F,  G
E, F, G

etc

So if a user selects A, B and E, I need to find all the records that have A, B and  E.

So I should get all these reocrds

A, B
A,C
C, B
A, B,D,  E
E, F, G

I need a statement such as contains ('A) or contains ('B') or contains ('E') for the values the user selected in the mutliselect prompt.

Thanks again for taking the time to reply.

[BigChris]

I can't think of a way to do that. If you think about the SQL that the query would need to generate there'd be no way for it to loop round the values that you've selected. The closest I can think of is to have some working columns where you split out (deconcatenate? unconcatenate?) the data that you've concatenated and filter on those using the IN operator.

[hespora]

similar issue has been solved here: http://www.cognoise.com/index.php/topic,32391.0.html

[anaz]

Thank you hespora. :)