Using Promptmany with like or contains commands

[Cognos Talk]

I created select and search prompt and should be able to select multiple values.
When creating a filter I run in to the problem:
Filter using command "in" works fine: [TestItem] in (#promptmany('pTestItem')#)
Then I need to create another filter, using "like" command:
[Test1] like '%'|| (#promptmany('pTest1')#)||'%'
It only works if I select one item from select and search prompt, when selecting multiple items it gives me an error:
V5 syntax error found in expression "[Statute] like '%'|| ('CC-271(1)';'CC-181')||'%'", invalid token ";" found after "[Statute] like '%'|| ('CC-271(1)'".

For some reason it separates the values using ";" instead of "," (coma)
The same error comes when I use contains. [Test2] contains (#promptmany('pTest2')#), How I can fix the filter to report on multiple selected values?
I use Cognos Version 10.2

Thank you very much for your help,
MK

[calson33]

Like and contains only work on single values, IN can work with a set of values.

To do what you want, you would need to modify your return values so that they build a "LIKE" for each selected item within your query.

So, instead of
where
....
......
and [Test1] like '%'|| (#promptmany('pTest1')#)||'%'

You would have
where
....
......
#promptmany('pTest1')#


where the promptmany would resolve to
and [Test1] like '%MYVALUE1%' and [Test1] like '%MYVALUE2%'
etc.
Where
and [Test1] like '%MYVALUE1%'
is the return value for a single selection.

It is a bit more complicated than what I have written, but that is the general direction. Maybe some of the real experts around here could help you out with the syntax if you run into problems.

[CognosPaul]

You can generate the filter using macro expressions on a promptmany:

[Test1] like
#
join('%'' and [Test1] like ''%' +split(';',promptmany('pTest1','token','','''%','','%'''))
#

I'm not at a Cognos install, but I think this should work.


If the user selects a;b, it will
1. wrap it in '%a;b%'
2. split it into an array on the ; ['%a][b%']
3. Join it as '%a%' and [Test1] like '%b%'

[tschuman13]

Was anyone able to get this to work?  Need a solution similar to these but am getting errors trying to run it.

[CognosPaul]

Please see rule 1 from the forum etiquette post.

[cognos2014]

I have a similar problem where multi-select values are selected in the prompt and need to be used with LIKE in my query. I tried the solution suggested above and get a parsing error. Any help appreciated as I don't work much with macros.

thanks!

Example:
Prompt1 Options (multi-select)
A
B
C
D
E

Dataitem1 is in this format - A,B,C

[Dataitem1] like
#
join('%'' and [Dataitem1] like ''%' +split(';',promptmany('prompt1','token','','''%','','%'''))
#

[kmahesh]

I got the same situation but used contains parameter instead of like. Below is the syntax I used and it worked..

[COLUMN_NAME] contains # join (''' or [COLUMN_NAME] contains ''', split(',', promptmany('PARAMETER_NAME')))#

In the above filter condition replace, Column Name with Report Field Name and Parameter Name with your parameter. Also you can replace contains with any other parameter.

[mavi1122]

I have a report that has a multi line text box prompt where users can paste a list of names. The requirement is that regardless if the field is uppercase, lowercase or Initcap fields, it should be able to find the names from the source.

1) Used the following filter: #join('or',substitute('^','(upper([Data Item])) Contains ',split(';',promptmany('Parameter','string'))))#.  This appears to be working if the users manipulate their list of names to be all upper. Any suggestions on how it can be done without list of names manipulation?

2) I've tried upper('Parameter') but unsuccessful.

3) Tried replacing Contains with Like and included %wildcard, but the report just ends up bringing back everything(filter ignored).

4) Tried "starts with" function but didn't work either. Only retrieved how they were entered on the prompt box.

Should i be revamping my filter in it's entirety? Suggestions?

[CognosPaul]

Try:

Code Select Expand

#join('or',substitute('^','(upper([Data Item])) Contains ',split(';',
toupper(promptmany('Parameter','string'))
)))#

[mavi1122]

CognosPaul that worked! 😊👍🏽 You're a genius. Truly appreciate your help sir.

[shradha]

Need help with this. Using it exactly as  (#join('or',substitute('^','(upper([Data Item])) Contains ',split(';',
toupper(promptmany('Parameter','string')))))#). But i get an error stating toupper/upper is not a macro function.

Any help to achieve this within macro. TIA.

[dougp]

In the case of this macro, upper is part of the SQL statement and toupper is a macro function.  If you are using it exactly, you shouldn't get that error message.  Can you paste the actual error message Cognos is giving you?

[CognosPaul]

toupper is a DQM only macro function. If you're not running DQM you would need to wrap each item in a database upper function.

Code Select Expand


#
join(
  ') or '
, substitute(
    '^'
    , 'upper([Data Item]) Contains upper('
    , split(';'
      , promptmany('Parameter','string',sq('abc'))
    )
  )
)
+')'
#


[dougp]

Brilliant!  I wasn't aware the list of available macro functions differed between DQM and CQM.  And Cognos is smart enough to omit toupper from the list of available macro functions if the package uses CQM.  I'll pay closer attention to what's available in the Expression Definition UI in the future.

[shradha]

thanks a lot. It worked. :)
One more, what changes should i do if i need to apply IN, in place of CONTAINS.
Sorry, new to macros, so can't explore much. :)

[CognosPaul]

can you post an example of what you're trying to achieve?

[shradha]

I basically have few multi prompt in my prompt page. The values has to be case insensitive. So i want to achieve something like upper([DataItem]) in upper(#promptmany('param')#). I can't use like/contain as i want to filter exact values as the input.
Thanks. :)

[CognosPaul]

This should do it:

Code Select Expand


#
'upper([Data Item]) in (upper('
+ join(
  '),upper( '
  , split(';'
    , promptmany('Parameter','string',sq('abc'))
  )
)
+'))'
#


End result will be something like:

Code Select Expand

upper([Data Item]) in (upper('a'),upper('b'),upper('c'))

[shradha]

it did work.  :) You're a genius :)

[Magdalina08]

toupper is a DQM only macro function. If you're not running DQM you would need to wrap each item in a database upper function.

Code Select Expand


#
join(
  ') or '
, substitute(
    '^'
    , 'upper([Data Item]) Contains upper('
    , split(';'
      , promptmany('Parameter','string',sq('abc'))
    )
  )
)
+')'
#


My issue was solved with this!  Thank you!  I've been working on this all day.   :D

[Magdalina08]

Is there a way to use the promptmany macro similar to a case when statement?

Requirement:
We have offices A - Z
I need a multi-select prompt where users can choose A, B, F, R, T, and/or All (all will show all other values)

I have this working with a AND OR prompt, but it's not multi-select. 

[naughtyca]

Try:

Code Select Expand

#join('or',substitute('^','(upper([Data Item])) Contains ',split(';',
toupper(promptmany('Parameter','string'))
)))#

sorry for being ignorant very new to macros, so im trying to create a prompt where a user can copy and paste multiple list of IDs in native SQL

Is this how i use this macro?

where a.ID contains
#join('or',substitute('^','(upper([a.ID])) Contains ',split(';',
toupper(promptmany('Parameter','string'))
)))#

its not working for me
thank you