How to make SSRS filter selection optional?

0
votes

I'm using SQL Server 2005 Reporting Services to create a new report.

This report has 2 optional parameters that can be selected by users or users can leave them blank. Both are dropdown selection.

The problem that I'm facing is, I had set both parameters to Allow Null Value, but not Allow Blank Value. When I click on View Report button without select any filter criteria, the report give me 0 records. But if I selected a value for both dropdown selection, the reports give me result.

@Package is varchar(100) and @Plant is tinyiny.

I write the WHERE clause in this way. please help and much appreciate.

WHERE (@Package ='' OR PACKAGE_TYPE LIKE '%' + @Package + '%') AND
 (@Plant IS NULL OR PLANT_ID = @Plant )
1
sql-serverreporting-servicesreportingservices-2005
And your question is ...? – user359040

1 Answers

0
votes

So when no filter criteria are selected, both @Package and @Plant are NULL?

In your WHERE clause above, you're only handling @Plant NULL values.

You need to check for NULL @Package, too, something like:

WHERE (@Package IS NULL OR @Package ='' OR PACKAGE_TYPE LIKE '%' + @Package + '%') AND
  (@Plant IS NULL OR PLANT_ID = @Plant )