CHAPTER 2 REPORT AUTHORING: DESIGNING EFFICIENT QUERIES
Transactional Processing (OLTP) database, powers this application, capturing billing and clinical
information for home health and hospice patients. The database that we will use is called Pro_SSRS and
is available for download in the Source Code/Download area of the Apress Web site
(http://www.apress.com), together with instructions in the ReadMe.txt file on how to restore the database
in preparation for use in this and subsequent chapters.Introducing the Schema Design
Over the years, developers have added features to the application and altered the database schema
many times to accommodate the new functionality and to capture required data. This data is needed not
only to perform operational processes such as creating bills and posting payments to the patient’s
account, but also to provide valuable reports that show how well the company is serving its patients.
Because these types of healthcare facilities offer long-term care, our customers need to know if their
patients’ conditions are improving over time and the overall cost of the care delivered to them.
The database designed for the application consists of more than 200 tables and has many stored
procedures. In this book, you’ll use a subset of that database to learn how to develop reports that show
the cost of care for patients. You’ll use eight main tables for the queries and stored procedures
throughout the book, and you’ll begin using some of them to build reports as you work through the next
three chapters. These tables are as follows:Trx: The main transactional data table that stores detailed patient services
information. 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
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, in this case a clinician or
other staff member such as a chaplain visiting a hospice patient. An employee
is assigned to each visit 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 which visits and services
were delivered.Chargelnfo: 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.