Building Reports in SQL Server 2005—Hyperlink Actions

Microsoft .NET Framework, ASP.NET, Visual C# (CSharp, C Sharp, C-Sharp) Developer Training, Visual Studio

Hyperlink Actions

Having the ability to link one report item, such as the contents of a textbox, to another report or URL adds another valuable level of interactivity in SSRS. By adding hyperlinks to an SSRS report, users can work with the report as they would an application or a Web page, making their tasks more efficient. In this section, you will learn how to add several links or actions to your reports to aid users in linking to other reports and locations, such as a company intranet site.

You can associate three basic actions with values in a report:

• Jumping to a bookmark

• Jumping to a URL

• Jumping to a report

You can find these on the Navigation tab of the report items that support these actions, such as textboxes 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.

Report: You will add a link to your Employee Service Cost report that will pass an EmployeeID parameter to limit the results of the linked report.

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 4-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. Also, you will add a date range with two parameters, @DateFrom and @DateTo, that you will create and utilize later. The EmployeeListing_Start.rdl report contains default values for a date range from January 1, 2003, to the current date using the Now() function.

Listing 4-1. Employee Listing Query

SELECT
   Employee.EmployeeID, Employee.LastName,
   Employee.FirstName,
   Employee.EmployeeTblID,
   Employee.EmploymentTypeID AS EmploymentType,
   Employee.HireDate,
   Discipline.Dscr AS Discipline, Patient.LastName AS
   patLastname, Patient.FirstName AS patFirstname,
   Trx.ChargeServiceStartDate,Discipline.DisciplineID
FROM
   Employee INNER JOIN
   Trx INNER JOIN
   ChargeInfo ON Trx.ChargeInfoID = ChargeInfo.ChargeInfoID ON
   Employee.EmployeeTblID = ChargeInfo.EmployeeTblID INNER JOIN
   Discipline ON Employee.DisciplineTblID =
   Discipline.DisciplineTblID INNER JOIN
   Patient ON Trx.PatID = Patient.PatID
WHERE
   (Trx.ChargeServiceStartDate BETWEEN @DateFrom AND @DateTo)

To begin, open the EmployeeListing_Start.rdl report. The steps to produce the initial basic report as shown in Figure 4-11 are straightforward, with only a few pointers needed. First, you will be using a Table data region again, so simply drag the table to the report area on the Layout tab. Second, add a column to the default three columns of the table. Next, add the following fields onto the detail columns: EmployeeID, LastName, HireDate, and DisciplineID. The employee DisciplineID field references an employee’s clinical specialty, such as Home Health Aide or Skilled Nurse. For the employee name, because the fields have been padded with spaces, you will want to use the rtrim function to concatenate the LastName and FirstName fields into one field that will replace the LastName field that you added to the second column and then change the header to Employee Name.

=rtrim(Fields!LastName.Value) & ", " & rtrim( Fields!FirstName.Value

Figure 4-11. Employee Listing report with hyperlink actions

Additionally, when using dates, the default format is to include the date and time values, even if there is no time associated with the date. The hire date might look like this, for example: 10/20/2003 12:00:00 AM. By selecting the properties of the Hire Date cell, you can change the format from the default to a more standard format, such as 10/20/2003, excluding any time value. The format code for this date is d.

Next, because you are returning detail records, with more than one per employee, you need to group the detail row itself using the value of the Employee_Name field. You can do this by right-clicking the detail row and selecting Edit Group. In the Group On expression field, add the same trimmed employee name as shown in the previous code line. Now when you preview the report, you have your list of employees to which you can add hyperlink actions. Finally, force a page break after this table so you can add a detailed table that will be used as a bookmark link. To add a page break to a report, simply right-click to get to the table properties in the upper left of the table. On the General tab, select Insert a Page Break After This Table.