14-Creating a Checkbox List to Show Existing Records

Paginated Reports eBook Introduction, Resource and Sample Data Downloads

A checkbox list (or tickbox list if you grew up with the Queen’s English) is a familiar metaphor, used to display the state of items in a list. There are several practical uses for this style of report and this is one of two different recipes to use this checkbox list metaphor. In this example, you will see how to display a list of records with checkboxes. Items or rows that have corresponding records in another table will display a checked box and those that do not have related records will show an empty or unchecked box. The technique uses images for the checked and unchecked icons and you can use the same pattern to show any image or icon of your choice to show any number of states or conditions.

Recipe Ingredients

Product Versions

  • All versions of Reporting Services are supported

What You’ll Need

  • A table data region
  • Checked box and Unchecked box images
  • An expression used to manipulate the image value

Contributed by Paul Turley

Designing the Report

An example of the finished report for this recipe is shown in Figure 1.

Figure 1

To apply this recipe, you need a query that returns a set of records from one table and corresponding values from existing records in a related table. If a related record doesn’t exist, a Null value is returned for the corresponding column. The query shown in the code below returns a set of product names and only a ProductKey value for records that have Internet sales orders.

  1. Add a data source to the report for the SSRS2019Recipes sample database
  2. Add a dataset to the report, using the following T-SQL query:
select

p.ProductName

, s.ProductKey

from

Product p

left outer join Sales s on p.ProductKey = s.ProductKey

group by p.ProductName, p.ProductKey

order by p.ProductName

;
  1. Capture or create two image files: an unchecked checkbox and a checked checkbox. The images supplied with this recipe solution are shown in Figure 2.

Figure 2

An easy way to create these files is to open an application that displays checkbox icons in both states and use a screen capture tool to capture each of the two checkbox states to separate image files. You can use the Windows Snipping tool or a product like SnagIt Pro from TechSmith. Of course, you can also create your own images using MS Paint, PhotoShop, or any other graphic editing program. You can save these using any of the image formats supported by Reporting Services, including BMP, GIF, JPG, PNG, or TIF. This example uses embedded images, but the technique can be applied to external files or project resources with the appropriate modifications.

  1. Save these two files with the names “checked” and “unchecked.”
  2. Add the files to the report as embedded images. If you are working with any version of Reporting Services since 2008, right-click the Images folder in the Report Data window and choose Add Image. If you are working with Reporting Services 2000 or 2005, use the Report Properties dialog to add embedded images to a report.

Figure 3 shows a design view of the report in Report Builder. The two image files are added as embedded images and appear under the Images branch of the tree in the Report Data pane.

Figure 3

NOTE: When using images, keep in mind that simpler is better. Resizing the images as small as possible before you import them will help Reporting Services render them quickly and efficiently. You can use the Sizing property to rescale the image at the cost of performance.

  1. Add a table data region to the report.
  2. Add ProductName to the second column of the detail row in the table.
  3. Add an image report item to the first column of the detail row in the table.

Use the View menu in Report Builder to enable the Properties window, which you will use to set these properties.

  1. Set the Value property of the image using the following expression:

=IIF(IsNothing(Fields!ProductKey.Value), “unchecked”, “checked”)

  1. Set the MIMEType property to match the file type. In this case, image/jpeg.
  2. Set the Source property to Embedded.
  3. Set the Sizing property to FitProportional.

When the report is rendered, this expression is applied to the image in each row of the table. Since the query uses an outer join between the Product and Sales tables, all product records in the Product table are returned. Products with related sales records will return a valid ProductKey value from the Sales table, but products that have no sales will return a Null value for this column. The Visual Basic IsNothing function is used to make this determination and then the IIF function is used to return the corresponding image name based on whether a related sales record exists.

Final Thoughts

In this recipe, you saw how to display a checked box to indicate the state of a record when it has corresponding records in another table. You can extend this by adding multiple images to indicate several different states. This example is a simple, static view of data, but your technique can easily be modified and enhanced, as you will see in a more interactive and complex recipe titled “Using a Checkbox List to Select and Deselect Records.”

%d bloggers like this: