17
■ ■ ■
CHAPTER 2
Report Authoring: Designing
Efficient Queries
SSRS
provides a platform for developing and managing reports in an environment that
includes multiple data sources of information. These data sources can include both relational
data (for example, SQL Server, Oracle, MySQL, and so on) and nonrelational data (for example,
Active Directory, LDAP stores, and Exchange Server). Standards such as ODBC, OLE DB, and
.NET facilitate the retrieval of data from these disparate data stores, so as long as your system
has the relevant drivers, SSRS can access the data. In the SSRS report design environment,
configuring a dataset that drives the report content is the first step of the design process.
However, before we introduce the many elements of the report design environment, it’s
important to begin with the heart of any data-driven report—whether it’s Business Objects
Reports, SSRS, or Microsoft Access—and that is the query. With any report design application,
developing a query that returns the desired data efficiently is the key to a successful report.
In this chapter, we will describe the following:
• The health-care database that is the target of the reporting queries in this book—you
cannot design efficient queries unless you understand the design of the data. We’ll also
describe an easy way to familiarize yourself with your data when the full schema details
are not available.
• How to design basic but effective SQL queries for reporting purposes; we’ll create queries
based on real-world applications, the kind that report writers and database administra-
tors create every day.
• How to use SSMS to gauge query performance; the initial query defines the performance
and value of the report, so it’s important to understand the tools required to create and
test the query to ensure that it’s both accurate and tuned for high performance.
• How to transform the optimized query into a parameterized stored procedure. This
gives you the benefit of precompilation for faster performance and the benefit of the
procedure being centrally updated and secured on SQL Server.

We use the term services to refer to items with an associated cost that are provided for
patient care.
• Services: Stores the names and categories for the detailed line items found in the Trx
table. Services could be clinical visits such as a skilled nurse visit, but they could also
include billable supplies, such as a gauze bandage or syringes.
• ServiceLogCtgry: The main grouping of services that are similar and provide a higher-
level grouping. For example, all visits can be associated with a “Visits” ServiceLogCtgry
for reporting.
• Employee: Stores records specific to the employee, which in this case is the clinician or
other service personnel such as a chaplain visiting a hospice patient. An employee is
assigned to each visit that’s stored in the Trx table.
• Patient: Includes demographic information about the patient receiving the care. This
table, like the Employee table, links directly to the Trx table for detailed transactional data.
• Branch: Stores the branch name and location of the patient receiving the care. Branches,
in the sample reports, are cost centers from where visits and services were delivered.
Landrum_992-6 C02.fm Page 18 Sunday, July 20, 2008 5:40 PM
CHAPTER 2
■
REPORT AUTHORING: DESIGNING EFFICIENT QUERIES
19
• ChargeInfo: Contains additional information related to the individual Trx records that is
specific to charges. Charges have an associated charge, unlike payments and adjustments,
which are also stored in the Trx table.
• Diag: Stores the primary diagnoses of the patient being cared for and links to a record in
the Trx table.
Figure 2-1 shows a graphical layout of the eight tables and how they’re joined.
Figure 2-1. Viewing the sample application’s database tables
Knowing Your Data: A Quick Trick with a Small Procedure
For every report writer, familiarity with the location of the data in a given database can come
only with time. Of course, having a database diagram or schema provided by a vendor is a

CreateFieldInfo.sql.
Listing 2-1. Creating the sp_fieldinfo Stored Procedure
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[sp_FieldInfo]'))
DROP PROCEDURE [dbo].[sp_FieldInfo]
Go
CREATE PROCEDURE sp_FieldInfo
(
@column_name nvarchar(384) = NULL
)
Table 2-1. Output of sp_fieldinfo
Table Name Field Name
PatCertDates PatID
PatDiag PatID
PatEMRDoc PatID
Trx PatID
Patient PatID
Admissions PatID
Landrum_992-6 C02.fm Page 20 Sunday, July 20, 2008 5:40 PM
CHAPTER 2
■
REPORT AUTHORING: DESIGNING EFFICIENT QUERIES
21
AS
SELECT
Object_Name(id) as "Table Name",
rtrim(name) as "Field Name"
FROM
syscolumns
WHERE

Deliver a report that shows the ten most common diagnoses by service count.
Landrum_992-6 C02.fm Page 21 Sunday, July 20, 2008 5:40 PM