Pro SQL Server 2012 Reporting Services

CHAPTER 6  BUILDING REPORTS

You can find these on the Action tab of the report items that support these actions, such as

textboxes, charts, and images.

To demonstrate each of these hyperlink actions, we will use a report that is more suited to hyperlink

actions than the one you have been designing thus far, which already contains one level of interactivity

in the drill-down functionality. The next report, Employee Listing, will provide a simple list of

employees, grouped according to their clinical specialty. Two reports are available in the code download

for the Employee Listing report. One is provided with only the dataset created so that you can step

through the following procedures to create the report. It is called EmployeeListing_Start.rdl. The other

report is a complete version called EmployeeListing.rdl.

You will add the three interactive hyperlink actions to the report to deliver the following features:

Bookmark: When the employee name is selected, the report will jump to a

bookmarked location within the report that contains more details about the

employee, such as the number of patients they have seen.

URL: You will also set up a link to the employee’s department web site, based

on the employee’s discipline or clinical specialty. You will also use a report

parameter that you will set up specifically for the purpose of selecting the

employee’s branch location. When users select a branch location from a drop-

down list provided with the report parameter, they will be taken to their own

department’s intranet site. This functionality is shown for example purposes

only and will not actually take you to an intranet website; that is, unless you

have intranet sites set up for the branches and subfolders for each, containing

the disciplines.

Report: You will add a link to your Employee Service Cost report that will pass

an EmployeelD parameter to limit the results of the linked report. You will use

the same technique in the Employee Service Cost report that will link to a

patient survey form using rich-textbox formatting.

The completed Employee Listing report will contain two Table data regions: one for summary

information and one for detailed information about the employee’s visits. You will add the hyperlink

actions to the summary portion of the report, which will be the first page the user sees. Listing 6-1 shows

the query for the dataset that delivers employee information. For this report, you will limit the

employees to a known set, as shown in the WHERE clause, to keep the report small. You will also add a

date range with two parameters, @DateFrom and @DateTo, which you will create and utilize later. The

EmployeeListing_Start.rdl report contains default values for a date range from January 1, 2007, to the

current date using the Today() function. This query with sample variable settings can be found under the

Queries folder in a script named EmployeeListingQuery.sql.

Listing 6-1. Employee Listing Query

SELECT

RTRIM(E.EmployeeID) AS EmployeeID

, E.LastName

, E.FirstName

, E.EmployeeTblID AS EmpTblID

, E.EmploymentTypeID AS EmploymentType

, E.HireDate

, D.Dscr AS Discipline

, P.LastName AS patlastname

, P.FirstName AS patfirstname

, T.ChargeServiceStartDate