Cascading Prompt with Wildcard Search

[mgallot]

Using CR XI2 against SQL Server db. The user wants the first parameter to be Client Name (of which there are 1000's) so they want to search using a wildcard, such as %United%. Then they would like to see a list of Client Codes to select from. Can I use a cascading prompt for this?

I set up a command to select all Client Names and Client Codes. But I am not sure how to allow the user to key in a custom value from a parameter, or if that is even what is needed. I know this is available with regular parameters, but I don't have the option in Dynamic cascading prompts.

Is anyone doing this?

Thanks.

 

[briangriffin]

I don't think you'll be able to use wildcards in that way. Have you considered using a command to create an index?

select
substring(clientcode,1,1),
substring(clientcode,1,3),
clientcode

from yourtable


The user would select the first character, A-Z, then the first three characters that start with the selected letter, then hopefully a manageable list of clients to choose from.

You could tweak if of course to match your data better, but the user wouldn't have to type anything. The downside is if the user knows it's 'something United something'; the prompt values wouldn't help much.

 

[mgallot]

Okay I set up the command. But how should the parameter be set up? I have Client Name as a dynamic parm using the value of Client Name in the command. But it just brings up a list of names. How do I get that smaller list of names to come up as the next parameter selection?

 

[briangriffin]

When you define the hierarchy under Values, the first calculated field (first letter only) will be the first one listed. The the second calculated field, then the client name.

You only need to actually create the parameter for the client name field, which can be a little hard to get your head around. It should look like this:

Value Parameters
FirstLetter Click to create parameter
FirstThreeLetters Click to create parameter
ClientName ?MyParameter - ClientName

Then in your selection criteria it would be:

{ClientName} = {?My Parameter - ClientName}

By the way, you can rename the parameter to something better in the Field Explorer. Also, dynamic parameters can sometimes be hard to edit, so I would suggest deleting your current one and starting over.

 

[scoffron]

Hello & Thank you, briangriffin!

I am working on almost the same problem except with an Oracle db:
I've added the Command and the Prompt as suggested. However, the FirstLetter prompt only has the following 4 values:
... (space) * A

And the FirstThreeLetters:

....BA CA Ho Ma SH

I know there are a lot more values in the table than these. Any suggestions?
Also, Do I link the Command object with the table that I'm using in the report?

 

[briangriffin]

I just tried with an Oracle db and it returned all the values. Do you get all the expected results when you run the query in an editor?

I'm wondering why you get the space as one of the values - is there any possibility that the field values have spaces in front? You might have to trim the field, but still I don't think that would explain your problem.

I would also try something like this to get a feel for what to expect:

select substr(yourfield,1,1),
count(substr(yourfield,1,1)
from yourtable
group by substr(yourfield,1,1)

You don't link the command to your table, but I don't think this would expain your problem either.