dynamic parameter filter, wildcard

[hmax]

Crystal Reports 2013
Oracle - using Command in Crystal to retrieve dataset

1. In a dynamic parameter, when we choose to filter on the available values, is there a way to use a wildcard?

I've tested in the Record Selection Criteria where
table.field = {?parameter}
table.field like {?parameter} + "*"
table.field startswith {?parameter}

Should I, instead, use a wildcard at the level of the dynamic parameter filter, and if so, how is this done?

2. Results are always the same, whether I use "=", "like [...] + '*'", or "startswith". Therefore, is the dynamic parameter itself and/or its filter processing the results during a different "Pass" of the data?
I have not found any documentation online about this.

Thank you.

 

[Charliy]

I did a little testing and found that LIKE does not always return all the results.
STARTSWITH works well and is very fast.

 

[hmax]

Charliy, thanks for responding. That makes sense, because "like" will search the pattern in every single instance anywhere within the pattern, whereas "startswith" will only search for those that start with the pattern.

However, creating the dynamic parameter at the level of the report (not in the Command), where one has thousands of records (yes, I am aware that for performance purposes Crystal places a max of 1,000 records for the parameter unless a change is made in the registry to accommodate more), the results are "funky". In testing "=/like/startwsith" in the record selection criteria for the dynamic paramter, the results play out to be the same, which is not what one would expect. Therefore, it seems the dynamic parameter is not even looking at the record selection criteria when processing its results.

This is what I'm attempting to verify.

It would seem, logically, that a dynamic parameter will initially only retrieve results from the data source that "equal" what is in the database.
Then, once that dataset has been retrieved, another step needs to be taken to add a "like" or "startswith" clause - somewhere - in order to filter the results based upon what has been retrieved and what the client wishes to see.

So, how does one accomplish this using "a wildcard", "like", or "startswith"? -
(a) within a report level dynamic parameter,
or
(b) at the Command level (we are using a Command for our data source, I am used to using stored procedures, it has been a while since I've used Commands at all because generally, in big enterprises, the performance is too poor with Commands - this is a small shop).

I'm looking for a robust, dependable approach and have not yet come across one for creating parameters with a wildcard.

Thanks,

hmax

 

[PeterDimitrov]

Try with separate commands. Create Command1 to get LOV for Parameter1, and Command2 to get LOV for Parameter2. In Command2 use Parameter1 to filter LOV. Since the command will be an Oracle SQL clause you can use any SQL Operator including LIKE. This will work exactly as you want, but you will have 2 separate dialog boxes to select values for Parameter1 and Parameter2.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[hmax]

PeterDimitrov, apologies for not responding sooner.

Good idea for small LOV, and I'll keep this in mind for such a scenario. Unfortunately, the LOV is large, and would also require manual updates, so I'll have to keep hunting for an alternative solution.

Appreciate the feedback!

 

[hmax]

This works below:

I am now able to retrieve, in the Command statement, two parameters with multiple values (both of string type), set as Static parameters.
The same table.field is the dependency for both params, {?ProgCode} and {?ProgCode1}.
The client manually enters the params (there is no LOV - too large and would require constant manual updates).

Example:
(Table.FieldCODE like {?ProgCode} or TableFieldCODE IN {?ProgCode1})

Sample input for {?ProgCode} is '02%'
Sample input for {?ProgCode1} is '012'

Since these are Command parameters, it is not possible to make them optional, it seems.

Now, I must find a way to make them optional - the client can choose one or the other, or both, or none.

Grateful for suggestions.

Thank you.