Introduction
Like every other report, we have some filters when displaying our data. This is also the case for SQL Reporting. But most of the times, those filters have several options to select from. In this article, we will dig deeper into how to add a �Select all� option. With "Select All", we mean that when using a filter, you also get an option "Select all" above of the option list with the database values.
Using the code
There is not much coding to be done. Just a few lines of code in your data sources will solve this issue. First, create a new dataset for your filter options on your data tab.
Now you can create your standard query to show the result to filter within your dropdown. When this is created, you can add the following piece of code:
UNION
SELECT '-1','All Publishers'
When those lines are added, you will get the following message:
Just press Yes, otherwise your union code will disappear.
Then create the dataset with the data you want to show. Create also for this one, your SQL statement with the field you want to show. For the where clause, you need the following code:
(pub_id = @publisherParam) OR (@publisherParam = '-1')
Now, the only thing that rests us, is creating the report. Create your layout and add your parameter to the report parameters under the report menu. For the parameter selection, we use the following settings:
- Available values: Use From query. Take here the publisher filter you have created on the data tab.
When this is done, you can preview your report. Select a publisher and you will see the titles of this publisher. Select all publishers, and you will see the complete list of titles.
One note. If you want to have more than one parameter with a Select All feature, you should consider putting your report query in a Stored Procedure. This is because SQL Reporting tool regenerates your query and at that moment the query doesn�t give you the exact result anymore.
Points of Interest
SQL Reporting tool has a lot of nice features. For some of them, you have to program a little. One of the nice features is alternating rows by Jayarajan S Kulaindevelu.
