In a previous article called Check
Boxes for Reporting Services Reports, I showed you how to implement check
boxes in Reporting Services. This article will detail a way to display radio
buttons in your reports. Radio buttons are commonly used in surveys for things
such as displaying a selected ranking from 1 to 10 or from Strongly Agree to
Strongly Disagree.
The Solution
To achieve this, you’ll use a two-step process. First, you’ll modify your stored procedure or view to return one column for each possible value in the column that needs to be transformed into a radio button list. Then you’ll create your report to display the data in the desired format.
Modifying the Stored Procedure or View
Unfortunately, neither the Northwind nor Pubs sample databases have any data
that would help us appropriately illustrate this solution. Therefore, we’ll
need to build and populate a small table for illustrative purposes:
CREATE TABLE tSurvey
(
CustomerName varchar(30) NOT NULL,
WouldRecommend varchar(2) NULL
)
GO
INSERT INTO tSurvey (CustomerName, WouldRecommend) VALUES ('Trent Miesner', 'A')
INSERT INTO tSurvey (CustomerName, WouldRecommend) VALUES ('Bill Gates', NULL)
INSERT INTO tSurvey (CustomerName, WouldRecommend) VALUES ('George Bush', 'SA')
INSERT INTO tSurvey (CustomerName, WouldRecommend) VALUES ('Bill Clinton', 'SD')
GO
|
Now we’ll create the stored procedure (a view would also work). Our stored
procedure will use a CASE statement to return an “l”
(lowercase "L") to represent a selected radio button and an “m” to
represent a non-selected radio button (more on this in a moment). Unfortunately,
we need to create on case statement for each possible value in our WouldRecommend
column (except for NULL, since for NULL we want to leave all radio buttons unselected):
CREATE PROCEDURE GetCustomerSurvey
AS
SELECT tSurvey.CustomerName,
CASE tSurvey.WouldRecommend
WHEN 'SA' THEN 'l'
ELSE 'm'
END AS WouldRecommend_SA,
CASE tSurvey.WouldRecommend
WHEN 'A' THEN 'l'
ELSE 'm'
END AS WouldRecommend_A,
CASE tSurvey.WouldRecommend
WHEN 'SD' THEN 'l'
ELSE 'm'
END AS WouldRecommend_SD
FROM tSurvey
ORDER BY tSurvey.CustomerName
GO
|
Creating the Report
Now we can create our report. We’ll drag the WouldRecommend_SA, WouldRecommend_A, and WouldRecommend_SD fields onto the report. Then, we’ll use the Properties window to change the font to “Wingdings.” The letter “l”, rendered as a Wingding, becomes a selected radio button while “m” is a non-selected radio button.
Make sure you put the right data field in the right place. WouldRecommend_SA
goes in the Strongly Agree column, _A in the Agree column, and _SD in the Strongly
Disagree column.
Once we change the font, it scrambles the field name in Layout view...
...but it has the desired effect once the report is run.
The Character Map applet (Start, Programs, Accessories, System Tools) is where
we determined that “l” and “m” represent a selected and non-selected radio button
respectively. Feel free to experiment with other icons.
Conclusion
Producing radio buttons and check boxes in Reporting Services isn’t as simple as dropping a control onto the report. Fortunately, the simple steps shown in this article and in Check Boxes for Reporting Services Reports provide an easy way to achieve the desired result.