Pro SQL Server 2012 Reporting Services

CHAPTER 2  REPORT AUTHORING: DESIGNING EFFICIENT QUERIES

 Note The test server on which we’re developing the reports is an isolated and secure system. Typically,

granting execution privileges to the public role isn’t recommended. We’ll lock down both the stored procedure and

the report in Chapter 11.

You can now test the procedure directly in SSMS with the following command:

EXEC Emp_Svc_Cost

Because you have allowed NULL values for the parameters, you don’t explicitly have to pass them in

on the command line. However, to test the functionality of the stored procedure, you can pass in the full

command line with the appropriate parameters; for example, you can pass all services rendered in

September 2009, like so:

EXEC Emp_Svc_Cost 09, 2009, NULL, NULL, NULL

Executing the procedure in this way returns 893 records in a fraction of a second, and the results

verify that, indeed, only services in September 2009 were returned (see Figure 2-7).

Figure 2-7. Viewing the results of Emp_Svc_Cost with year and date parameters

Summary

In this chapter, you began to design the essential part of a report: the query and stored procedure. By

using stored procedures, you gain the benefits of central administration and security while also gaining

the ability to execute compiled code to return the dataset instead of a stand-alone query. You can

develop queries in conjunction with the report, using the built-in query tools within SSRS. However, it is

considered best practice to deploy the report with a stored procedure whenever possible.

A report request and the target audience are the deciding factors when determining the layout and

default rendering of the report. However, even though reports are often designed to answer a specific