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.
