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.
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: sql server 2008,
ssrs
Categories: SSRS |
SQL Server Reporting Services
e119af5f-3d03-4e17-af7d-21a684a78058|0|.0