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
