Add "Select All" to parameter lists in SQL Reporting

How to add a &quot;select all&quot; option to your parameter lists in SQL Reporting.

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:

UNIONSELECT'-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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.