Making Parameters Optional - CR XI

[ReportDr00]

Hello,

I have two parameters ID and Name and i set default value to be one space and i would like to return results based on values entered for parameters, but user can only enter value for one parameters then the report should ignore second parameter and return results only based on one parameter. If user did not enter value for any parameter then report should ignore parameters and return all results.
I hope i am making sense
For example,
I have ID as param 1 and Name as param 2 then user enters value for ID and nothing for param 2
the sql query should be
select id, name, dob, ssn, age from patient where id=?ID

if Name param is entered and ID is null then
sql query should be
select id, name, dob, ssn, age from patient where name=?name

If both param values are entered then
select id, name, dob, ssn, age from patient where ID = ?ID and Name =?Name

If both param values are not entered then
select id, name, dob, ssn, age from patient

I appreciate you feedback.

Regards

 

[Turkbear]

Hi,
Set a default value like 'ANY' for each parameter then have your selection formula sometning like:

If ({?Param1} = 'ANY' and {?Param2} = 'ANY')
then
True
Else
If ({?Param1} = 'ANY' and {?Param2} <> 'ANY')
then
{?Param2} = {Table.Name}
Else
If ({?Param1} <> 'ANY' and {?Param2} = 'ANY')
then
{?Param1} = {Table.ID}
Else
({?Param1} = {Table.ID} AND {?Param2} = {Table.Name})

There are other ways to check but this should work



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ReportDr00]

Thanks, I will give it a try. i do have one question regarding your selection. Having the param2 or param1 on left side instead of right side makes diff? or it doesnt matter
{?Param2} = {Table.Name}

 

[Turkbear]

Hi,
It is a matter of choice, but I always tried to write the formula like I would read it:
"If my parameter is equal to the table value then get that data".

I do not believe it makes any difference to Crystal.




To Paraphrase:"The Help you get is proportional to the Help you give.."