SQL Server 2005 Reporting Essentials

Next Page

Book Cover
SQL Server 2005 Reporting Essentials
By Bill Hamilton
...............................................
Publisher: O'Reilly
Pub Date: November 01, 2006
Print ISBN-10: 0-596-52941-4
Print ISBN-13: 978-0-59-652941-3
Pages: 80

Table of Contents
Overview

SQL Server 2005 Reporting Services allows you to design, create, view, and manage reports. Reports can be distributed and managed over the Web. End users can create, publish, and share ad hoc reports-all using web-based tools.

This book shows you how to use Report Builder, a tool that lets information workers build ad hoc reports, and how to use Report Designer, a report development tool hosted in Visual Studio that lets you interactively create reports. This book shows you how to publish, manage, and run those reports.

This book concludes by showing the three mechanisms used by developers to integrate reports into applications: form controls, parameterized URLs, and web services.

Next Page


Table of Contents

Previous Page

Book Cover
SQL Server 2005 Reporting Essentials
By Bill Hamilton
...............................................
Publisher: O'Reilly
Pub Date: November 01, 2006
Print ISBN-10: 0-596-52941-4
Print ISBN-13: 978-0-59-652941-3
Pages: 80

Table of Contents

Copyright
Preface
Chapter 1. Getting Started
Chapter 2. Set Up and Configuration
Chapter 3. Key Concepts
Chapter 4. Report Manager
Chapter 5. Report Designer
 | Section 5.1. Create a Report Server Project
 | Section 5.2. Create a Report
 | Section 5.3. Publish a Report
Chapter 6. Report Model Designer
 | Section 6.1. Create a Data Source
 | Section 6.2. Create a Data Source View
 | Section 6.3. Create a Report Model
 | Section 6.4. Publish a Report Model
Chapter 7. Report Builder
 | Section 7.1. Create a Data Source
 | Section 7.2. Create a Report Model
 | Section 7.3. Create a Report Layout
 | Section 7.4. Publish (Save) a Report
 | Section 7.5. Run a Report
 | Section 7.6. Open a Saved Report
 | Section 7.7. Report Snapshots and History
 | Section 7.8. Subscriptions
Chapter 8. Integrating Reports into Applications
 | Section 8.1. URL-based
 | Section 8.2. Report Viewer Control
 | Section 8.3. Web Services

Previous Page


Copyright

Copyright © 2006, O'Reilly Media, Inc.. All rights reserved.


Preface

SQL Server 2005 Reporting Services lets you create, manage, and deliver a variety of report types that contain data from relational and multidimensional data sources as well as any data source that has a .NET Framework managed data provider, an OLE DB data provider, or an ODBC provider.

You can create tabular, matrix, chart, and free-form report layouts. Graphical elements can be incorporated into the reports and the reports can be nested to provide drill down functionality. Document maps and bookmarks can be added to facilitate navigation in large or complex reports.

Reporting Services export formats include HTML, MHTML, PDF, Excel, XML, CSV, and image (TIFF).

You can deliver reports using a URL, using the report viewer control in either a Windows or Web application, or through a Web service. Report delivery options include: on-demand, through SharePoint sites including SharePoint Web parts, automated reports, subscription reports, and automated reports based on data.

Reporting Services has two report authoring tools:

  • Report Designer is a report-authoring application hosted in Visual Studio that lets developers define, preview, and publish reports interactively.

  • Report Builder is a report-authoring application, built on familiar Microsoft Office paradigms, that lets users create ad hoc reports based on published report models. Report models make it easy for end users to navigate and use data stored in a database or other data source, eliminating the need for understanding of concepts such as tables, views, fields, and relationships.

Reporting Services is an open and extensible reporting platform. The API lets you deploy, install, and manage Reporting Services components to support custom data, delivery, rendering, and management requirements. Reporting Services includes configuration, monitoring, and management tools.

This book introduces the most important aspects of Reporting Services. You will learn how to design, define, deploy, deliver, and manage reports.


Chapter 1. Getting Started

Each section in this book walks you through a complete example that first accomplishes a simple task and builds on that to illustrate additional and more advanced features. Although you don't have to, it is a good idea to actually work through the examples to get the most out of this book. You should be able to mimic these examples using your own reporting projects. Some of the examples in this book use the AdventureWorks databases and sample reports. All reports that you develop in the examples in this book use the AdventureWorks database.

The samples and samples databases are not installed by default during SQL Server installation. You can install samples by selecting them in the Advanced area of the Components to Install page in the setup wizard. You can also install them after installing SQL Server by following the instructions in the topic Running Setup to Install AdventureWorks Sample Databases and Samples in SQL Server Books Online.


Chapter 2. Set Up and Configuration

Follow the instructions that ship with SQL Server to install Reporting Services in your environment. The examples in this book assume that the SQL Server installation is on a local computer with the default configuration. The examples also assume that the login account has administrative permissions on the local computer.

Use SQL Server Configuration Managera tool used to manage services associated with SQL Serverto ensure that SQL Server and SQL Reporting Services services are running. Start Configuration Manager by selecting All Programs [image] Microsoft SQL Server 2005 [image] Configuration Tools [image] SQL Server Configuration Manager from the Start menu. Select the SQL Server 2005 services Node in the left pane to view the state of each SQL Server 2005 Service as shown in the following figure.

Figure 2-1. SQL Server Configuration Manager

Ensure that Reporting Services are configured properly. To do so, start Reporting Services Configuration Manager by selecting All Programs [image] Microsoft SQL Server 2005 [image] Configuration Tools [image] Reporting Services Configuration from the Start menu. The Reporting Services Configuration Manager Server Status page is shown in the following figure.

Figure 2-2. Reporting Services Configuration Manager

Ensure that none of the items in the left pane are marked Not configured. Items marked Optional configuration or Recommended configuration don't need to be configured. Consult SQL Server Books Online for help using Configuration Manager.

Once you've checked that Reporting Services is correctly deployed, you can deploy the sample reports. Follow these steps to extract, compile, and deploy sample reports to the report server:

  1. Run the wizard that extracts the SQL Server samples (including reports) by selecting All Programs [image] Microsoft SQL Server 2005 [image] Documentation and Tutorials [image] Samples [image] Microsoft SQL Server 2005 Samples from the Start menu.

    NOTE

    Business Intelligence Development Studio is a Microsoft Visual Studio 2005 tool that includes project types that are specific to SQL Server 2005. Business Intelligence Studio is the main tool used to develop Analysis Services, Integration Services, and Reporting Services projectsit supplies templates, designers, tools, and wizards specific to each project type.

    If you install Visual Studio 2005, the Business Intelligence Development Studio project types will be added to Visual Studio 2005. Launching Business Intelligence Development Studio will launch Visual Studio 2005.

  2. Open Business Intelligence Development Studio by selecting All Programs [image] Microsoft SQL Server 2005 [image] SQL Server Business Intelligence Development Studio from the Start menu.

  3. Open the AdventureWorks sample reports. Select File [image] Open [image] Project/Solution from the main menu to display the Open Project dialog. Open the solution file C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports\AdventureWorks Sample Reports.sln.

  4. Change the active configuration of the project from Debug to Production by setting the drop-down list on the Standard toolbar as shown in the following figure.

    Figure 2-3. Standard Toolbar

  5. Compile the reports and publish them to the report server by selecting Build [image] Deploy AdventureWorks Sample Reports from the main menu.


Chapter 3. Key Concepts

Report Services ships with the following tools and components that support configuration, management, design, development, processing, rendering, and delivery of reports:

