Pro SQL Server 2012 Reporting Services

CHAPTER 13  CREATING REPORTS USING REPORT BUILDER 1.0, 2.0, AND 3.0

that provides the desired fields from several tables in the Pro_SSRS database. Like the reports you will build
with Report Builder 1.0, this stored procedure and report were created based on feedback from users who
needed it. One objective when creating the stored procedure was to design it not only to address the
requirements for one report, but also to be versatile enough to be reused for other similar reports. We will
cover this issue when we show how to build a report model to meet the needs of the users and the report
designers who will be creating and publishing the reports with Report Builder 1.0.
In the health-care industry (namely, in post-acute care such as home health and hospice), you have
to follow many reporting requirements for state and federal agencies. From cost reports to patient
admission and discharge information, different reports are needed by different departments for different
reasons. One group of reports that is used by every department is the patient census. This report shows all
the patients who are currently admitted to the health-care facility and who are receiving services. The
report can be as simple as an alphabetized listing of patient names or as detailed as all the demographic
information about each patient, such as where they live and who to contact in case of an emergency.
Since the patient census is the most frequently requested report for customization, we will use it as
the example in this chapter for the report model, making sure we provide enough data to meet both
current and future needs.
For the patient census report example, imagine that users have requested to see a daily total of
patients who have been admitted to the facility. Such patients all have common data associated with
them that is important when looking at a census. The information answers these types of questions:

• What is the patient’s primary diagnosis?


• What branch office is the patient currently admitted to?


• What is the patient’s length of stay in the facility?


• How many active patients are in each branch currently?


• What is the patient’s address, phone number, and other key demographic
  information? (This type of census report, often referred to as a face sheet, shows a
  lot of information about a patient in a compact location.)


• If the patient was discharged, when did that occur and what was the reason? Is it
  because they recovered, or did they transfer to a hospital?

 Note The type of information we are discussing in this chapter is considered to be Protected Health Informa-

tion (PHI) data, which, thanks to HIPAA, needs to be tightly guarded and accessible only to those who are permitted to

use it. Chapter 11 covers how to secure access to data delivered through SSRS.

You can answer all of these questions and more with a single query, which can form the foundation
of the report model. So, in the Pro_SSRS_2008R2 database, you will join several tables into a single data
source view to provide the information needed to deliver the patient census. You have used most of
these tables already when developing other reports, but we will review the database schema of the
associated tables before showing how to build the report model. These are the tables you will use in this
chapter’s example:

• Admissions