SSRS: Boolean Parameter

Data source: ContosoRatailDW

Date set: DimEmployee

Fields that can be used for the Boolean parameter must have bit data type . Alternatively, you can create a Calculated Field in SSRS which returns true or false.

SELECT

[FirstName]

,[LastName]

,[HireDate]

,[BirthDate]

,[MaritalStatus]

,[SalariedFlag]

,[Gender]

,[PayFrequency]

,[BaseRate]

,[VacationHours]

,[CurrentFlag]

,[SalesPersonFlag]

,[DepartmentName]

FROM [ContosoRetailDW].[dbo].[DimEmployee]

This T-SQL code is not using any parameter in the WHERE clause. I will have to create the parameter first and then reference the parameter in FILTERS page of the dataset (Query).

You can notice that the BIT data type is displayed as TRUE or FALSE value in SSRS.

Step 1

Add Parameter...

1. Give the parameter a name (pSalaried)

2. Prompt: is Salaried?

3. Data type: Boolean

Step 2

I will specify Available Values YES for TRUE and NO for FALSE. So instead of TRUE and FALSE, the dropdown box will display YES and NO.

Step 3

I need to reference the parameter in FILTERS page of the dataset.

Open the dataset properties. In Expression find the dataset field with the BIT data type, set the data type to Boolean.

Step 4

In the Value field click on the fx symbol to build the expression. Go to Category > Parameters. Find the parameter (pSalaries), double click on it to add as an expression. What I wrote is : WHERE SalariedFlag = @pSalaried . I want to check if the value in SalariedFlag is equal to my @pSalaried parameter. If it is true, corresponding records will be returned.

Report preview

(I could remove the fields with the BIT data type)

Step 5

To convert the dropdown box to Radio Button: Double click on the @pSalaried parameter > Available Values > None

Step 6

Allowing NULL value

Creating an optional parameter by allowing NULL value

Double click on the parameter name > General > check Allow null value

Go to Dataset Properties to change the FILTERS

Step 7

Change Value to true

Step 8

Click on the fx symbol in the Expression field and write an expression. Make sure, after you finish this, data type must be Boolean.

=Fields!SalariedFlag.Value = Parameters!pSalaried.Value OR Parameters!pSalaried.Value IS NOTHING

SQL interpretation :

(WHERE SalariedFlag = @pSalaried OR @pSalaried is NULL)

Report Preview

Parameter is optional

Demo 2

I want to create another optional parameter that will display all the Employees with BaseRate value >25.00

Step 1

Right click on the dataset > Add Calculated Field...

Create a new field, give it some name (IsOverpaid)

Click on the fx button

Build an expression (=Field!BaseRate.Value > 25.00) This can return only two values (true or false)

Report preview

I created a calculated field, it is a Boolean data type.

Now I need to create a parameter and reference the isOverpaid column value to that parameter.

Step 2

Create a parameter type Boolean

check: Allow null value

Step 3

Reference the parameter in FILTERS page of the dataset

1. Right click on the dataset > Dataset Properties...

2. Go to Filters, Add new expression [BaseRate]

3. in the Value field specify true

4. Click on the fx button to build an expression

5. =(Fields!BaseRate.Value > 25.00) = Parameters!pBaserate.Value OR Parameters!pBaserate.Value IS NOTHING

Report preview

I have 2 optional parameters, if null are checked all the data have been returned.