Reporting Services offers a wonderful array of reporting features, but surprisingly
leaves out one important feature you find in most other reporting tools: check
boxes. Check boxes are vital for all sorts of reports such as surveys, checklists,
etc. Fortunately, there’s a simple solution to this problem and I'll show you
it in this how-to.
The solution is a two-step process. First, you’ll modify your stored procedure
or view (shame on you if you’re pulling data directly out of a table) to return
your check box data in a special way. Then you’ll create your report to display
the data in the desired format.
Modifying the Stored Procedure or View
We’re going to use a CASE statement in our stored procedure (or view) to return special characters instead of a one or zero (if we’ve stored our data as a bit data type) for our check box value. Specifically, we’ll return an "n" for a checked check box, and an "o" for an unchecked check box (note that this is case sensitive). A representative stored procedure definition would look like this:
USE Northwind
GO
CREATE PROCEDURE GetProductList
AS
SELECT Products.ProductName,
Products.UnitPrice,
CASE Products.Discontinued
WHEN 1 THEN 'n'
ELSE 'o'
END AS DiscontinuedCheckBox
FROM Products
ORDER BY Products.ProductName
GO
|
If you’ve stored your data in some other data type (such as a CHAR with either a "Y" or an "N"), simply modify the stored procedure as appropriate. For example:
CASE Products.Discontinued
WHEN 'Y' THEN 'n'
ELSE 'o'
END AS DiscontinuedCheckBox
|
Creating the Report
Now that our stored procedure or view is returning n's or o's, we simply create
our report in the normal manner but with just one small change. The trick is
to use the "Wingdings" font for the check box data. Drag your field onto the
report as normal. Then simply use the Properties window to change the font to
"Wingdings" (see FIGURES 1 and 2).
FIGURE 1: Once we change the font, it scrambles the field name in Layout view.
FIGURE 2: The desired effect occurs once the report is run.
The Character Map applet (Start, Programs, Accessories, System Tools) is where
we determined that "n" and "o" represent a filled in and empty check box, respectively.
You may decide to use other characters such as "x", which puts an X in the box
instead of simply filling it in. Check out the Character Map applet and explore
all the fonts to find the check box that works for you.
Conclusion
Although implementing check boxes aren’t as easy in Reporting Services as
in some other tools, the workaround presented above isn’t too onerous a way
to achieve the desired effect in Reporting Services.