How do I best display CheckBoxes in SQL Server Reporting Services?

Question

One of the many quirks of Reporting Services we've run across is the complete and utter lack of a CheckBox control or even something remotely similar.

We have a form that should appear automatically filled out based on information pulled from a database. We have several bit datatype fields. Printing out "True" or "False" just looks silly, as this is supposed to look like a form that has been auto-filled out, so we want to have a series of checkboxes and labels that are either checked or unchecked.

We are running SSRS 2005 but I'm not aware of SSRS 2008 having added a CheckBox control. Even if it did, we'd need to have an alternative for the time being. The best we've found so far is:

1. use Wingdings
2. use images
3. use text boxes with borders and print a blank/space or a capital X

All three approaches require IIF expression shenanigans.

The Wingdings approach seemed to work acceptably, and was the most aesthetically pleasing except that for whatever reason it didn't always print correctly. More importantly, PDF exports, also for whatever reason, converted all fonts (generally) to Arial and so we got funky letters instead of the Windings dingbats.

Images, being a pixel-based raster, don't do so well when printed along side vector stuff like text. Unless handled carefully, they tend to stretch, pixelate, and do other unprofessional looking things.

While these methods do work (some with limitations as mentioned above) none of them are particularly elegant.

Are we missing something obvious? Not so obvious? Does someone at Microsoft have a good reason why such a control was not provided in SSRS 2000, let alone 2 versions and 8 years later? This can't be the first time this issue has come up...

Scuppernong

SSRS 2000 was released in 2004. So they've had only 5 years to get it sorted. – Pontificate

SQL Server 2012 came out and still no check box only 12 years to not add one simple feature. – Sarcenet

I'd suggest starting or adding to an actual Microsoft discussion or feature response area - they have plenty - rather than complaining here. The reasoning may be what I've seen too much of already: people using SSRS as a business process tool, instead of a reporting tool. It adds a tremendous amount to the TCO and can cause other issues... and really, what report needs a checkbox?! – Priscillaprise

@volvox: any report that needs to report on boolean values... – Thing

Answer 1

I, along with others in my shop, have used images, toggling the hidden attribute based on the field value (true or false). We haven't had any problems with blurring or scaling, unless we tried to increase the scale of the image beyond 100% obviously.

Another option I've used is similar to the wingdings idea, but I just use a plain old "X". On our forms at least, it is not uncommon for someone to use an X in a box instead of a check mark, so it looks completely acceptable. Plus, you don't have to worry about strange characters when printing.

As for why Microsoft does not include a checkbox control, I can't answer that as I've been wondering the same thing myself for a long time now.

Lichen

Answer 2

I just wanna share the idea on this blog. SSRS: How to Display Checkbox on Report

1. First create a textbox
2. Then change the font family to Wingdings

3. Insert an expression on the textbox and write this expressions.
   

   =IIF(Fields!Active.Value,chr(254),"o")
   

   Fields!Active.Value could be anything from your query that should return a boolean value 1 or 0.

4. Then click Preview and see the checkbox ;)

More styles can be selected on the blog that I shared above.

Here is an example of my output

Depurative

This one is the best solution :) – Kerstinkerwin

Answer 3

What I have used to display a check box (or ballot box):
1- create textbox (that will become your check box)
2- change font to Arial Unicode MS
3- in the expression window use:
ChrW(&H2611) for a filled-in checkbox
ChrW(&H2610) for an empty checkbox

Cytokinesis

when I export to pdf it shows the checkbox in a half size. I'm using Chr(0168). – Esthonia

Works with exporting to pdf too! The font Arial Unicode MS was key to it working. Didn't work with standard Arial – Doorkeeper

Answer 4

Besides the different methods already presented, as of SQL Server 2008 R2 there's a built-in control that can be used for checkbox-alike functionality: the Indicator!

Have a look here for details on how to use it: https://web.archive.org/web/20190916105459/http://blog.hoegaerden.be/2012/08/04/displaying-checkboxes-in-an-ssrs-report/

To be able to use a field of type bit, you'll have to cast it to int first. This can be done either in the dataset query or by adding a calculated field to the dataset.

If you want the NULLs to come up as yellow, then you'll need to build the expression that way so it takes that requirement into account as well.

Here's a possible expression for a calculated field:

=Switch(
    IsNothing(Fields!YourBoolean.Value), 50,
    Fields!YourBoolean.Value = False, 0,
    Fields!YourBoolean.Value = True, 100)

Depending on the meaning of your fields - is False good or bad - you may need to swap the zero and 100.

Felic

The indicator seems like it would be a (finally) good fit, but I noticed your example uses integer values. Without pulling up an SSRS project, can you easily use it with a boolean value? What about a nullable one (and get the expected yellow/warning version maybe)? – Scuppernong

I've extended the answer to address your questions, seemed more feasible than replying through comment :) – Felic

Answer 5

Another way to do thisd is go to "Placeholder properties" of TextBox and check Html - Interpret HTML tag as styles

Then in the Value - Expression put this line of code for checked:

="<font face=""Wingdings 2"" color=""green"">" & Chr(81) &"</font>" & "some other text"

Or this code sample for unchecked:

="<font face=""Wingdings 2"" color=""red"">" & Chr(163) &"</font>" & "some other text"

This way you can have checkbox and text in the same textbox.

Later edit:

If you are having problem displaying Wingdings 2 on Azure, then use Wingdings.

Apparently it works.

="<font face=""Wingdings"" color=""green"">" & Chr(253) &"</font>" & "some other text"

Or this code sample for unchecked:

="<font face=""Wingdings"" color=""red"">" & Chr(168) &"</font>" & "some other text"

Elmaelmajian

Answer 6

You can also use a string calculated field like "[X]" or "[ ]". It's less pretty than the textbox with border but you don't have to put a specific control for the value and you can fill table or matrix with this.

At least there is some solution for the checkbox. I'm still looking for full justification for my text (In fact I'm looking for another solution than SSRS know).

ACCESS 97 could make this kind of thing but not SQL SERVER 2012.

Lineolate

Answer 7

I think there is a bug with SSRS and embedding font characters above 128 (some thing todo with ANSI encoding). Basically you can use 1-128 fine, the rest show up as tall rectangular blocks.

I like NY's idea of the textbox with a border and an optional X - this sounds simple and effective.

Apure

That's ultimately what we ended up doing. Nothing else worked in all three formats: Web viewer, printed, and PDF export. I still think they should have had a checkbox control long ago. The fact that SSRS 2008 still doesn't have a good solution after 8 years of Reporting Services is unbelievable. – Scuppernong

Answer 8

This is building on Dragos Durlut's answer. I don't have a high enough reputation to comment but I can answer...

I needed a checkbox as part of text that is passed as a parameter. The parameter contains HTML and is used in a placeholder set up just like Dragos suggests: HTML - Interpret HTML tags as styles.

Instead of having to switch between the HTML and the strings, you can use the HTML Escape Codes (& + # + CharCode + ; --> ¨)

="<font face='Wingdings'>&#168;</font> Empty checkbox"

Since mine is a parameter, it just pass in the string:

<font face='Wingdings'>&#168;</font> Empty checkbox

If you need the checkbox selected, you would pass in either &#253; or &#254; instead:

<font face='Wingdings'>&#253;</font> filled with an x
<font face='Wingdings'>&#254;</font> filled with a checkmark

Lampe