Reporting Services Configuration Manager

Reporting Services Configuration Manager is a tool used to deploy a local Reporting Services installation and configure remote Reporting Services instances.

Report Manager

Report Manager is a web-based report access and management tool used to administer a single report server instance. Report Manager lets you view, search, and subscribe to reports; manage the report server folder hierarchy; configure site properties, security, and report execution properties; create shared data sources; create report models; and launch Report Builder.

Report Designer

Report Designer is a report creation tool hosted in Visual Studio. Report Designer supports designing and defining tabular, matrix, chart, and freeform reports. Report Designer provides tabbed windows for Data (query builder and expression editor), Layout, and Preview that allow you to design a report interactively and iteratively. Report Designer is accessed through Business Intelligence Development Studio.

Report Model Designer

Report Model Designer is a report model creation toola report model is the basis of an ad hoc report. Report Model Designer is used to define, edit, and publish report models used by Report Builder. Report Model Designer is accessed through Business Intelligence Development Studio.

Report Builder

Report Builder is an end-user authoring tool used to create tabular, matrix, and chart reports through a simple drag-and-drop interface. Report Builder lets you select queries and fields; group, sort, filter, and explore data; define expressions, calculations, and aggregates; define layout and format; and preview, print, publish, and export reports.

Report Server

Report Server is the primary component of Reporting Services providing its report processing, rendering, and delivery.

A report is the result of the integration of four separate definitions defined in order of dependency on each other (i.e., order in which they are created). The definitions are stored in files with the file extensions listed in parentheses after the definitions that follow.

Data Source (.ds)

A data source contains information about how to connect to a data sourcefor example, a database, flat file or XML file. This includes authentication information, a data source name, and a connection string. A data source can be used by a single report and contained within that report or shared among multiple reports and stored independently on a report server. A report can access one or more report-specific and shared data sources. A report model can reference only one data source and one data source view.

You create a data source using Report Designer, Report Model Designer, or Report Manager.

Data Source View (.dsv)

A data source view is a description of the data to which the data source refers including (in the case of a relational database) the tables, fields, and relationships.

You create a data source view using Report Designer, Report Model Designer, or Report Manager.

Report Model (.smdl)

A report model is a metadata description of data providing a business abstraction of the underlying data and relationships that exist within the data. A report model references one data source (.ds) file and one data source view (.dsv) file.

A report model lets Report Builder users easily explore and select data they are interested in from the underlying data sourceReport Server then uses the report model to generate a query to retrieve the data.

You create a report model using either Report Model Designer or Report Manager.

Report Definition (.rdl)

A report definition completely describes the data source connections, queries, expressions, parameters, images and other design-time elements, properties, and layout used in a report. A report definition is rendered to a processed report.

You create a report definition using either Report Designer or Report Builder.

The next four sections of this book will examine the tools and components.


Chapter 4. Report Manager

Report Manager is a browser-based tool used to access and manage Reporting Services objects over the Web. You launch Report Manager by entering the Report Manager URL into the browser address bar. By default, the URL is:

http://<Server Name>/reports.

You can change the Report Manager URL in the Report Manager Virtual Directory tab of Reporting Services Configuration Manager.

You can access Report Manager on your local machine using the URL: http://localhost/reports.

The following figure shows the Report Manager Home folder page (simply called Home Page in subsequent references) after the AdventureWorks sample reports are published (which you did in the preceding section).

Figure 4-1. Report Manager: Home Page

Report Manager lets you view reports, manage reports, manage the hierarchy of reports, configure role-based security, subscribe to reports, manage report subscriptions, create schedules, configure site properties, create report models, create shared data sources, and launch Report Buildera report design tool that lets users design and deploy reports over the Web. Report Builder is discussed later in this book.

Report Manager organizes Report Services objects into a folder hierarchy. The link for each object has an icon next to it identifying the object type. For example, the AdventureWorks Sample Reports link is a Folder that contains other Report Services objects. A complete list of object link types are: Report, Report model, Linked report, Folder, Subscription, Data-driven subscription, Resource, Shared data source, and Property page. Position the mouse pointer over an icon to display a tooltip describing the object link type.

Click the AdventureWorks Sample Reports link to display the contents of the folder. The contents are two shared data sources (AdventureWorks and AdventureWorksAS) and seven reports as shown in the following figure.

Figure 4-2. Report Manager: AdventureWorks Sample Reports Folder

Click on a report to render and view it and to access report management options. For example, click on the Product Line Sales sample report to display the report in the following figure.

In addition to the View tab which displays the report, notice the Properties, History, and Subscriptions tabs used to manage different aspects of the report. These three tabs are discussed in the Report Builder section later in this book.

Report Manager can create data sources and report models as mentioned in the Key Concepts section at the beginning of this book. Creating these items is described as part of the example in the Report Builder section of this book.


Chapter 5. Report Designer

Report Designer is a report authoring tool hosted in Visual Studio. Report Designer lets you create simple and complex reports from stock and custom reporting elements. You use Report Designer to select the data source, define the query, arrange the layout of data and graphical elements, define interactive functionality, and preview the report during the development phase. Once you are satisfied, you use Report Designer to publish the report to a report server for end-user access.

NOTE

Report Wizard is a tool that steps you through creating a report. Report Wizard helps you specify the report data and create a tabular or matrix report. Report Wizard is easy to use and is not discussed in this bookall Report Designer examples in this book start with empty projects based on the Report Server template. For more information about Report Wizard, see SQL Server Books Online.

In the following subsections, you will create and publish a tabular report based on the AdventureWorks database. This involves creating a report project using Business Intelligence Development Studio, creating the query and layout for the report, and publishing the report to the report server.


Section 5.1. Create a Report Server Project

5.1. Create a Report Server Project

First, create a report server project using Business Intelligence Development Studio. Follow these steps.

  1. Open Business Intelligence Development Studio by selecting All Programs [image] Microsoft SQL Server 2005 [image] SQL Server Business Intelligence Development Studio from the Start menu.

  2. Select File [image] New [image] Project... from the main menu to open the New Project dialog.

  3. Specify Business Intelligence Projects as the project type, select the Report Server Project template, name the project SS2005RE Report Project, and specify its location (or accept the default location). The completed New Project dialog is shown in the following figure.

    Figure 5-1. New Project Dialog

  4. Click OK to close the dialog and create the project.

The Solution Explorer pane in the Business Intelligence Development Studio IDE now contains a project node SS2005RE Report Project with two child nodesShared Data Sources and Reports.


Section 5.2. Create a Report

5.2. Create a Report

In this section we will create a tabular report by defining the underlying report query and the report layout. We will then enhance the report by adding grouping, sorting, filtering, and aggregates. Finally, we will parameterize the report.

Follow these steps to create a report.

  1. Add a report to the project. Right-click the Reports node in the Solution Explorer pane and click Add [image] New Item... from the context menu to open the Add New Item dialog.

  2. Create the report. Select the Report template and name the report EmployeeList 2.rdl. The completed dialog is shown in the following figure.

    Figure 5-2. Add New Item Dialog

Click the Add button to close the Add New Item dialog and create the report.

The IDE now displays two new panes:

  • Datasets: A hierarchical list of datasets, tables, and fields of data available for use in the report.

  • Design: A three tabbed design surface for building the report query (Data tab), designing the report layout (Layout tab), rendering a preview of the report (Preview tab).

The IDE is shown in the following figure.

Figure 5-3. Business Intelligence Development Studio IDE

