Building Reports in SQL Server 2005

Microsoft .NET Framework, ASP.NET, Visual C# (CSharp, C Sharp, C-Sharp) Developer Training, Visual Studio

This article—Building Reports in SQL Server 2005—is from Pro SQL Server 2005 Reporting Services, by Rodney Landrum, Walter J. Voytek II. Copyright © 2006 Rodney Landrum, Walter J. Voytek II. All rights reserved. Reproduced by permission. This article has been edited especially for C# Online.NET.  Read the book review!

Building Reports in SQL Server 2005

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. So, it is time to put all the pieces together and begin building reports. You can easily apply the concepts 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 available report elements in SSRS.

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, that you have been working with since Chapter 2 to provide the report data. As a reminder, the query returns detail records that represent services, such as a skilled nurse or home health aide visit, performed for patients. 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, the user 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 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.

• Add interactivity to the report with document mapping, visibility, hyperlink actions, and interactive sorting, which is a new feature of SSRS for SQL Server 2005. Both document mapping and hyperlink actions allow the user to navigate to defined locations either within the report or outside the report, such as aWeb site. In this chapter, you will use visibility properties within your report to expand and collapse report items from summary to detail. Interactive sorting gives an SSRS report versatility by allowing it to be sorted, in much the same way Microsoft Outlook allows sorting by clicking column headers.

• Add parameters to the report automatically by changing the dataset from a query to a parameterized stored procedure. You will also add other datasets to populate the parameters defined by the stored procedure.

• Learn how to use multivalued parameters using amodified stored procedure and UDF.

• Add a filter to the Table data region to show only service types that are visits.

• Add a Chart data region for the top ten diagnoses to the report.

• Add the final touches to the report, such as a page header and footer, title, and page numbers.

In addition, as you begin to work more closely with report and query parameters, you will learn how to use a new feature of SSRS for SQL Server 2005: multivalued parameters. As mentioned in previous chapters, multivalued parameters require special consideration when designing the underlying query, so in this chapter you will use amodified version of your stored procedure that takes advantage of a UDF; this will teach you how to best utilize this new and much anticipated feature.

In the preceding chapters, we covered the steps for creating the solution, project, and data source that your report will use, so we will not cover these steps again here. We will, however, show how to use the same data source properties to connect to the health-care database where the data for your report resides. The same database also contains the stored procedure you created in Chapter 2, Emp_Svc_Cost, that you will use later in this chapter.