Filter Matches SQL Pattern with Embedded Prompt

[egates17]

Query Studio has functionality that I am attempting to mimic in Report Studio.  If you are familiar with QS, you are able to Filter (Type in value) using an operation 'Matches SQL Pattern'.  Additionally there is an option to include multiple selections.  As part of a design test I opened a QS report in RS and found this filter:

#join('or',substitute('^','[Project ID] like ',sq(array('410393.03.1.0002.DC.AW%','410393.03.1.0002.DD.BL%','4PIM03.200%'))))#

I am hoping to replace the static values in this array with user driven prompt values.  The end result I am hoping to achieve is a prompt that allows a user to paste in a series or Project ID's (something like [Project ID] like array(?Projects?)...  Any suggestions?   Thanks, Eric

[cognostechie]

RS already has a Select & Search prompt that does it pretty well. It can also be multi-select.

For ex:

If you type e in the prompt and choose the option 'Contains any of these keywords', then it shows:

Berlin
Mannheim
Cowes

So it works like %e%

[egates17]

I understand the select and search prompting funtionality,  but the issue at hand is with the filter itself.  In my example above you can see that the array includes multiple values in a like statement.  Alternatively I could use the cognos 'Starts With' with static values.  Essentially my question is,  how can I get an RS filter like this to work:

[Project ID] starts with (?Projects?)

Where the prompt '?Projects?' is a multi select.  I know this syntax is incorrect but that gives you an idea of what I would like to achieve.  I am able to derive this with query studio but not report studio. Thanks for the assistance.

[cognostechie]

Either you are too hung up on the technicality of QS or I am too dumb to understand your issue.

As I understand, this is your issue -

Project IDs

410393.03.1.0002.DC.AW
410393.03.1.0002.DD.BL
4PIM03.200

The select & search prompt allows to search for these numbers and choose the option Starts with. The filter in RS needs to have  'in' operator and it will work. I tested this and it works using 'Starts with'. You can also type in 03 and use 'contains any of these words' and that also works. It automatically converts the SQL and uses the appropriate syntax.

As you said, if you could use 'Starts with' with a number of Project IDs, it would work. That's exactly how it works. You just don't have to worry about the syntax part as RS takes care of it well if you use 'in' operator in the filer and make the prompt multi-select. You also have other options like 'in-range' if you wan't to change the result set.

[egates17]

Leaving QS aside, I think is more of a case of me not explaining the situation properly. Using the example the three projects above,  the user wants to copy the values and paste them into a multi ling text prompt, then have the report aggregate/roll up at that project level (2) but accumulate all of the detail records for 4PIM03.200%.  I have a work around in place using multiple text prompts that the user is satisfied with but was hoping to find a single prompt solution.  Not to beat a dead horse,  but the filter construct that QS uses combined with a multi line text prompt would give the user exactly what she is looking for.  She wants to paste a series of projects into the box and have the report generate.  She knows she can do it in QS and wants this capability in RS.  Like they say,  this job would be easy if it weren't for those pesky users and their requests LOL.  Thanks again!

[Harman]

Hi egates
I am not sure if you resolved this but I had a similar problem and below is the macro code I used to resolve it.

#join('or',substitute('^','[ITEM] contains ',split(';',promptmany('Name of search string field','string'))))#

Regards
Sid