Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

Add "Select All" to parameter lists in SQL Reporting

0.00/5 (No votes)
9 Mar 2005 2  
How to add a "select all" option to your parameter lists in SQL Reporting.

Result using the Select All

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.

Creating your dataset

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'

The parameterquery with the select All option

When those lines are added, you will get the following message:

The message when using the UNION option

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

The WHERE clausule in your report query

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.

Adding parameter selection to your report

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.

Final result with All Publishers selected

Final result with one Publisher selected

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.

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.

A list of licenses authors might use can be found here