• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

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
Rhonda Carroll
Rhonda Carroll
  • 3
1 Solution
I dnt know how u guys use is null is oracle but the query should look like this

OR ( (Cust_I'd in (:Cust_I'd)) or (Cust_I'd is null))
Rhonda CarrollAuthor Commented:
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
Nico BontenbalCommented:
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.
Rhonda CarrollAuthor Commented:
neither option worked .. i ended up creating two reports
Rhonda CarrollAuthor Commented:
Neither option worked,  I created two reports

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now