5.2.1. Define a Report Query

  1. Define a data source for the report. In the Data tab of the design pane, select <New Dataset...> from the Dataset drop-down list to display the Data Source dialog.

  2. Name the data source Adventure Works 2.

  3. Enter the connection string directly into the Connection string text box (replacing <SQL Server Name> with the name of your SQL Server) or click the Edit... button to launch the Connection Properties dialog that will help build the connection string.

    The completed Data Source dialog is shown in the following figure.

    Figure 5-4. Data Source Dialog

    If you decided to use the Connection Properties dialog to help build the connection string, use the drop-down list to select the Server name (or type it). Select the database name AdventureWorks from the drop-down list. The completed Connection Properties dialog is shown in the following figure.

    Figure 5-5. Connection Properties Dialog

    Click OK to close the Connection Properties dialog and populate the Connection string in the data source dialog with the generated connection string.

  4. Click OK in the Data Source dialog to create the data source and to close the dialog. AdventureWorks is added to the Dataset pane.

    Figure 5-6. Generic Query Designer

    Define the query for the report. After you have created the AdventureWorks dataset, the Data tab displays a generic query designera tool used to write queries that are not supported by the graphical query designer. You simply enter a report query directly into the query pane (text box above grid) of the generic query designer shown in the preceding figure.

    We will use the graphical query designer. Switch to the graphical query designer by un-clicking the generic query designer toolbar button identified in the preceding figure. The following figure shows the graphical query designer panethe figure contains the completed query for the report that you will build in the subsequent discussion.

    Figure 5-7. Graphical Query Designer

    The graphical query designer contains four panesdiagram, grid, SQL, and resultsthat are used together to define a report query.

  5. Right-click in the diagram pane and select Add Table... from the context menu (or click the Add Table toolbar button) to add a table to the query. This displays the Add Table dialog that contains a list of tables in the database as shown in the following figure.

    Figure 5-8. Add Table Dialog

  6. Select the Contact (Person) and Employee (Human Resources) tables and click Add to add the tables to the query. After the tables are added to the diagram pane, click the Close button to return to the query designer. Notice that the SQL pane is updated to show an empty query from the two tables, automatically joined based on the relationship between them defined in the AdventureWorks database.

  7. In the diagram pane, check the NationalIDNumber and BirthDate fields in the Employee table and check the FirstName and LastName fields in the Contact table. The fields are added to the grid pane and the SQL pane is updated to include the fields in the SELECT statement.

  8. Right-click in any of the four panes and select Execute SQL from the context menu to show the results of the query in the results pane.

  9. Save the report. Notice that the AdventureWorks dataset (once expanded) in the Datasets pane is updated with the selected fields as shown in the following figure.

    Figure 5-9. Datasets Pane

The report query is now defined.

5.2.2. Define a Report Layout

You will define the report layout next. Follow these instructions.

  1. Click the Layout tab of the Design pane to display the layout design surface shown in the following figure.

    Figure 5-10. Layout Tab: Initial Design Surface

  2. Display the Toolbox if it is not visible by selecting View [image] Toolbox from the main menu or by clicking the Toolbox icon or tab. The Toolbox is shown in the following figure.

    Figure 5-11. Toolbox

  3. Drag the Table Report Item (since we are building a tabular report) from the Toolbox onto the layout design surface. A three-column, three-row table is drawn on the layout design surface as shown in the following figure.

    NOTE

    In addition to the Table Report Item, the Toolbox contains report items for building reports with matrix, list, and chart layouts. Building reports with these layouts is similar to building tabular reports, but beyond the scope of this book. For more information about these layouts, and about using other Toolbox Report Items including Image and Subreport, see SQL Server Books Online.

    Figure 5-12. Layout Tab: Table Control

  4. Add a column to the report. Right-click on a column header in the table. Select Insert Column to the Right from the context menu to add the fourth column.

  5. Associate fields with the report columns. Drag the NationalIDNumber field from the Dataset pane and drop it onto the first column in the Detail (middle) row of the table in the layout design surface. Drag the BirthDate, FirstName, and LastName fields onto the adjacent three cells in the row. Notice that Report Designer automatically populates the column headers with more human-readable column names. The following figure shows the layout design surface with all four fields added.

    Figure 5-13. Layout Tab: National IDNumber, Birth Date, First Name, and Last Name Columns

  6. Render and view the report by clicking the Preview tab. The following figure shows the result.

    Figure 5-14. Preview Tab

  7. Modify the BirthDate field to display only the month and year of the date. Click the Layout tab to return to the layout design surface. Right-click in the BirthDate field (cell containing value =Fields!BirthDate.Value) and select Properties from the context menu to display the Textbox Properties dialog. Select the Format tab and set the Format code to MMMM dd as shown in the Textbox Properties dialog shown in following figure.

    Figure 5-15. Textbox Properties: Format Tab

    In addition to the formatting we have applied, notice that you can use the Textbox Properties dialog to control visibility, navigation, font, interactive sort, and data output. For more information about these topics, see SQL Server Books Online.

    Click OK to close the dialog and return to the layout design surface.

  8. Select the Birth Date column in the table control. In the Properties pane, set the TextAlign property to Left as shown in the following figure.

    Figure 5-16. Birth Data Column Properties

  9. Click the Preview tab in the Design pane to render and view the report as shown in the following figure.

    Figure 5-17. Preview Tab: Birth Date without Year

5.2.3. Add Grouping, Sorting, Filtering, and Aggregates to a Report

Next, we'll modify the report to demonstrate grouping, sorting, filtering, and aggregates. Follow these steps.

  1. If you closed the report project in the preceding example, open it by opening Business Intelligence Development Studio, selecting File [image] Open from the main menu, and selecting the report solution SS2005RE Report Project.sln in the Open Project dialog.

  2. Add a column for the Hire Quarter field to the report. In the Layout tab, click the table control and right-click on the first column header (above the National IDNumber header). Select Insert Column to the Left from the context menu to add the new column.

  3. Add a column for the Sick Leave Hours field to the report. In the Layout tab, click the table control and right-click on the last column header (above the Last Name header). Select Insert Column to the Right from the context menu to add the new column.

  4. Make the HireDate and SickLeaveHours fields available for use in the report. Select the Data tab in the Design pane. Check the HireDate and SickLeaveHours fields in the Employee table in the diagram pane to add the fields to the query. Update the information displayed in the Datasets pane by right-clicking the Report Datasets node and selecting Refresh from the context menu.

  5. Create a new field named HireQuarter containing the quarter in which an employee was hired. Right-click on the AdventureWorks node in the Datasets pane and select Add... from the context menu to display the Add New field dialog shown in the following figure.

    Set the Name property to HireQuarter. Click the Calculated field radio button and set the Calculated field value to ="Q" & DatePart("q", Fields!HireDate.Value) either by entering the value directly or by clicking the function button and using the Edit Expression dialog. The completed dialog is shown in the following figure.

    Figure 5-18. Add New Field Dialog

    Click OK to add the calculated field HireQuarter to the AdventureWorks dataset.

  6. Left align the Hire Quarter column output. In the Layout tab, select the Hire Quarter column in the table control. In the Properties pane, set the TextAlign property to Left.

  7. Add the HireQuarter and SickLeaveHours fields to the report. Drag the HireQuarter field from the Dataset pane onto the first column of the detail row in the table control. Drag the SickLeaveHours field onto the last column of the detail row in the table control. The Layout tab now appears as shown in the following figure.

    Figure 5-19. Layout Tab: Add HireQuarter and SickLeaveHours Fields

  8. Preview the report and examine the newly added Hire Quarter and Sick Leave Hours columns as shown in the following figure.

    Figure 5-20. Preview Tab

  9. Group the output rows by the value of the Hire Quarter column. Select the table control and right click on the grey area to the left of the first column. Click Insert Group from the context menu to display the Grouping and Sorting Properties dialog. In the General tab, select =Fields!HireQuarter.Value from the drop-down list in the first row of the Group on list as shown in the following figure.

    Figure 5-21. Grouping and Sorting Properties Dialog: General Tab

  10. Sort the group on the Hire Quarter column. In the Sorting tab, select =Fields!HireQuarter.Value from the drop-down list in the first row of the Sort on list as shown in the following figure.

    Figure 5-22. Grouping and Sorting Properties Dialog: Sorting Tab

  11. Click OK to close the Grouping and Sorting Properties dialog and return to the Layout tab. Grouping header and footer rows are added to the table control (above and below the detail row) as shown in the following figure.

    Figure 5-23. Layout Tab: Add Grouping

  12. Define sorting on the National IDNumber column within the group. In the Layout tab, select the table control and click on its top left corner so that the column and row handles appear as shown in the following figure.

    Figure 5-24. Layout Tab: Table Control Handles

    Right-click on the shaded border around the table control and select Properties from the context menu to display the Table Properties dialog shown in the following figure.

    Figure 5-25. Table Properties Dialog: General Tab

    Select the Sorting tab. Set the first Sort on expression to =Fields!NationalIDNumber.Value as shown in the following figure.

    Figure 5-26. Table Properties Dialog: Sorting Tab

  13. Add filtering to report only employees with at least 60 hours of sick leave. Select the Filters tab in the Table Properties dialog. Set the first Filter list expression to =Fields!SickLeaveHours.Value, the Operator to >=, and the Value to =60 as shown in the following figure.

    Figure 5-27. Table Properties Dialog: Filters Tab

  14. Click OK to close the Table Properties dialog.

Select the Preview tab to examine the effect of the grouping and filtering you have applied.

  1. Add an aggregate to the reportthe subtotal of Sick Leave Hours. Select the Layout tab. Right-click on the footer group cell below the Sick Leave Hours detail field and select Expression... from the context menu. Enter the expression =Sum(Fields!SickLeaveHours.Value) into the cell. Enter the string Quarter Subtotal into the group footer cell below the National IDNumber cell.

  2. Add a second aggregate to the reportthe total of Sick Leave Hours for the entire report. Enter the expression =Sum(Fields!SickLeaveHours.Value) into the footer (bottom row) cell in the Sick Leave Hours column. Enter the string Total in the footer cell in the National IDNumber column.

The Layout tab with subtotals and total aggregates is shown in the following figure.

Figure 5-28. Layout Tab: Add Totals

Select the Preview tab to examine the aggregates you have created.

5.2.4. Add a Parameter to a Report

In this section, you will add a parameter in the report that will prompt you to enter the sick leave hour threshold for employees to include in the report. Follow these steps.

  1. Select the Data tab in the Design pane.

  2. Select Report [image] Report Parameters... from the Business Intelligence Development Studio main menu to open the Report Parameters dialog.

  3. Click the Add button. Enter the values for the Name, Data type, Prompt, and Default values properties to complete the dialog as shown in the following figure.

    Figure 5-29. Report Parameters Dialog

    Press OK to close the dialog and create the new report parameter.

  4. Modify the query in the report to use the new parameter. In the grid pane of the graphical query designer, add the filter >= @SickLeaveHoursThreshold to the SickLeaveHours row as shown in the following figure.

    Figure 5-30. Graphical Query Designer: Add Parameter

    Notice that the SQL pane is automatically updated with a WHERE clause.

  5. Remove the filter created on the Sick Leave Hours column earlier in this section so that the report is filtered only by the new report parameter. In the Layout tab, select the table control and click on its top left corner so that the column and row handles appear. Right-click on the shaded border around the table control and select Properties from the context menu to display the Table Properties dialog. Select the Filters tab and delete the expression in the Filter list. Click OK to close the dialog.

  6. Select the Preview tab. The prompt for the parameter has been added in the area above the report toolbar as shown in the following figure.

    Figure 5-31. Preview Tab: New Report Parameter

  7. Enter a value for the Sick Leave Hours Threshold parameter and click the View Report button to examine the effect.


Section 5.3. Publish a Report

5.3. Publish a Report

