CHAPTER 6 BUILDING REPORTS, D.DisciplineID
, P.PatID
FROM
Trx AS T
JOIN ChargeInfo AS CI ON T.ChargeInfoID = CI.ChargeInfoID
JOIN Employee AS E ON E.EmployeeTblID = CI.EmployeeTblID
JOIN Discipline AS D ON E.DisciplineTblID = D.DisciplineTblID
JOIN Patient AS P ON T.PatID = P.PatID
WHERE
(T.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 6-14 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 Design surface. When you add
a table, BIDS automatically generates three columns. Add another column to the table. Next, add the
following fields onto the detail columns: EmployeeID, LastName, HireDate, and Discipline. The
employee Discipline field references an employee’s clinical specialty, such as Home Health Aide or
Skilled Nurse. Now that we have a start, let’s edit the Lastname field to make it a concatenated value for
Employee Name using the LastName and FirstName columns separated by a comma (Ex. LastName,
FirstName). Right click on the Lastname textbox in the details row and select Expression. Because the
first and last name fields have been padded with spaces, you will want to use the RTRIM function to
remove the extra spaces. The expression for the last name text box should look like this:
=RTRIM(Fields!LastName.Value) & ", " & RTRIM(Fields!FirstName.Value)
Now that you have combined LastName and FirstName into one, change the header from Last
Name to Employee Name. Make the entire header row of the table Bold. Finally, let’s change the
Employee column to look a little more like a hyperlink. Select the EmployeeID detail field and then set
text to be underlined and the font color to blue.