Passing optional parameters

Question

I am working on SSRS Reporting Services with 2008R2.

ISSUE: I have two tables Orders and Customers.

I join both the tables and get the sufficient data.

My issue is how to pass Customername as optional parameter. I need to take this value from a query (I mean to display customer name in drop down).

To do this I have checked allow nulls as true for the parameter name: Customer name. But while generating the report it asks to select customer name.

I have also tried with:

Specified the filter condition of the dataset as follows:

Expression:

=IIF(Parameters!CustomerName.Value is nothing or (Fields!CustomerName.Value =Parameters!CustomerName.Value),true,false)

(Select Boolean in the second drop-down list)

Operator: =

Value: true

So what's the issue exactly? Can you populate the parameter query correctly? Do you want NULL (i.e. all Customers) to be selected by default? Is the report running but your filter not working? — Ian Preston

Ian Preston Ian Preston · Accepted Answer · 2013-09-08T20:30:20

Have you considered applying the filtering in the query?

First, set up the parameter, say Customer, based on a Dataset populated with a query like:

select distinct CustomerID, CustomerName
from Customers

Make sure it can pass NULL values.

Then in the main Dataset query text, you can use the parameter here by referencing it as @Customer:

select o.*
from Orders o
  inner join Customers c on o.CustomerID = c.CustomerID
where c.CustomerName = @Customer or @Customer is null

Obviously you'll need to change for your own specific tables.

If you want all Customers to be selected by default, you can set the default value of the parameter as =Nothing.