Multiple Parameters in SSRS 2008

Posted on 2011-05-13
Last Modified: 2012-05-11
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
Question by:Rhonda Carroll
    LVL 11

    Expert Comment

    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))

    Author Comment

    by:Rhonda Carroll
    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
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    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.

    Accepted Solution

    neither option worked .. i ended up creating two reports

    Author Closing Comment

    by:Rhonda Carroll
    Neither option worked,  I created two reports

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (  This article explained how …
    Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now