SSRS - Display Checkboxes in SQL Server Reporting Services

Data visualisation is an art and a science and its primary goal is to communicate information clearly and efficiently via statistical graphics, plots and information graphics. Sometimes, we really want a checkbox displayed on a report. We will learn how to add checkboxes in a column on our SQL Server Reporting Services (SSRS) report.

For the better understanding, we will take an example of week days where current week day should be checked in and other days should be unchecked in our report. We are going to generate dummy data by helping of Common table expression feature of SQL as given below:

Use Demo Go ------ Create on the fly CTE Table ;with CurrentDay as ( select NameDay=DATENAME(DW,getdate()), WeekDay=DATEPART(dw, getdate()) union all select NameDay=DATENAME(DW,getdate()+WeekDay), WeekDay=DATEPART(dw, getdate()+WeekDay) from CurrentDay ----- set weekday conditions where WeekDay<7 ) ------ Pull Data From CTE Table: CurrentDay select NameDay, WeekDay, CurrentDay=IIF(WeekDay=DATEPART(dw, getdate()),1,0)  from CurrentDay order by WeekDay; NameDay WeekDay CurrentDay Sunday 1 0 Monday 2 1 Tuesday 3 0 Wednesday 4 0 Thursday 5 0 Friday 6 0 Saturday 7 0

Now, we have the data table and we want to display this data on the SSRS report 

where current day =1 should be checked in the checkbox and, 

current day=0 should be unchecked in the checkbox as given below:

There are very simple instructions to create this kind of report in SSRS as given below:

In the SQL Server Data Tools environment, open your existing SSRS Project or create new project environment.

In Solution Explorer, right click on the report to add the new report as given below:

In the report wizard window, just past the above SQL query in Query Builder as given below:

Click on the next button and choose tabular radio button as given below:

After clicking next button, fill all the columns in the Details section as given below:

After clicking the next button, click on the finish button after fill the report name as Report with checkbox as given below:

Add Page header to the report and set the report title, background color or report icon as given below:

Preview the report as given below:

Now we have the report having current day values as 1 and 0. Now, we want to change these values with checkboxes and do the following setting against Current Day detail column-

1. Change font to Arial Unicode MS and font size =16
2. Right click on the Current Day and click on Expression as given below:
   
3. In Expression window, we should write the following condition as given below:

=IIF(Fields!CurrentDay.Value=1, ChrW(&H2611), ChrW(&H2610)) Where, ChrW(&H2611) for a filled-in checkbox ChrW(&H2610) for an empty checkbox

After putting the conditional settings for checkboxes, click OK button and preview the report as given below:

Now, we want to fill green color for checked and red color for unchecked checkboxes in the textbox font color property as given below:

=IIF(Fields!CurrentDay.Value=1, "Green","Red")

After putting the conditional settings for font color, click OK button and preview the report as given below:

Same settings, we can do the same setting for background color for the checked and unchecked rows as given below:

Conclusion

We can generate checkboxes in the report by doing some additional expression settings on the textboxes and change font type also. To generate checkboxes, conditional data should be Boolean to write down the simple conditional expression to avoid the complex conditional expressions.