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...
Give the parameter a name (pSalaried)
Prompt: is Salaried?
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
Right click on the dataset > Dataset Properties...
Go to Filters, Add new expression [BaseRate]
in the Value field specify true
Click on the fx button to build an expression
=(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.
