Hi All,
I have two input controls; Project ID and Change Request ID.
I have created a cascading input control that displays all Change Request ID's that are related to a Project selected in the previous Input control.
I am wondering if it is possible to specify in the where clause that a user can use the Input controls as above, or simply select all Change Request ID's related to a Project by using only the Project Input control?
Currently, my WHERE clause is as below. I believe this is where my issue lies.
WHERE (inv.investment_key = $P{PROJECTID} AND cr.change_request_key = $P{CRID})
OR cr.investment_key = $P{PROJECTID}
2 Answers:
Hey Kurtis,
Some of this might depend on your dataset... are your CRID's unique across the board, or will the project + the CRID form the PK for the table?
I usually use the $X{IN, cr.change_request_key, CRID} syntax (assuming you have a multi-select input) and find it resolves to nothing when no matches occur. Could you make 1 or both of the params optional and allow this to occur? e.g. if no CRID, the project key takes over and it returns all CRID's for the project(s) selected. This depends a little on your data structure but it may work for you?
Also should have a 1=1 in the where clause so that the query still pulls things back if both are blank.
Hi Robsil,
Thank you for the help.
The CR ID's are not unique to the system, however I believe they are unique to the Project; i.e you can have many CR ID's named 001, but only one named 001 can be added to a particular Project.
I must admit, at the moment I am using a single select query. I will try turning into a multi-select and see if it helps. Part of my question was what you have mentioned in your 2nd paragraph, you have just phrased it much better than I did! lol
When you say 'query still pulls things back if both are blank.', what do you mean? If no parameters are set, then the report shouldn't return data should it?
Thanks
Hi Robsil,
Thank you for the help.
The CR ID's are not unique to the system, however I believe they are unique to the Project; i.e you can have many CR ID's named 001, but only one named 001 can be added to a particular Project.
I must admit, at the moment I am using a single select query. I will try turning into a multi-select and see if it helps. Part of my question was what you have mentioned in your 2nd paragraph, you have just phrased it much better than I did! lol
When you say 'query still pulls things back if both are blank.', what do you mean? If no parameters are set, then the report shouldn't return data should it?
Thanks