Creating a boolean select box in an SSRS Report.

Posted on 15. November 2009 01:58 by David Knipper|

I’m currently working on porting a large legacy classic asp application to asp.net. The classic asp application contains "reports" written in classic asp, so I’m porting that report section into SQL Server Reporting Services (SSRS 2008). One of the "reports" I encountered contained a SELECT statement such as the following.

Visual Basic-Code: 
dim sql
sql = "SELECT * FROM TABLE "

if condition = "True" then
     sql = sql & " WHERE (MyBoolean = 1)"
end if
if condition = "False" then
     sql = sql & " WHERE (MyBoolean = 0)"
end if

In this particular query, "condition" can also contain a value for "True or False" where all records are selected. I’m still a novice to Sql Server Reporting Services so I was not sure how to handle this issue. If I create a boolean parameter in my report, I get a set of radio buttons that I could not configure. The following method would not work:

The not-working parameter setup:



The unwanted result:



I looked on the web for creating a Boolean parameter select box with no luck, so I came up with this solution. I first had to ditch the idea of using the Boolean parameter. Then I figured, why not just treat this parameter as an integer rather than a Boolean?

First, set your integer parameter as follows. If you want a value for "True or False" (aka "All Records"), make sure you check you check the "Allow multiple values" checkbox.



Next, setup your default values as follows:



The result:



I’m not sure is this method will work with other databases other than SQL Server, but maybe it will. Happy reporting.

Tags: ,
Categories: SSRS | SQL Server Reporting Services

Permalink E-mail | Kick it! | DZone it! | del.icio.us Comments (0) Post RSSRSS comment feed