Crystal Reports 2013, Oracle 11g, Command Parameters for same field, wildcard and existing results

[hmax]

Crystal Reports 2013
Oracle 11g
Parameters within the Command statement

Goals:
1. Create a parameter or parameters that allow(s) retrieval of
(a) results that exist in the values; and
(b) results that are like those that exist in the values (wildcard).
2. Parameter entry is optional for both, one or the other, or none (there are other parameters). [Optional does not seem possible for Command parameters.]
3. Using a LOV is not an option (too large, and would require constant manual updates) - client will enter parameter results required manually.
4. The same Table.Field is the dependency for both parameters below ({?ProgCode} and {?ProgCode1}).

So far:
Testing capability to retrieve multiple mixed result types ("like '02%'" and "in '012'") within one parameter fails.
So, two Static parameters have been created within the Command statement, both of string type, with "Multiple" checked for each, as follows -

Example:

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

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


Questions:
(a) Is it possible to make these parameters optional (choose one or the other, both, or none)?
(b) Is it possible to capture mixed results in just one parameter (e.g., like wildcard '012%' and in existing '012')?


Thanks!

 

[tomcat24]

YES, This possible!

I would address this in the STORED PROCEDURE

1. CREATE a additional PARM to indicate how many PARMS you would LIKE to use EXAMPLE PARM1='1234' PARM2.......
2. IN THE WHERE CLAUSE

THEN BEFORE every IF statement for the PARM use the LIKE command
1ST IF: IF PARM1 LIKE '1%' THEN USE THE CONDITION
2ND IF: IF PARM1 LIKE '%2%' THEN US CONDITION AND SO ON

3. IN CRYSTAL REPORT
DYNAMIC PARMATER populate the possibles available for PARM1
'1---'
'12--'

 

[hmax]

Hi, tomcat24!

Thanks for your response. Unfortunately, in this scenario, we are not using stored procedures, but instead Command statements.

So, the parameters are created at the Command level, too.

Appreciate your feedback.

 

[tomcat24]

Then move everything I suggested to the Report Selection Expert in ONE BIG FORMULA.

 

[tomcat24]

https://www.youtube.com/watch?v=tPcw27SwmqY