Pro SQL Server 2012 Reporting Services

CHAPTER 2  REPORT AUTHORING: DESIGNING EFFICIENT QUERIES

AS
SELECT
TABLE_NAME AS [Table Name]
, RTRIM(COLUMN_NAME) AS [Field Name]
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME LIKE '%' + @column_name + '%'

Introducing Query Design Basics

Whether you’re a seasoned pro at writing SQL queries manually through a text editor or someone who
prefers to design queries graphically, the result is what matters. Accuracy, versatility, and efficiency of
the underlying query are the three goals that designers strive to achieve. Accuracy is critical, but a query
that performs well and is versatile enough to be used in more than one report makes the subsequent
report design task much easier. For scalability and low response times, efficiency is paramount. A great
report that takes 15 minutes to render will be a report your users rarely run. Keep the following goals in
mind as you begin to develop your report queries:

The query must return accurate data: As the query logic becomes more

complex, the chance of inaccuracy increases with extensive criteria and

multiple joins.

The query must be scalable: As the query is developed and tested, be aware that

its performance might change radically as the load increases with more users.

We cover performance monitoring with simulated loads in Chapter 10.

However, in this chapter we’ll show how to use tools to test query response

times for a single execution in order to improve performance.

The query should be versatile: Often a single query or stored procedure can drive

many reports at once, saving on the time it takes to maintain, administer, and

develop reports. However, delivering too much data to a report at once, to

support both details and a summary, can affect performance. It’s important to

balance versatility with efficiency.

Creating a Simple Query Graphically

Query design typically begins with a request. As the report writer or database administrator (DBA),
you’re probably often tasked with producing data that’s not available through the standard reports often
delivered with third-party applications.
Let’s begin with a hypothetical scenario. Say you receive an e-mail that details a report to be created
and deployed for an upcoming meeting. It has already been determined that the data is unavailable from
any known reports, yet you can derive the data using a simple custom query.
In this first example, you’ll look at the following request for a healthcare organization:

Deliver a report that shows the ten most common diagnoses by service count.