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.
SSRS 2008 Tutorial: The Reporting Services Architecture
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:
SSRS 2008 Tutorial: Using Report Designer
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:
Try It!
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:
Try It!
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:
Try It!
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.
SSRS 2008 Tutorial: Publishing a Report
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.
Try It!
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
SSRS 2008 Tutorial: Using Report Builder
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:
Try It!
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.
Try It!
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
SSRS 2008 Tutorial: Using Report Manager
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.
Export Format | Handles |
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 TrainingFor 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
Alabama
Birmingham
Huntsville
Montgomery
Alaska
Anchorage
Arizona
Phoenix
Tucson
Arkansas
Fayetteville
Little Rock
California
Los Angeles
Oakland
Orange County
Sacramento
San Diego
San Francisco
San Jose
Colorado
Boulder
Colorado Springs
Denver
Connecticut
Hartford
DC
Washington
Florida
Fort Lauderdale
Jacksonville
Miami
Orlando
Tampa
Georgia
Atlanta
Augusta
Savannah
Hawaii
Honolulu
Idaho
Boise
Illinois
Chicago
Indiana
Indianapolis
Iowa
Cedar Rapids
Des Moines
Kansas
Wichita
Kentucky
Lexington
Louisville
Louisiana
New Orleans
Maine
Portland
Maryland
Annapolis
Baltimore
Frederick
Hagerstown
Massachusetts
Boston
Cambridge
Springfield
Michigan
Ann Arbor
Detroit
Grand Rapids
Minnesota
Minneapolis
Saint Paul
Mississippi
Jackson
Missouri
Kansas City
St. Louis
Nebraska
Lincoln
Omaha
Nevada
Las Vegas
Reno
New Jersey
Princeton
New Mexico
Albuquerque
New York
Albany
Buffalo
New York City
White Plains
North Carolina
Charlotte
Durham
Raleigh
Ohio
Akron
Canton
Cincinnati
Cleveland
Columbus
Dayton
Oklahoma
Oklahoma City
Tulsa
Oregon
Portland
Pennsylvania
Philadelphia
Pittsburgh
Rhode Island
Providence
South Carolina
Charleston
Columbia
Greenville
Tennessee
Knoxville
Memphis
Nashville
Texas
Austin
Dallas
El Paso
Houston
San Antonio
Utah
Salt Lake City
Virginia
Alexandria
Arlington
Norfolk
Richmond
Washington
Seattle
Tacoma
West Virginia
Charleston
Wisconsin
Madison
Milwaukee
Alberta
Calgary
Edmonton
British Columbia
Vancouver
Manitoba
Winnipeg
Nova Scotia
Halifax
Ontario
Ottawa
Toronto
Quebec
Montreal
Puerto Rico
San Juan