AnsweredAssumed Answered

Testing no value is passed to a multi-value parameter from Clarity

Question asked by RaviCCC on Jul 24, 2012
Latest reply on Jul 27, 2012 by RaviCCC
[font=Times New Roman]Hi,

I have Crystal report 2008 which is integrated with Clarity 12.1.1.. This Crystal Reports uses plain SQL. I have a multi-value parameter called project type (string). This is not required parameter. So when a user selects no value in the parameter, report should ignore this parameter and is expected to bring all projects irrespective of project type.

If user selects one or more project types, then report should bring only the projects with selected project types.

The following issue I am having. In the Command SQL first condition in OR clauses tries to capture no value is passed. But it is not working and throws an error


select i.code,
i.name
from inv_invesments i, odf_ca_project p
where i.id = p.id
and nvl({param_prj_type},'0') = '0' or p.prj_type in {param_prj_type}

I tried another condition to test null string. This time it doesn't throw any error but report return no rows

select i.code,
i.name
from inv_invesments i, odf_ca_project p
where i.id = p.id
and '' in {param_prj_type} or p.prj_type in {param_prj_type}


I don't want to use this parameter in Record selection, I want to use in SQL command. Also I don't want to create special value like 'All' in the lookup, since I will have to create and maintain
seperate set of lookups for reports.

Question what kind of value is passed to multi-value parameter when a user selects no value and how do we trap that condition?

Please let me know

Your help is appreciated

Thanks

Regards
Ravi Chandramouli
[font][font]

Outcomes