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

Workaround: When a user clicks "Select All", the report should pass a blank value instead of parsing everything in the MultiSelect DropDown parameter

Rate me:
Please Sign up or sign in to vote.
2.43/5 (5 votes)
12 Feb 2008CPOL2 min read 46.8K   16   5
If you have a long list of drop down items and you want to pass in one value, you can use this method
Image 1

Introduction

I had two problems when dealing with Reporting Services. This article address the following two issues.

1) Having a "null checkbox" in the drop down menu so that a user is not REQUIRED to enter a value for every multi select drop down.

2) If all the values are selected (i.e Select All is selected), all the values in the drop down are not passed into the query/stored procedure since it will hamper the performance.

The image above shows the null checkbox and all the values selected.

Background

My client refused to have an application layer on top of Reporting Services. So everything had to be done in the Report itself. With many limitations that come with Reporting Services, there were only so many ways to handle the problems (as described above)

Creating the Null Checkbox

So how do we let the Report run, without selecting a value in the drop down multi select checkbox. Unfortunately, you cannot pass a blank value/null value by default. You have to select atleast one value.

My solution to the problem was creating a null checkbox. The way to create it, is as follows:

1) Create a New Dataset, call it (for example) EmptyDS.

2) Write the following Code in it

SELECT ColumnName FROM TableName UNION ALL SELECT '' AS Expr1 ORDER BY ColumnName

3) Go to the Design View, and open Report Parameters. After selecting the Parameter, under default values, select "From Query" and select EmptyDS.

And thats it, you have your null checkbox

Passing blank value instead of all the values in the dropdown.

There were so many suggested ways of doing this. Only one worked for me.

1) Firstly, we need a new dataset the counts the number of values in the particular parameter. So we create a new dataset called, for example, CountDS.

2) Then ,you click on the data tab, and enter the properties of the dataset where you are passing the report, instead of adding =Parameters!DataSetName.Value, I changed it to the expression with =if(..). For example,

=iif(Parameters!DataSetName.Count>=Paramet<wbr />ers!CountDS.Value<wbr />, "",Parameters!DataSetName.Value

Conclusion

This way, your query/stored procedure doesnt eat up your server's resources with a drop down menu with a lot of values being passed into the Stored Procedure.

Points of Interest

They need to add more functionalities to Reporting Services. If the client is not interested in an application layer, it can become really challenging to give Reporting Services the functionalities of a proper application.

History

Posted February 12, 2008

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
United States United States
I have been a developer for over 3 years specializing in .NET technologies in both Winforms and Webforms.


Comments and Discussions

 
QuestionHi I followed this workaround blank optionset has been created but when i select that nothing returned its empty , please share how to get all records. Thanks in advance Pin
durga6313-Jul-23 17:03
durga6313-Jul-23 17:03 
QuestionSkip the Server Roundtrip for a count Pin
Anna Brown28-Jun-13 7:04
Anna Brown28-Jun-13 7:04 
GeneralMy vote of 5 Pin
Anna Brown28-Jun-13 6:58
Anna Brown28-Jun-13 6:58 
QuestionDeselect all the items in drop-down Pin
dhirenipatel15-Sep-11 0:05
dhirenipatel15-Sep-11 0:05 
GeneralGood job Pin
T.Ashraf5-Sep-08 7:17
T.Ashraf5-Sep-08 7:17 

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.