Multiple Parameters in SSRS 2008
I have a query developed in Oracle that has two parameters, the user wants to be able to select either parameter or both. i.e. enter a value for parameter 1 and leave the other parameter therefore returning all data that meets one parameter. I have done this before with SQL as a datasource but I am missing something this time when I use Oracle as a data source. I need to be able to select one parameter or the other
Here is the where clause
where h.invoice = d.invoice
and H.invoice in (:INVOICE)
OR cust_ID in (:Cust_id)
order by 1
Here is the where clause
where h.invoice = d.invoice
and H.invoice in (:INVOICE)
OR cust_ID in (:Cust_id)
order by 1
ASKER
I have tried that but the problem is the field is never null, I want the report to prompt the user to select either parameter or both parameters. When one parameter is selected the report prompts for the second parameter..if the code has the null piece in the where clause the report comes back blank as there is no combination of invoice with a blank cust ID
You can't have a multi value parameter with a null value. You could set the default values to the same datasource. This way all the the customers (or invoices) are selected by default. You could also a <all> value to your parameter. When you use this query as the datasource for your parameter:
select id, name from customer union -1, '<all>'
(syntax might be a bit different for Oracle) You'll get an extra option. You could set this )-1) as the default for the parameter. In your query you could use this in the where clause:
OR (cust_ID in (:Cust_id) or -1 in (:Cust_id))
(Again not sure if this works in Oracle also).
The advantage is that this will return records where cust_ID is null also when you select the <all> option.
I'd like SSRS to accept null values for multi value parameters also, but since it doesn't you'll have to find some kind of workaround.
select id, name from customer union -1, '<all>'
(syntax might be a bit different for Oracle) You'll get an extra option. You could set this )-1) as the default for the parameter. In your query you could use this in the where clause:
OR (cust_ID in (:Cust_id) or -1 in (:Cust_id))
(Again not sure if this works in Oracle also).
The advantage is that this will return records where cust_ID is null also when you select the <all> option.
I'd like SSRS to accept null values for multi value parameters also, but since it doesn't you'll have to find some kind of workaround.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Neither option worked, I created two reports
OR ( (Cust_I'd in (:Cust_I'd)) or (Cust_I'd is null))