Apress - Pro SQL Server 2008 Reporting Services (2008)02

Apress - Pro SQL Server 2008 Reporting Services (2008)02
Nội dung trích xuất từ tài liệu:
Apress - Pro SQL Server 2008 Reporting Services (2008)02CHAPTER 2■■■Report Authoring: DesigningEfficient QueriesSSRS provides a platform for developing and managing reports in an environment thatincludes multiple data sources of information. These data sources can include both relationaldata (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 systemhas 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’simportant to begin with the heart of any data-driven report—whether it’s Business ObjectsReports, 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. 1718 CHAPTER 2 ■ REPORT AUTHORING: DES IGN ING EFFICIENT QUERIE S Introducing the Sample Relational Database Throughout the book, we’ll show how to design and deploy a reporting solution and build custom .NET SSRS applications for a SQL Server–based health-care application using relational tables and stored procedures. The application was originally designed for home health and hospice facilities that offer clinical care to their patients, typically in their homes. The Online Transactional Processing (OLTP) database that powers this application, and the one we’ll use for examples in this book, captures 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) with instructions available 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, the application has had features added, and the database schema has been altered many times to accommodate the new functionality and to capture data that is required. 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 health-care 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 that was ultimately 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 for the stored procedures you’ll begin using to build reports in the next chapter. 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 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. C HA PTE R 2 ■ ...