Click here to Skip to main content
15,860,972 members
Articles / Database Development / SQL Server
Article

Add "Select All" to parameter lists in SQL Reporting

Rate me:
Please Sign up or sign in to vote.
5.00/5 (9 votes)
9 Mar 20052 min read 102.3K   25   9
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:

SQL
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:

SQL
(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


Written By
Web Developer
Belgium Belgium
After playing with computers since the early 80's. I started developing on an AS/400.

After 5 years I leave this area and started programming using ASP and VB6.

When .Net was released I switched over. I started as a .Net programmer/Web Developer. Mostly working with ASP.Net, C# and VB.Net, Oracle, SQL Server, Reporting server, Host Integration Server 2004.

I ended up as a .Net Solution Architect. At this moment I'm getting more and more to the role of Business Consultant.

Comments and Discussions

 
GeneralAdd "Select All" to parameter lists in SQL Reporting Pin
farah78m5-Jul-07 4:16
farah78m5-Jul-07 4:16 
AnswerRe: Add "Select All" to parameter lists in SQL Reporting Pin
Sven Cipido5-Jul-07 20:47
Sven Cipido5-Jul-07 20:47 
GeneralRe: Add "Select All" to parameter lists in SQL Reporting Pin
farah78m5-Jul-07 22:50
farah78m5-Jul-07 22:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.