Displaying Checkboxes In An SSRS Report

Published on
20,424 Points
10 Endorsements
Last Modified:
Editor's Choice
1998: C++ - SQL Server 6.5
2000-2007: C++, VB6, C#, java - SQL Server 7.0-2005
2008-...: SQL Server 2005-2016
2014-2016: MVP Data Platform


On the forums I now and then encounter questions asking for the possibility to display a checkbox control on a Reporting Services report.  And the methods usually presented are either through images or by using a certain font one way or another.  However, as of SQL Server 2008 R2 there is actually a third and interesting alternative which comes really close to actually having a control!

In this article I'll be presenting those three methods.

Screenshots are made using SSRS 2012, and so is the Checkboxes.rdl available for download on my Skydrive.

Checkbox Control, huh?

You may be wondering why people would want to put a checkbox on a report.  After all, reports are not capable of accepting input - except through parameters but that's a different story - and isn't that what a checkbox is all about?

Not entirely.  Reporting Services is not only used for data exploration.  Sometimes people use it to produce data-driven printouts, such as letters or even checklists.  In that perspective, having checkbox control functionality would indeed be useful.

A Silly Scenario

My imagination is failing me a little today so I came up with this silly example: a recipe checklist.  And today we’ll be cooking some pasta sauce!

This is the query that produces the list of ingredients:

select 'Yummy Pasta Sauce' as Recipe, 'zucchini' as Ingredient, 1 as Quantity, 'piece' as Unit, 1 as Needed
union select 'Yummy Pasta Sauce', 'mushrooms', 500, 'g', 1
union select 'Yummy Pasta Sauce', 'minced meat', 1, 'kg', 1
union select 'Yummy Pasta Sauce', 'Brussel sprouts', 1, 'kg', 0
union select 'Yummy Pasta Sauce', 'onion', 2, 'piece', 1
union select 'Yummy Pasta Sauce', 'tomato sauce', 1, 'L', 1
union select 'Yummy Pasta Sauce', 'potato', 1, 'piece', 0

Open in new window

I’m cheating a bit here, all the data is hardcoded in the query.  Normally you’d of course have a database that contains your recipes.

And I’ve also introduced some ingredients which I wouldn’t really want in my sauce, such as Brussels sprouts. Silly, but it gives me a good excuse to use the red checkbox.

Time to explore the possibilities.

Method 1: Images

I will not be going into full detail to explain the usage of images in SSRS reports.  If you need additional information on that, please first read my previous article that covers all possibilities of putting images on reports.

As I don’t have access to an inventory of stock images, I opened my favorite drawing program, Paint.NET, and created two images myself.  Then I embedded both images in the report:

Two images embedded in the report
I created a dataset using the query above.  Then I set up a Table with a group on the Recipe field whilst adding a header row to display the name of the recipe.

The row groups: Recipe > Details
The first column will show the ingredient details, using the following expression:

=Fields!Quantity.Value & " " 
    & Fields!Unit.Value 
    & " of " & Fields!Ingredient.Value

Open in new window

In the second column in the detail level cell, I did a drag & drop of one of my images.  That gives the following pop-up:

Dragging an image into the report brings up the Image Properties      

Clicking the fx button brings up the brings up the Expression editor, in which I created the following expression:

=IIF(Fields!Needed.Value = 1, "checkbox_true", "checkbox_false")

Open in new window

Rendering the report shows us:

Using images to display checkboxes on a report
While easy to implement, a disadvantage to this method is that you need to get hold of the images.  For my example I created them myself to avoid any copyright issues, but I wouldn’t call them fit for professional purposes.  For a recipe list they serve just fine though!

Mission one accomplished: the report shows some image-based textboxes!  On to mission two then!

Method 2: The Wingdings Font

I won’t be going into too much detail on this method either.  It’s already been explained by Krzysztof Kryczka in his article here.

In short, you just use the Wingdings font with the appropriate character to show an image.

In my table, I added an additional column to the right of the last one and entered the following expression:

=IIF(Fields!Needed.Value = 1, Chr(252), Chr(251))

Open in new window

As you can see in the character table on Krzysztof’s site, character 252 is a check mark while 251 is a cross.  Using the Chr() function, I can put the character on the report without typing the letter. (I actually doubt that they even match a letter so typing it would be difficult anyway.)

After setting some font-related properties, here’s what the rendered report looks like:

Using the Wingdings font to show a check mark
A small disadvantage to this method is that the Wingdings font needs to be installed on the SSRS server.

Mission two accomplished!  On to number three!

Method 3: The Indicator Control

Have you already used the Indicator control?  It’s new since SQL Server 2008 R2!

The list of Report Items in the Toolbox contains an Indicator
Let’s create a third column to the right of the last one and drag an Indicator into it.  That brings up following pop-up:

The SSRS Indicator comes in four different types with several different shapes!
In our context here the “3 Symbols (Uncircled)” seem like a valid option, so I’m selecting that.

The next step is to inform the indicator which field it should use.  In our case, it’s the Needed field.  It can be selected by using the dropdown next to (Unspecified).

Telling the Indicator which field is should use
Let’s have a look at the rendered report now:

Using the Indicator control to display check marks!
We’ve used the built-in Indicator control to show check marks, mission three accomplished!

(remark from audience:)
Erm, mister SSRS dude sir, how does the indicator know what image it should show?
(me thinking:)
Darn, they noticed I cheated a little.

You’re right, I have something to confess.  I made use of some handy defaults of the Indicator to skip some steps.

Ready to have some fun?  Add the following line to the query in the dataset:

union select  'Yummy Pasta Sauce', 'Let''s break it!', 1, 'attempt', 100

Open in new window

Without any other changes, render the report:

Oh no, I've broken the Indicator!
Oh my, look at that! The image and font-based check marks are still okay but the Indicator is broken!

Right, next test, in the additional query line added earlier, change the 100 to 2 and render the report:

Now the Indicator is totally broken!
That surely didn’t better the situation, it made it worse!  Don’t worry, the explanation is fairly easy.  Let’s have a look at the actual settings of the indicator.  Open the Indicator properties by right-clicking the indicator and selecting Indicator Properties.  Then select the Value and States page:

The default settings of the indicator
As you can see, the units are measured using percentages with “red” starting at zero while ending at 33.  That means that, based on all available values in the dataset, all values that fall in the first 33% will become red.

And that is exactly what the two tests above demonstrate.  If we take the last one, the value ranges from zero to two.  So zero is 0%, 1 is 50% and 2 is 100%.  That’s why all ones end up as the yellow exclamation mark!

If all you need is a green/red check mark like in our example, you need to ensure that you’ve only got two possible values and the “red” value needs to be lower than the “green” value.  That way you can make use of the default values of the indicator, making implementation a piece of cake.

Alternatively, if you’d like to customize the behavior of the indicator, that’s an option as well.  Have a look at what the Icon dropdown produces:

The Indicator ships with several different icons to be used
Any of those built-in icons can be selected.  Customizing the color is easy too, just use the Color dropdown.  And of course the numeric ranges can be changed as well.

If the percentage-based measurement doesn’t work out well in your situation, you can switch to Numeric:

The indicator can use either Percentage or Numeric measurement
If that hasn’t convinced you yet, then I don’t know what will...

Alright alright, one more:

Putting smileys on your SSRS report


In this article I’ve demonstrated three different methods of implementing checkboxes in SSRS reports.  Contrary to what is currently still believed, it is actually possible to add checkboxes to a report making use of just built-in functionality!

Remember: have fun!


MSDN: Indicators

Originally appeared at my blog: http://blog.hoegaerden.be/2012/08/04/displaying-checkboxes-in-an-ssrs-report/ 

Expert Comment

Great article, thanks. It really helped me with the development of a report I'm currently creating. Personally, I like the webdings solutions the best, as it is the fastest for large reports.
LVL 37

Author Comment

Cool, great to hear!  Good point on the performance: if you need to display really a lot of checkboxes, using just a font will certainly be the fastest to render.
LVL 22

Expert Comment

by:Nico Bontenbal
Thanks ValentinoV. Always nice to learn some new and creative ways to display the data exactly as you want it with SSRS.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 25

Expert Comment

Great stuff. Thanks ValentinoV.
LVL 21

Expert Comment

by:Jason Yousef, MS
Nice article Valentino, good idea.
LVL 37

Author Comment

Thanks guys, glad to hear you found it useful, and I appreciate the comment and vote!
LVL 38

Expert Comment

Very well written article. An excellent author.

"Yes" vote above.
LVL 37

Author Comment

Thank you sir, appreciate it!

Expert Comment

A yes vote from me also, although I don't think I will be attempting to add mincemeat to my pasta sauce anytime soon. ;)

EE Page Editor
LVL 37

Author Comment

Thank you Nata!  How you make your paste sauce is of course up to you, everyone is entitled to his/her own taste.  In my case, I surely like that meat in there :)
LVL 54

Expert Comment

by:Mark Wills
Yep :)

Congratulations !! It was a pretty close contest, but yours emerged as the eventual winner.

Check out the "official" announcement : http://blog.experts-exchange.com/ee-blog/announcing-the-nexus-7-contest-winner-and-other-awesome-articles/

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Join & Write a Comment

Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Did you know PowerShell can save you time with SaaS platforms? Simply leverage RESTfulAPIs to build your own PowerShell modules. These will kill repetitive tickets and tabs, using the command Invoke-RestMethod. Tune into this webinar to learn how…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month