C H A P T E R 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 non-relational 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 SSRS can access the data as long as your system has the relevant drivers.
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 healthcare database that is the target of the reporting queries in this book:
You must understand the design of the data before you can design efficient
queries. 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 administrators 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 pre-compilation and cached execution plans for
faster performance and the benefit of the procedure being centrally updated
and secured on SQL Server.Introducing the Sample Relational Database
Throughout the book, we’ll show you how to design and deploy a reporting solution and build custom
.NET SSRS applications for an SQL Server-based healthcare 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. Our example for this book, the Online