In this section, you will publish the report that you created to the report server. Follow these steps.

  1. In the Solution Explorer pane, right-click the report project (SS2005RE Report Project) and select Properties from the context menu to display the Property Pages dialog shown in the following figure.

    Figure 5-32. Report Project Property Pages

  2. Click the Configuration Manager... button to display the Configuration Manager dialog shown in the following figure.

    Figure 5-33. Configuration Manager

  3. Select Production from the Active solution configuration drop-down list.

  4. Select Production for the Configuration drop-down list for the SS2005RE Report Project.

  5. Click the Close button to return to the Property Pages dialog.

  6. Select Employee List 2 from the Start Item drop-down list.

  7. Enter the report server virtual directory (not the Report Manager virtual directory) for the TargetServerURL property (http://localhost/reportserver by default).

  8. Click OK to close the Property Pages dialog.

  9. Save the project by selecting File [image] Save All from the main menu or by pressing the Save All toolbar button.

  10. Deploy the report to the report server by selecting Build [image] Deploy SSRE Report Project from the main menu.

You can now access the new report project and the one report it contains through Report Manager as shown in the following figure.

Figure 5-34. Report Manager: New SS2005RE Report Project Folder

Previous Page
Next Page


Chapter 6. Report Model Designer

You need to create a report model before you can use Report Builder to create reports. The report model lets users explore and select data from the underlying data source using a business-oriented view of the data and of the relationships between data items such as tables and fields. This section shows you how to create a report model. Follow these steps.

  1. Open SQL Server Business Intelligence Development Studio by selecting All Programs [image] Microsoft SQL Server 2005 [image] SQL Server Business Intelligence Development Studio from the Start menu.

  2. Select File [image] New [image] Project... from the main menu to display the New Project dialog.

  3. Specify Business Intelligence Projects as the project type, select the Report Model Project template, name the project SS2005RE Model and specify its location (or accept the default location). Click OK to create the report model project.


Section 6.1. Create a Data Source

6.1. Create a Data Source

Create a data source for the report model. Follow these steps.

  1. Right-click the Data Sources node in the Solution Explorer pane. Select Add New Data Source from the context menu to start the Data Source Wizard.

  2. If the Welcome dialog for the Data Source Wizard appears, click the Next > button.

  3. Add a new Connection by clicking the New... button in the Select how to define the connection dialog shown in the following figure.

    Figure 6-1. Data Source Wizard: Select How to Define the Connection

    Clicking the New... button opens the Connection Manager dialog.

    NOTE

    The SQL Server name is commonly confused with the SQL Server service name MSSQLSERVERthey are not the same. The SQL server name appears in the Connect To Server dialog that appears when you start SQL Server Management Studio and as the value for the Name attribute in the property page for the server node in Object Explorer in SQL Server Management Studio.

  4. Specify the name of your SQL Server in place of <SQL Server Name> in the Server name drop-down list and select the AdventureWorks database as shown in the following figure.

    Figure 6-2. Data Source Wizard: Connection Manager

    Test the connection by clicking the Test button.

    Press the Close button to create the connection and return to the Data Source Wizard dialog, which now displays the new connection in the Data Connections list box.

  5. Click the Finish >> button on the Select how to define the connection dialog.

  6. Click Finish on the Completing the Wizard dialog to create the new data source and end the wizard.

The new data source Adventure Works.ds appears in the Solution Explorer pane under the Data Sources node.


Section 6.2. Create a Data Source View

6.2. Create a Data Source View

Create a data source viewa logical data model based on the data sourcefor the Adventure Works data source. Follow these steps.

  1. Right-click the Data Source Views node in the Solution Explorer pane. Select Add New Data Source View from the context menu to launch the Data Source View Wizard.

  2. If the Welcome dialog for the Data Source View Wizard appears, click the Next > button.

  3. Select the Adventure Works data source in the Select a Data Source dialog as shown in the following figure.

    Figure 6-3. Data Source View Wizard: Select a Data Source

  4. Click the Next > button to open the Select Tables and Views dialog shown in the following figure.

    Figure 6-4. Data Source View Wizard: Select Tables and Views

  5. Click the >> button to include all of the tables and views from the AdventureWorks database in the data source view.

  6. Click the Next > button to open the Completing the Wizard dialog shown in the following figure.

    Figure 6-5. Data Source View Wizard: Completing the Wizard

  7. Click the Finish button in Completing the Wizard to create the new data source view and end the wizard.

The new data source view Adventure Works.dsv appears in the Solution Explorer pane under the Data Source Views node.


Section 6.3. Create a Report Model

6.3. Create a Report Model

To define a new report modela metadata abstraction of the underlying database that identifies business entities, fields, and rolesfollow these steps:

  1. Right-click on the Report Models node in the Solution Explorer pane and select Add New Report Model from the context menu to launch the Report Model Wizard.

  2. If the Welcome dialog for the Report Model Wizard appears, click the Next > button.

  3. Select the Adventure Works.dsv data source view in the Select Data Source View dialog as shown in the following figure.

    Figure 6-6. Report Model Wizard: Select Data Source View

  4. Click the Next > button to open the Select report model generation rules dialog shown in the following figure.

    Figure 6-7. Report Model Wizard: Select Report Model Generation Rules

  5. Accept the defaults and click the Next > button to display the Collect Model Statistics dialog shown in the following figure.

    Figure 6-8. Report Model Wizard: Collect Model Statistics

  6. Accept the dialog defaults and click the Next > button to display the Completing the Wizard dialog shown in the following figure.

    Figure 6-9. Report Model Wizard: Completing the Wizard

  7. Click the Run button to create the new report model. The dialog displays metadata rules for the report model in the Status list box, and a Finish button (in place of the Run button).

  8. Click the Finish button after processing completes to end the wizard.

The new report model Adventure Works.smdl appears in the Solution Explorer pane under the Report Models node.


Section 6.4. Publish a Report Model

6.4. Publish a Report Model

Finally, publish the report model to the report server so that you can use it to create new reports using Report Builder. Follow these steps.

  1. Right-click the SS2005RE Model node in the Solution Explorer pane. Select Deploy from the context node to publish the model.

  2. View the deployment results in the Output pane as shown in the following figure.

    Figure 6-10. Output Pane: Deploy Report Model

  3. Open the Report Manager Home page. A new Models folder has been created as shown in the following figure.

    Figure 6-11. Report Manager: New Data Source

    The Models folder is the default location for the new Report Model project. You can change this in the Property Pages dialog displayed by selecting Project [image] Properties... from the main menu.

  4. Click on the Models folder. The new Adventure Works report model is listed.

  5. Click the Report Builder button on the toolbar to launch Report Builder. The new data source Adventure Works is available for use in the data source list box in the Getting Started pane.


Chapter 7. Report Builder

Report Builder is a client-side Windows Forms application launched from Report Manager. Report Builder provides a simple way to create reports based on predefined data sources, report models, and report templates. Report Builder insulates you from having to understand the underlying database, how to construct queries, how to create report definitions, and how Reporting Services works to publish, store, and deliver reports.

You create a report on a client computer and then publish it to the report server to make it available to users. This involves four steps: creating a data source, creating a report model, creating a report layout, and publishing the report.

You use Report Manager to define the data source and the report model that Report Builder needs. You then use Report Builder to create the report and publish it to the report server.

These steps are discussed in the following subsections:


Section 7.1. Create a Data Source

7.1. Create a Data Source

In the Report Manager Home page, click the New Data Source button on the Report Manager toolbar. This opens the New Data Source page. Complete the fields as shown in the following figure specifying the name of your SQL Server (in place of <SQL Server Name>) for the value of the Data Source attribute of the Connection string.

Click the OK button to create the data source and return to the Report Manager Home page. The new data source Adventure Works appears as shown in the following figure.

Figure 7-1. Report Manager: Create Adventure Works Data Source

Previous Page
Next Page


Section 7.2. Create a Report Model

7.2. Create a Report Model

Once the data source, the next step is to create the report model. Follow these steps:

  1. Click on the Adventure Works data source in the Report Manager Home pagethis opens its management page.

  2. Click on the Generate Model button to open a page used to generate a new model.

  3. Complete the form as shown in the following figure.

    Figure 7-2. Report Manager: Generate Model

  4. Press the OK button at the bottom of the page to create the new model. Generating the model might take a few minutes. The property page for the model will be displayed once the model is built.

  5. Return to Report Manager Home page. The new model named Adventure Works Model was created as shown in the following figure.

    Figure 7-3. Report Manager: Create Adventure Works Model

Previous Page
Next Page


Section 7.3. Create a Report Layout

7.3. Create a Report Layout

Now that you have defined a data source and a model based on it, you will use Report Builder to create a tabular report.

Click the Report Builder icon on the toolbar on the page displaying the data source and model built in the previous sections. If you are prompted with a Security Warning dialog, click Run to start Report Builder. The first time report builder runs, it will download the application from the report server before launching the application. Report Builder as it looks when it first starts is shown in the following figure.

Figure 7-4. Report Builder

The IDE contains a Getting Started panel which in turn contains two sections:

New

This section lets you create a new report. The section contains a list box showing the available report models including Adventure Works Model that you created in the preceding section. It also lets you specify the report layoutone of table, matrix, or chart. After you have specified the model and layout, press the OK button to create the report.

Open

This section lets you open an existing report from either a report server or file.

NOTE

While you are working through the Report Builder example, you should examine the underlying database for the report model (in this case AdventureWorks) to better understand how the model abstracts complexities of the underlying data. Use SQL Server Management Studio or your favorite tool to examine the AdventureWorks database schema.

Follow these steps to build a new report.

  1. Specify the report model on which the report is based. Select Adventure Works Model in the New section of the Getting Started pane.

  2. Specify a tabular report layout. Check the Table (columnar) radio button in the New section of the Getting Started pane.

    NOTE

    In addition to creating tabular reports, Report Builder lets you build reports with matrix and chart layouts by selecting the appropriate Report layout radio button in the New section of the Getting Started pane. The design and publishing process is similar to the process for creating a tabular report described in this example, but beyond the scope of this book. For more information about these layouts as well as information about more complex report types including drill-down and nested, see SQL Server Books Online.

  3. Press the OK button to generate a new, empty tabular report and to open the design surface you will use to define the report as shown in the following figure.

Figure 7-5. Report Builder: Explorer Pane and Design Surface

Report Builder displays:

  • An Explorer pane that contains two list boxesEntities and Fieldsused to build the report. Entities are a collection of logical model items based on items in the database. Fields map to columns in database tables or to calculations based on columns.

  • A design surface where you place entities and fields to define both the content and the layout of the report.

Let's start by creating a report that lists some employee information. Follow these steps:

  1. Change the title of the report to Employee List by clicking on and replacing the text Click to add title.

  2. Select an entity that the report is based on. Scroll down in the Entities list and click on Employee. Notice that the contents of the Fields list box updates to show entities for the Employee entity.

  3. Add a field to the report. Drag the National ID Number field from the Field list box onto the design surface and drop it into the panel containing the text Drag and drop column fields. Notice that the Entities list box is updated to reflect the data relationship constraints imposed on the report by adding the National ID Number field. Notice also that the Birth Date field is now bolded in the Field list box indicating that it is used in the report. If you make a mistake adding a field, right-click on the field on the design surface and select Delete from the context menu.

  4. Preview the report. Click on the Run Report button in the Report Builder toolbar. An empty report titled Employee List is generated. Click the Design Report toolbar button to return to the designer.

    NOTE

    You can switch back and forth between design and report views at any time to see progress and help you refine reports as you develop them. You must add at least one field to a report in order to view it.

  5. Add the birth month and day for the employee to the report. Drop the Birth Date field to the right of the National ID Number field on the design report. Let go of the mouse button when you see a large insert caret (blue by default) appear.

    Since the birth date can be sensitive, we want to display only the month and year. Click on the Birth Date field and right-click Format... from the context menu to display the Format dialog shown in the following figure.

    Figure 7-6. Format Dialog

    Select October 21 from the Format list box and click OK. Note that you can specify any valid .NET Framework format string. After you click OK, the format for the Birth Date field on the design surface is updated.

  6. Add the quarter that the employee was hired in to the report. Expand the Hire Date field in the Field list box. Select the Hire Quarter field (a function based on the Hire Date field that was automatically created in the model) and drop it to the right of the Birth Date field on the design surface.

  7. Format the Hire Quarter field so that it is prefixed with the letter Q on the report. We'll do this the hard way using a function instead of simply formatting the field (as we did with the Birth Date field in step 5). Right-click on the Birth Month field on the design surface and select Edit Formula... to bring up the Define Formula dialog shown in the following figure.

    Figure 7-7. Define Formula Dialog

    Next, select the Functions tab. Expand the Conversion node and you will see that there is a function TEXT that converts a number to a string. We will use this together with the concatenation operator &. Modify the text in the Formula for each Employee text box to read "Q" & TEXT(Hire Quarter). Note that Hire Quarter is a field (rather than simply a string) in the Formula for each Employee text box so you will need to prepend and append the formula text to it. Click OK to return to Report Builder.

    Notice that the Hire Quarter field has been renamed to Hire Quarter[2] column header on the design surface to prevent a name conflict. Click on the column header and edit it in place to remove the [2] suffix.

  8. Add employee contact information to the report. Select the Contact entity from the Entities list box. Notice that the Fields list box updates to show fields for the Contact entity. Drag the First Name field to the right of the Employee ID field on the design report. Drag the Last Name field to the right of the First Name field on the design surface.

    The completed report is shown in the following figure.

    Figure 7-8. Report Builder: Employee List Report

The report you've designed contains columns from both the underlying HumanResources.Employee table and the Person.Contact table using data relationships automatically identified from the database and added to the report model.

Run the report by clicking the Run Report button in the Report Builder toolbar. The resulting report is shown in the following figureit's a simple report that automatically supports paging, navigation, printing, exporting (XML, CSV, TIFF, PDF, Web archive, and Excel), zooming, searching, and sorting (click on the arrows in the column headings).

Figure 7-9. Report Builder: Employee List Report

Next, we'll modify the report in this example to demonstrate grouping, totaling, sorting, and filtering. Return to the design view by clicking the Design Report button in the toolbar. Follow these steps.

  1. Use the Hire Quarter field to group data and calculate aggregates. Right-click on the Hire Quarter field on the design surface to select it. Drag it from the right of the Birth Date field and drop it to the left of the National ID Number field (making it the first field). Notice that a second group tab named Hire Quarter[2] appears to the left of the Employee Group tab. This behavior lets you easily group, subtotal, and organize hierarchies of data as we'll see next.

  2. Add a Total Sick Leave Hours column, displaying totals by Hire Quarter. Drag the Total Sick Leave Hours field from the Field list box onto the design surface and drop it to the right of the Last Name field. Report Builder automatically adds group totals as shown in the following figure.

    Figure 7-10. Report Builder: Employee List WithTotals

    You control whether totals for a group are displayed by right-clicking a group tab and selecting or deselecting Show Group Subtotals on the context menu.

  3. Sort the report on the Hire Quarter and the National ID Number fields. Click the Sort and Group button on the Report Builder toolbar to display the Sort dialog box shown in the following figure.

    Figure 7-11. Report Builder Sort Dialog

    Select Hire Quarter[2] in the Select Group list box. Select Hire Quarter in the Sort by drop-down list.

    Select Employee in the Select Group list box. Select National ID Number in the Sort by drop-down list.

    Click OK to complete the sort definition, close the dialog, and return to Report Builder designer.

  4. Filter the report to include only employees with more than 60 hours. Click the Filter button on the Report Builder toolbar to display the Filter Data dialog.

    Drag the Sick Leave Hours field from the Fields list box and drop it onto the Employees with filter condition text box. (If you make a mistake, click on the field in the filter condition and select Remove Condition from the context menu.)

    Click equals (in the filter condition that you just added) and select Greater Than or Equals from the context menu.

    Select 60 from the to drop-down list.

    The completed Filter Data dialog is shown in the following figure.

    Figure 7-12. Report Builder: Filter Data Dialog

    Click OK to return to Report Builder designer. Notice that the Filter Description at the bottom of the design surface reflects the filter you just applied.

Run the report and examine the effect of the grouping, totaling, sorting, and filtering.

In the final part of this example, we will add additional aggregations at the report and group level. Follow these steps.

  1. Add the count of employees by quarter. Drag the #Employees expression field from the Fields list box onto the Hire Quarter[2] group tab.

  2. Add a calculation showing the average sick leave hours by employee. Define a new field by selecting Report [image] New Field... from the Report Builder menu. This opens an empty version of the Define Formula dialog box shown in the following figure.

    Figure 7-13. Report Builder: Define Formula Dialog

    Complete the dialog as shown in the figureyou will need to expand the Sick Leave Hours field in the Fields list box to access the Total Sick Leave Hours expression. Click OK to return to the Report Builder designer. Notice the expression Average Sick Leave Hours has been added to the Fields list box.

  3. Drag the Average Sick Leave Hours field from the Field list box onto the Hire Quarter[2] group tab.

Run the report and notice that both the number of employees and the average sick leave hours are now calculated for each quarter and for the entire report. The bottom of the last page of the report is shown in the following figure.


Section 7.4. Publish (Save) a Report

7.4. Publish (Save) a Report

Next, let's publish the report created in the preceding section to the report server so that other users can access it.

Click the Save button on the Report Builder toolbar or select File [image] Save from the menu. This opens an empty version of the Save As Report dialog box shown in the following figure.

Figure 7-14. Report Builder: Save As Report Dialog

Make sure that the Look in property is set to Report Server Home. This will save the report to the Home folder of the report server (where the data source and the report model were created). Name the report Employee List and click the Save button.

When you save a report in Report Builder, it is always saved to the same Report Server from which you opened Report Builder. You can save a report as often as you like.

Open or refresh the Report Manager Home page and confirm that the Employee List report is now published as shown in the following figure.

Figure 7-15. Report Manager: Create Employee List Report

You can click on the report link to view the render and view the report, and to manage its properties, snapshot history, and subscriptions.


Section 7.5. Run a Report

7.5. Run a Report

You run a report by clicking the report link in Report Manager. This example section walks through running a report and some of the functionality automatically provided by Reporting Services.

Click on the Employee List report link in the Report Manager Home page. Reporting Services renders the report and it is returned to your browser as shown in the following figure.

Figure 7-16. Report Manager: Employee List Report

As mentioned earlier, Reporting Services tabular reports automatically support paging, navigation, printing, exporting (XML, CSV, TIFF, PDF, Web archive, and Excel), zooming, searching, and sorting (click on the arrows in the column headings). Access these features through the toolbar above the report.

For example, to export the report as an Excel file, follow these steps:

  1. Select Excel from the Select a format drop-down list on the toolbar.

  2. Click the Export link on the toolbar to export the report.

  3. A File Download dialog will display asking whether you want to save or open the exported file. Click the Open button.

Excel launches with an export of the report as shown in the following figure.

Figure 7-17. Excel Export: Employee List Report

Previous Page
Next Page


Section 7.6. Open a Saved Report

7.6. Open a Saved Report

This section shows you how to use Report Builder to open a report that is published to a report server. Follow these steps.

  1. Navigate to the Home page in Report Manager.

  2. Launch Report Builder by clicking the Report Builder toolbar button in Report Manager.

  3. Select File [image] Open from the Report Builder main menu (or click the Open from Report Server... link in the Open section of the Getting Started pane) to display the Open Report dialog as shown in the following figure.

    Figure 7-18. Opening a Report from Report Builder

  4. Select the Employee List report and click the Open button.

The Employee List report is displayed in design mode.


Section 7.7. Report Snapshots and History

7.7. Report Snapshots and History

A Report history is a collection of snapshots of previously run reports. This section shows you how to create snapshot reports and how to manage report history.

Data source connection credentials must be stored in the report server to enable report history functionality. Follow these steps to store the credentials for the Adventure Works data source (created earlier in this section) to the report server.

  1. Open the Report Manager Home page.

  2. Click the Adventure Works data source link to open the data source management page shown in the following figure.

    Figure 7-19. Adventure Works Data Source Properties

  3. Select the Credentials stored securely in the report server radio button in the Connect using section.

  4. Enter the User Name and Password.

  5. Check the Use as Windows credentials when connecting to the data source check box.

  6. Click the Apply button to update the Connect using properties and return to the Report Manager Home page.

Credentials for the Adventure Works data source are now stored at the report server and you can now create snapshots of reports (such as Employee List) that use this data source. Follow these steps to create a snapshot of the Employee List report.

  1. Open the Report Manager Home page.

  2. Click on the Employee List report to render it.

  3. Click the History tab on the Employee List page.

  4. Click the New Snapshot toolbar button to create the report snapshot as shown in the following figure.

    Figure 7-20. Employee List Report History

View a snapshot by clicking on its date/time link.

Delete one or more snapshots by clicking the checkbox beside each snapshot and then clicking the Delete button on the toolbar.


Section 7.8. Subscriptions

7.8. Subscriptions

A subscription is a request to deliver a report according to a schedule or in response to a data event. Subscriptions can be created by end users from the Subscriptions tab for a report in Report Manager or by report server administrators. Both push and pull subscriptions are supported and can be personalized for content, format, and delivery.

Subscriptions are beyond the scope of this book. For more information, see SQL Server Books Online.


Chapter 8. Integrating Reports into Applications

You can integrate Reporting Services reports into web and Windows applications in three ways:

  • Issue URL-based requests to navigate, access, and view reports. This is the most efficient way to render reports because URL-based requests directly access the server. URL-based reports are also easy and efficient to implement.

  • Use the freely distributable report viewer control for Visual Studio 2005 to embed Reporting Services functionality.

  • Access the report server through Report Server web service using SOAP over HTTP as the communication interface between the client and the report server. In addition to providing the capabilities of URL-based requests, the web service exposes report management functionality that is not available through URL access or through the Report Viewer control including content, subscription, and data-source management.

An enterprise application often uses more than one of these methods.

The three methods are discussed in more detail in the following subsections. The example in all three sections uses the AdventureWorks samples discussed in the Setting Up section at the beginning of this book.


Section 8.1. URL-based

8.1. URL-based

The URL request contains information identifying the report server to use, as well as parameters that are processed by the report server and that control the formatting and rendering of the report. The parameters, parameter prefixes, and combination of supplied parameters in the URL determine how the report server handles a specific request.

For example, open the URL http://localhost/ReportServer?/AdventureWorks Sample Reports/Sales Order Detail&rs:Command=Render. The following figure shows the report that is rendered in the browser.

Figure 8-1. URL-based Access

The information in the URL specifies that you want to do the following:

  • Use the local report server.

  • Access a report in the report server virtual root named ReportServer.

  • Access the report named AdventureWorks Sample Reports/Sales Order Detail.

  • Render the report in the default rendering format for the browser.

Report server parameters are used to control report processing. They are prefixed with rs: in the URL. For example, the URL http://localhost/ReportServer?/AdventureWorks Sample Reports/Sales Order Detail&rs:Command=Render&rs:Format=IMAGE renders the report as a TIFF image.

HTML device-information settings control how the report is rendered in HTML format. They are prefixed with rc: in the URL. For example, the URL http://localhost/reportserver?/AdventureWorks Sample Reports/Company Sales&rs:Command=Render&rc:Toolbar=false&rc:Zoom=200 renders the report at twice the default size without a toolbar.

Report parameters are passed in the URL as name-value pairs separated by an equals sign (=). Pass a null parameter using the syntax parameterName:isNull=true.

Note that report parameters are not prefixed. For example, the report Employee Sales Summary takes three parameters: ReportMonth (integer), ReportYear (integer), and EmpID (string). When you run the report normally, the RDL populates the employee drop-down list using the DataSet named SalesEmps. This corresponds to the following query:

    SELECT E.EmployeeID, C.FirstName + N' ' + C.LastName AS Employee
    FROM HumanResources.Employee E
    INNER JOIN Sales.SalesPerson SP ON E.EmployeeID = SP.SalesPersonID
    INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
    ORDER BY C.LastName, C.FirstName

We need to run this query to determine the employee ID for each employee. Jillian Carson has employee ID 277. The URL http://localhost/reportserver?/AdventureWorks Sample Reports/Employee Sales Summary&rs:Command=Render&EmpID=277&ReportMonth=7&ReportYear=2002 sets the report parameters to return the sales summary report for Jillian Carson for July 2002.

The preceding examples show how to request a report using a URL. This is equivalent to an HTTP GET method. You can also request a report using an HTTP POST method, which transfers the parameter name/value pairs in the HTTP header instead of the URL. Using an HTTP POST method overcomes the maximum allowable URL length limit in cases where a parameter list is long, and is also more secure because the user cannot directly modify the parameter names and values. The following HTML returns the same sales summary report for Jillian Carson for July 2002 as in the preceding example but uses an HTTP POST method:

<form id="postRenderForm"
  action="http://localhost/reportserver?/AdventureWorks Sample Reports/
  Employee Sales Summary" method="post" target="_self">
    <input type="hidden" name="rs:Command" value="Render"/>
    <input type="hidden" name="EmpID" value="277"/>
    <input type="hidden" name="ReportMonth" value="7"/>
    <input type="hidden" name="ReportYear" value="2002"/>

    <input type="submit" value="Render"/>
</form>


Section 8.2. Report Viewer Control

8.2. Report Viewer Control

The report viewer control is a freely distributable control that lets you embed Reporting Services functionality into a .NET application. The control is called ReportViewer, ships with Visual Studio 2005, and is located in the Data section of the Toolbox in the Visual Studio 2005 IDE.

Follow these steps to build an example that uses the report viewer control.

  1. Launch Visual Studio 2005.

  2. Create a new C# Windows application. Select File [image] New [image] Project from the main menu. Select Visual C# [image] Windows project type, and the Windows Application template. Name the application SS2005RE ReportViewerControl. Press OK to create the application.

  3. Open the form and give it a meaningful caption. Right-click Form1.cs in the Solution Explorer pane and select Open from the context menu. Set its Text property = Report Viewer Control Example.

  4. Drag the ReportViewer control from the Toolbox onto Form1. The ReportViewer control menu prompts you to either Choose Report from a drop-down list or Design a new report. Click Design a new report to bring up Report Designer. Select <Server Report>, and you are prompted for the Report Server Url and the Report Path that together specify an existing report.

    Set Report Server Url = http://localhost/reportserver and Report Path = /AdventureWorks Sample Reports/Company Sales.

  5. Make the form a bit larger so that the Report Viewer control fits within it.

  6. Run the applicationthe resulting output is shown in the following figure.

You can control and change the report at runtime by modifying the properties of the control's ServerReport objectexposed through the ServerReport property.


Section 8.3. Web Services

8.3. Web Services

Reporting Services provides full access to report server functionality through web services. The web service provides methods and properties for both report executioncontrolling the processing and rendering of reportsand report management.

The example in this section accesses a report from a .NET applicationyou can use any development tool that is capable of invoking SOAP methods.

This example builds a Windows application that retrieves the list of reports in the Adventure Works Sample Reports folder and displays them in a list box. The user can render a report and save it to a Web archive (.mht) file. The complete, running application is shown in the following figure.

Figure 8-2. Completed Application (Running)

Follow these steps to build and run the example.

  1. Launch Visual Studio 2005.

  2. Create a new C# Windows application. Select File [image] New [image] Project from the main menu. Select Visual C# [image] Windows project type, and the Windows Application template. Name the application SS2005RE WebService. Press OK to create the application.

  3. Open the form and give it a meaningful caption. Right-click Form1.cs in the Solution Explorer pane and select Open from the context menu. Set its Text property = AdventureWorks Sample Reports.

  4. Add a list of reports and a button to render the report selected in the list. Drop a ListBox control onto Form1 and name it reportListBox. Drop a Button control onto Form1, set its Name property = renderButton, and set its Text property = Render.

    The form is shown in the following figure.

    Figure 8-3. Completed Form1 Design

  5. Right-click on References in the Solution Explorer pane and select Add Web Reference... from the context menu to display the Add Web ReferenceStart Browsing for Web Services dialog as shown in the following figure.

    Figure 8-4. Add Web Reference Dialog

  6. Click on the Web services on the local machine link to display a dialog listing Web Services on the local machine as shown in the following figure.

    The ReportService2005 Web service is used to programmatically manage reporting objects. The ReportExecution2005 Web service is used to programmatically process and render reports.

  7. Click the ReportService2005 link to display a dialog describing the Web service.

  8. In the "ReportingService2005" Description dialog, set the Web reference name = RS2005 as shown in the completed dialog shown in the following figure.

    Figure 8-5. ReportingService2005 Description Dialog

    Click the Add Reference button to add the Web service reference to the project. The Web References node in the Solution Explorer pane will update to show the new Web service reference.

  9. Follow steps 5 through 8 to add reference to the ReportExecution2005 servicename the reference RE2005.

  10. Open the code for Form1. Right-click Form1.cs in the Solution Explorer pane and select View Code from the context menu.

  11. Add using directives to the top of the class for the ReportService2005 and ReportExecution2005 Web services:

    using SS2005RE_WebService.RE2005;
using SS2005RE_WebService.RS2005;

    Add a using directive to the top of the class for IO:

    using System.IO;

  12. Add an event handler to populate the list box when the form is created. Switch to the form designer view. Double-click on the form (but not on the list box or button) to add an event handler for the Load event. Switch back to the code view and add the following code for the Form1_Load method:

    // Instantiate Web service proxy used to programmatically
// report objects
ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Get reports in the folder
CatalogItem[] cis = rs.ListChildren(@"/AdventureWorks Sample Reports",
      false);
foreach (CatalogItem ci in cis)
{
      // Populate list box with Report items
      if (ci.Type == ItemTypeEnum.Report)
            reportListBox.Items.Add(ci.Name);
}

  13. Add an event handler to render and save the report when the Render button is clicked. Switch to the form designer view. Double-click on the button renderButton to add an event handler for the load event. Add code to the renderButton_Click method to render the report to a user-specified file.

    The complete code for Form1.cs, including the renderButton_Click event handling code, follows:

    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.IO;

using SS2005RE_WebService.RE2005;
using SS2005RE_WebService.RS2005;

namespace SS2005RE_WebService
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // Instantiate Web service proxy used to programmatically
            // manage reporting objects
            ReportingService2005 rs = new ReportingService2005();
            rs.Credentials =
                  System.Net.CredentialCache.DefaultCredentials;

            // Get reports in the folder
            CatalogItem[] cis = rs.ListChildren(
                  @"/AdventureWorks Sample Reports", false);
            foreach (CatalogItem ci in cis)
            {
                // Populate list box with Report items
                if (ci.Type == ItemTypeEnum.Report)
                    reportListBox.Items.Add(ci.Name);
            }
        }

        private void renderButton_Click(object sender, EventArgs e)
        {
            if (reportListBox.SelectedIndex >= 0)
            {
                // Get the name of the report selected in the list box
                string reportName = reportListBox.Items
                  [reportListBox.SelectedIndex].ToString();

                // Get the name to save the report to
                SaveFileDialog sfd = new SaveFileDialog();
                sfd.Filter = "Web archive (*.mht)|*.mht";
                sfd.FileName = reportName;
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    string renderFileName = sfd.FileName;

                    // Instantiate Web service proxy used to
                    // programmatically process and render reports
                    ReportExecutionService re =
                        new ReportExecutionService();
                    re.Credentials =
                        System.Net.CredentialCache.DefaultCredentials;

                    // byte array to hold rendered report
                    byte[] report;

                    // report rendering argument
                    string reportFullName =
                       @"/AdventureWorks Sample Reports" + "/" +
                       reportName;
                    string format = "MHTML";
                    string historyID = null;
                    string deviceInfo = null;
                    RE2005.ParameterValue[] pv =
                        new RE2005.ParameterValue[0];
                    string encoding;
                    string mimeType;
                    string extension;
                    RE2005.Warning[] warning = null;
                    string[] streamIDs = null;

                    try
                    {
                        // load report, set parameters, and render
                        re.LoadReport(reportFullName, historyID);
                        re.SetExecutionParameters(pv, "en-us");
                        report = re.Render(format, deviceInfo,
                              out extension, out mimeType,
                              out encoding, out warning,
                              out streamIDs);

                        // save report to user-specified file
                        using (FileStream fs =
                              File.OpenWrite(renderFileName))
                            fs.Write(report, 0, report.Length);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Error",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Error);
                    }
                }
            }
        }
    }
}

  14. Compile and run the report. Select the Company Sales report and click the Render button. Accept the default file name Company Sales. Close the application. Open the file to view the saved report as shown in the following figure.

    Figure 8-6. Saved Company Sales Report Company Sales.mht

Previous Page