Pro SQL Server 2012 Reporting Services

CHAPTER 2  REPORT AUTHORING: DESIGNING EFFICIENT QUERIES

Table 2-2. Sample Output from the Top Ten Diagnoses Query

Patient Count Diagnosis

152 ABNORMALITY OF GAIT

107 BENIGN HYPERTENSION

83 BENIGN HYP HRT DIS W CHF

77 PHYSICAL THERAPY NEC

64 DECUBITUS ULCER

59 DMI UNSPF UNCNTRLD

55 CHR AIRWAY OBSTRUCT NEC

52 ABNRML COAGULTION PRFILE

49 DMII UNSPF NT ST UNCNTRL

43 CONGESTIVE HEART FAILURE

This particular aggregated query has a small result set. Even though it’s potentially working with

tens of thousands of records to produce the resulting ten records, it runs in less than a second. This tells

you that the query is efficient, at least in a single-user execution scenario.

This type of query is designed to deliver data for quick review by professionals who will make

business decisions from the results of the summarized data. In this example, a healthcare administrator

will notice a demand for physical therapy and might review the staffing level for physical therapists in

the company. Because physical therapists are in high demand, the administrator might need to

investigate the cost of caring for physical therapy patients.

Creating an Advanced Query

Next, we’ll show how to design a query that reports the cost of care for the physical therapy patients. The

goal is to make the query and subsequent report flexible enough to include other types of medical

services that can be analyzed as well, not only physical therapy. This query requires more data for

analysis than the previous one did. Because you’ll process thousands of records, you need to assess the

performance impact.

The design process is the same. Begin by adding the necessary tables to the graphical query designer

and selecting the fields you want to include in the report. The required data output for the report needs

to include the following information:

• Patient name and ID number


• Employee name, specialty, and branch