C H A P T E R 6
Building Reports
In the previous chapter, you laid the foundation for your first report by creating a query and subsequent
stored procedure. You also learned about the fundamental elements used to build reports and are now
familiar with the design environment. Now, it is time to put all the pieces together and begin building
reports. You can easily apply the concepts introduced in this chapter to any company that uses SQL
Server and relational database systems. This chapter will focus primarily on creating a reporting solution
based on data from a SQL Server health care database; it will use many of the report elements that have
been available since SSRS’s inception in SQL Server 2000 through 2012. SSRS 2008 and 2008 R2
introduced many new features such as report variables, enhanced charting, and data visualizations. The
most significant additions were the Tablix data regions and dashboard style elements like Sparklines,
Data Bars, and the Indicator. We are excited to incorporate these significant and long-awaited
enhancements into the reports featured in this book.
The report you will be creating in this chapter is called the Employee Service Cost report. This report
will utilize the same query and stored procedure, Emp_Svc_Cost, which you have been working with
since Chapter 2 to provide the report data. As a reminder, the query returns detail records that represent
services performed for patients, such as visits by skilled nurses or home health aides. Each type of
service has an associated cost for the health care company. This report, when complete, will show
important cost points based on associated data provided by the query, such as the patient’s diagnosis,
the employees who performed the services, the date of each service, and the branch location of the
patient. By grouping and sorting the report at these cost points, you will be able to see the cost of
services from the individual patient all the way up to the branch location, which might serve hundreds of
patients. You will group and calculate the cost amount at each level.
Specifically, in the following sections, you will create the Employee Service Cost report initially with
the Report Wizard, which produces a report based on predefined selections, and then from scratch. We
will show the process of using the wizard for demonstration purposes only and therefore will not
continue with the report that it produces. For the report that you build from scratch, you will add all the
features that the Report Wizard can add, plus much more. The following list highlights the design goals
for the Employee Service Cost report:
- Step through adding a base report that uses the Table data region based on the
dataset you defined for the Emp_Svc_Cost query. - Add several basic formatting elements to the report.