CHAPTER 6 BUILDING REPORTS
- Add interactivity to the report, with document mapping, visibility, hyperlink
actions, and interactive sorting (introduced in SSRS 2005). Both document
mapping and hyperlink actions allow the user to navigate to defined locations
either within the report or outside the report, such as a Website. In this chapter,
you will use visibility properties within your report to expand and collapse report
items from summary to detail. Interactive sorting gives an SSRS report versatility
by allowing it to be sorted in much the same way that Microsoft Outlook allows
sorting by clicking column headers. - Add parameters to the report automatically by changing the dataset from a query
to a parameterized stored procedure. You will also add other datasets to populate
the parameters defined by the stored procedure. - Learn how to use multivalued parameters using a modified stored procedure and
UDF. - Add a filter to the Table data region to show only service types that are visits.
- Add a Chart data region for the top ten diagnoses to the report.
- Add a report variable (introduced in 2008) to use as a constant threshold value.
- Add a gauge control to the report to show threshold information.
- Add a column grouping to the report Table region. Column groupings within the
Table region are part of the new Tablix functionality for the Table, Matrix, and List
data regions. The Tablix functionality was covered in detail in Chapter 4. - Add the final touches to the report, such as a page header and footer, title, and
page numbers.
In addition, as you begin to work more closely with report and query parameters, you will learn how
to use multivalued parameters. As mentioned in previous chapters, multivalued parameters require
special consideration when designing the underlying query. Therefore, in this chapter, you will use a
modified version of your stored procedure that takes advantage of a UDF; this will teach you how to best
utilize this feature.
In the preceding chapters, we covered the steps for creating the solution, project, and data source
that your report will use, so we will not cover these steps again here. We will, however, show how to use
the same data source properties to connect to the health care database where the data for your report
reside. The same database also contains the stored procedure you created in Chapter 2, Emp_Svc_Cost,
which you will use later in this chapter.Creating a Report with the Report Wizard
In many scenarios, the Report Wizard is a fast method for creating a basic report that can be further
enhanced before deployment. The Report Wizard is suitable for reports that are primarily data listings
that do not require much special formatting. In this section, you will step through the Report Wizard to
create the Employee Service Cost report before designing the same report manually.
To open the Report Wizard in your report project, right-click the Reports folder in the Solution
Explorer and select Add New Report. The first wizard screen defines the data source. For this example,
check New Data Source; however, you also have the choice to use a shared data source that has already
been defined as part of the project. Supply the same data source information as you did in the previous