We offer private, customized training for 3 or more people at your site or online.
For many years, SQL Server did not have a good answer for creating attractive reports that summarize information in ways that make sense to business users. Finally, Microsoft shipped SQL Server Reporting Services. Like Notification Services, Reporting Services was originally an add-on for SQL Server 2000, and now it's a part of the core product. In this chapter, you'll learn how to use Reporting Services to produce your own reports.
Reporting Services has a quite a few components that work together seamlessly to provide a complete reporting solution. The full Reporting Services architecture includes development tools, administration tools, and report viewers. There are a number of ways to get to Reporting Services programmatically, including URL, SOAP and WMI interfaces.
Figure 17-1 shows a simplified diagram of the main Reporting Services components that we'll be using in this chapter.
Figure 17-1: Report Server architecture
In this chapter you'll learn about these components:
Reporting Services includes two tools for creating reports:
We'll start our tour of Reporting Services with Report Designer. Report Designer runs inside the Business Intelligence Development Studio shell, and offers several ways to create reports. You can either use the Report Wizard to quickly create a report, or you can use a set of design tools to build a report from scratch. You can also use the design tools to modify a report created with the wizard.
Using the Report Wizard
The easiest way to create a report in Report Designer is to use the Report Wizard. Like all wizards, the Report Wizard walks you through the process in step-by-step fashion. You can make the following choices in the wizard:
To create a simple report using the Report Wizard, follow these steps:
Figure 17-2: Query Builder
Figure17-3: Grouping columns in the report
Figure 17-4 shows the finished report, open in Report Designer.
Figure 17-4: Report created by the Report Wizard
Figure 17-4 shows the main features of Report Designer:
Modifying a Report
Now that you've created a report with the Report Wizard, you can modify it with the Report Designer. If you've used any sort of visual report design tool in the past, you should have no problem making changes here. Among the possibilities here:
To modify the report that you just created, follow these steps:
Figure 17-5: Modified product report
Designing a Report From Scratch
You can also use Report Designer to build your own reports starting from scratch. In general, you'll follow these steps to create a report:
To create a fresh report in Report Designer, follow these steps:
Figure 17-6: Designing a report from scratch
Select the Preview tab to see the report with data.
Creating reports in Business Intelligence Development Studio is good for developers, but it doesn't help users at all. In order for the reports you build to be available to others, you must publish them to your Reporting Services server. To publish a report, you can use the Build and Deploy menu items in Business Intelligence Development Studio. Before you do this, you need to check the project's configuration to make sure that you've selected an appropriate server for the deployment.
You can publish any report, but the first report you created is probably more visually interesting at this point. To publish the first report, follow these steps:
Figure 17-7: Setting the active configuration
Figure 17-8: Setting report project properties
Figure 17-9: Deploying a report
Report Designer gives you one way to create reports for Reporting Services, but it's not the only way. SQL Server 2008 also includes a tool directed at end users named Report Builder. Unlike Report Designer, which is aimed at Developers, Report Builder presents a simplified view of the report-building process and is intended for business analysts and other end users.
Building a Data Model
Report Builder doesn't let end users explore all of a SQL Server database. Instead, it depends on a data model: a preselected group of tables and relationships that a developer has identified as suitable for end-user reporting. To build a data model, you use Business Intelligence Development Studio. Data models contain three things:
To create a data model, follow these steps:
Figure 17-10: Creating entities for end-user reporting
Report Builder itself is a ClickOnce Windows Forms application. That means that it's a Windows application that end users launch from their web browser, but it never gets installed on their computer, so they don't need any local administrator rights on their computer to run it. To get started with Report Builder, browse to your Reporting Services home page. Typically, this will have a URL such as http://ServerName/Reports (or http://localhost/Reports if you're running the browser on the same box with SQL Server 2008 itself). Figure 17-11 shows the Reporting Services home page.
Figure 17-11: Reporting Services home page
To run Report Builder, click the Report Builder link in the home page menu bar. Report Builder will automatically load up all of the available report models and wait for you to choose one to build a report from.
Figure 18-12: New report in Report Builder
The Explorer window to the left of the design surface shows all of the tables in the report model. Beneath that, the Fields window shows the attributes in the currently-selected entity. Note that not everything in this window is a column in the table: the report model also contains aggregate entities such as Total Safety Stock Level and automatically calculated fields.
Figure 17-13: Report in Report Builder
The Web home page for Reporting Services provides a complete interface for managing reports (as well as other objects such as data sources and models) after they are created. This interface, known as Report Manager, is intended primarily for database administrators, but as a developer you should know about its capabilities for managing and modifying reports.
When you click on a report in Report Manager, you'll see the report's data, as shown in Figure 17-14.
Figure 17-14: Report in Report Manager
Note that reports in Report Manager open in a tabbed interface. The four tabs allow you to perform various functions:
Printing and Exporting Reports
When viewing reports in the Report Manager, users can print the reports directly from their browser. The print button in the report toolbar utilizes an ActiveX control for client-side printing. The first time this button is clicked on a given computer, the user is prompted to install the ActiveX control, as in Figure 17-15. After that, the standard Windows print dialog box is displayed for the user to select a printer and paper size, etc.
Figure 17-15: ActiveX install prompt.
Users can also export the report into any of several handy formats. Table 17-1 lists the available export formats.
|XML||Creates a data file in XML format./td>|
|CSV||Creates a comma-delimited text file of report data.|
|Creates an Adobe Acrobat file with the formatted report.|
|MHTML||Creates a Web Archive file with the formatted report.|
|EXCEL||Creates a MS Excel spreadsheet with the formatted report.|
|TIFF||Creates a TIFF graphic of the formatted report.|
|Word||Creates a MS Word document with the formatted report.|
Table 17-1: Export Formats
SSRS 2008 Tutorial: Exercises
Use Report Builder to create a report from the AdventureWorks2008 data model showing the minimum and maximum order quantity for orders taken by each salesperson in the company. You'll find the necessary data in the SalesOrderHeader and SalesOrderDetail tables.
Solutions to Exercises
Figure 17-15: Sales performance report
In-Depth SQL Server Training
For in-depth SQL Server training, click here to view all of Accelebrate's SQL Server training courses for you and your staff.
Our live, instructor-led lectures are far more effective than pre-recorded classes
If your team is not 100% satisfied with your training, we do what's necessary to make it right
Whether you are at home or in the office, we make learning interactive and engaging
We accept check, ACH/EFT, major credit cards, and most purchase orders
New York City
Salt Lake City