Click here to Skip to main content
Click here to Skip to main content

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

, 12 Feb 2008
Rate this:
Please Sign up or sign in to vote.
If you have a long list of drop down items and you want to pass in one value, you can use this method

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>=Parameters!CountDS.Value, "",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)

About the Author

Shobhit Dixit
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

 
GeneralGood job PinmemberT.Ashraf5-Sep-08 7:17 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 12 Feb 2008
Article Copyright 2008 by Shobhit Dixit
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid