Prompts with wild card

[NishiNidhi73]

Hi All,

User would like to search for customer names that starts with 'A%' or 'WAN%' or 'AMERI%' etc. Is it possible to use in operator with like operator?
Normally, in a query we use
where ((CustName like 'A%') or (CustName like 'WAN%') or (CustName like 'AMERI%'))
How do we get this in a prompt? I can't use Search prompt because there are more than 50000 customers.
Please advise.
Thanks for your help!!
Swathi

 

[ymonge]

If I am understanding correcylt then you need to try creating a filter on the prompt that reads

[CustName] starts with ('A') or [CustName] starts with ('AMERI') or [CustName] starts with ('WAN')

This should limit the search. I hope this helps.

 

[NishiNidhi73]

Hi ymonge,
Thanks much for your response!!
It works if it is just A, WAN or AMERI but the user can input any string, so how can I get the user inputs and use them in the prompt with multiselect wild card string inputs
i.e the user can view the customer names that starts with the user inputs.
Please help!!
Thanks
Soujanya

 

[globalbear]

How about adding one more prompt where you enter a value, e.g. "WAN". Then filter the second prompt with [CustName] starts with ?Parameter1?

 

[NishiNidhi73]

Thank you globalbear for your response!!
The issue is, the user can input any number of search strings. Is there any javascript that can be used in this prompt that can collect the input string and concatenate it with '%'? I contacted cognos support but they said it cannot be done. Please help!!

Thank you
Swathi

 

[globalbear]

I don't think I follow you now Swathi.

If you use 'starts with' you don't need to concatenate with '%'.

In SQL you would use something like
'select Custname from myTable where myTable.Custname like 'whatever%'

In Cognos you would filter on something like
'[CustName] starts with ?MyParameter'

Mark that there is no %-sign in the Cognos syntax.

This works fine for displaying all values that starts with e.g. 'WAN'. However, it seems that you want to filter on every value that starts with 'WAN' OR every value that starts with 'ABC' OR every value that... etc etc...

I don't know how to do that if that's what you want. My solution only filters on one (1) starts-with expression.

 

[misscognos]

why not use a search and select prompt?

 

[cog8user]

Hi Swathi,

You can use a text box prompt for this.
Create a text box prompt and modify the parameterized filter in your query as following:
CAST([data item], VARCHAR(XX)) LIKE ?Parameter1?

This enables the user for wild card search using '%'.
XX is the maximum number of characters of the data item in the database.