Input control logic in WHERE clause

[kurtis.fleming]

Hi, I have built a set of reports, that contain multiple Input controls (Single select queries, Single Values) and i am having a little trouble understanding how to configure them properly. I would like the user to be able to use the Input controls as they please; i.e they can either use a single input control, or more than one input control (as long as there are records that match the criteria). I decided not to use the 'AND' operator, as the user would need to satisfy all criteria in the WHERE clause.

 

My WHERE clause is as follows:

WHERE

(

rsk.risk_key = $P{RISKID}

OR ch.investment_key = $P{PROJECTNAME}

OR pa.investment_key = $P{PROGRAMME}

OR ch.investment_manager_key = $P{PROJMAN}

OR rsk.rim_status_key = $P{RISKSTATUS}

OR rp.rim_priority_key = $P{RISKPRIORITY}

OR rpb.rim_probability_key = $P{RESRAGPROB}

OR imp.rim_impact_key = $P{RESRAGIMP}

OR rsk.owner_key = $P{RISKOWNER}

OR rsk.act_owners_key = $P{ACTIONOWNER}

)

AND ch.investment_key = rsk.investment_key

AND rsk.risk_id IS NOT NULL

AND prj.is_program = CASE WHEN $P{ISPROGRAMME} = 1 THEN 1 ELSE 0 END

AND rsk.rim_status_key != 'CLOSED'

ORDER BY rsk.risk_id ASC, ch.investment_name ASC

)x

WHERE $X{IN, res_risk_rag_key, RESRAGSTATUS }

This does not work for me, because:

1. If the user selects a value for $P{RISKSTATUS} and a value for $P{RESRAGPROB}, this will not work.

2. If the user selects a value for $X{IN, res_risk_rag_key, RESRAGSTATUS }, this will not work unless a value is added in another input control.

 

Appreciate any answers! Thankyou for your time.