Dependent SSRS parameters with a multi value parameter and another null allowable parameter

0
votes

I am working on a SSRS report with a table thats similar to the below one

enter image description here

Now I set @ProductID and @Customer as two parameters for my report. I want the report to work as when I select a particular ProductID and leave Customer as Null I should get all the results for that ProductID and When I select both ProductID and one particular Customer I should get the result set for that ProductID with the selected Customer only.

I tried adding both the parameters and allowed multiple values for @ProductID and Null value for @Customer. But the problem is when I select @ProductID and leave @Customer as Null, SSRS is returning an empty tablix. But when I pass-in the value for @Customer then its returning values for that particular @ProductID and @Customer combination.

Where am I doing wrong? Thank you guys.

Note: I need both of them as multi-valued parameters with null value allowed for @Customer. How to allow null value for multivalued parameter is another story for which i have found a solution, so that is not a problem

Edit:

I figured that the problem is with the query in the stored proc itself

When I have where clause as

Where ProdID = @ProductID AND Customer = @Customer

I got the above mentioned problem but when I changed AND to OR then it worked when I allowed null value for @customer. But when I want to pass the values for both of them its not working again.

So I might need a case statement or something to satisfy my condition. Can anyone help me with that?

1
sqlsql-serverreporting-services

1 Answers

1
votes

Here is a solution for adding Null to a multiselect.

for the second part i would do, 

where PartId = @ProductID
and (@Customer IS NULL OR (Customer = @Customer))