Report Authoring

In the first section of this chapter, you learned about the various components that make up SQL Server 2005’s Reporting Services. In the second half of this chapter, you’ll get a more detailed look at steps required to design and deploy a simple report.

Development Stages

The process for developing a Reporting Services application begins with using the Report Designer to define the report’s data sources and layout. Next, you need to build and deploy the report to the Report Server. Finally, you need to make the report available to end users by either embedding the report within an application or adding subscriptions for the report.

Designing the Reporting Solution

In creating reporting solutions using Reporting Services, you first select a dataset that defines the data that will be used in the report, and then you lay out the individual data fields on the report. To handle stock reports that are presented in a tabular or matrix format, Microsoft has provided a Report Design Wizard that steps you through the process of creating a data source and laying out the report.

Building and Deploying the Reporting Solution

Once the report has been designed, you need to build the report and then deploy it to the Report Server. Building the report creates a .NET assembly that will run the report. Deploying the report essentially takes this assembly and copies it to the Reporting Services Report Server. While you can perform this manually, the Report Designer has built-in options to both build and deploy reports to the Report Server.

Making the Report Available to End Users

After the report has been deployed to the Report Server, you can then make the report available to end users via several different mechanisms. You can allow access to the reports by embedding them in an application, via their URLs, or by creating a subscription that will push the report to the end user. Report subscriptions can be set up to be delivered at a certain time, or they can be data driven.

Now that you have an overview of the Reporting Services development process, the next section will take you through the steps of developing and deploying a simple report using SQL Server 2005’s Reporting Services.

Creating a Reporting Services Report

You can begin using the Report Designer either by starting the Report Wizard and using it to create your initial report or by starting off with a blank design surface and then adding your own report definition elements. In either case, defining a dataset is the first thing you need to create a report. In this example, I’ll show you how you can quickly build a report using the Report Wizard.

To build a Reporting Services application, first open the Business Intelligence Development Studio and then select the File | New | Project option to display the New Project dialog that’s shown in Figure 8-7.

Figure 8-7: Creating a new report: New Project

To create a new Reporting Services report using the Report Wizard, first select the Business Intelligence Projects option from the Project Types list. Then, in the Templates list shown in the right side of the screen, select the Report Project Wizard option. Then fill in the boxes at the bottom of the dialog. In the Name text box, enter the name of the current project. The Location box specifies where the report project’s source files will reside. The Solution Name box allows you to name the Reporting Services solution. Here, you can see that the value AdWReports is used as the name of the project and the solution. Clicking OK starts the Report Wizard Welcome dialog that’s shown in Figure 8-8.

Figure 8-8: The Report Wizard Welcome dialog

The Report Wizard Welcome dialog gives you an overview of the steps that the Report Wizard follows during the creation of a report. You first select a data source, then design a query, then select the type of report that you want, and finally specify the formatting for the report. Clicking Next displays the Select The Data Source dialog you can see in Figure 8-9.

Figure 8-9: Select the Data Source

The Select the Data Source dialog allows you to define your connection to the database. To create the Data Source, first give it a name. The name can be anything you choose. It just serves to identify the data source. Next, use the Type drop-down to select the type of database system that the data source will use. The default value is Microsoft SQL Server, but you can also choose OLE DB, Microsoft SQL Server Analysis Services, Oracle, or ODBC. Next, in the Connection String box input the connection string that’s required to connect to the target database. If you’re unfamiliar with the connection string values, you can click Edit to display the Data Link dialog, which will step you through the creation of the Data Source. Then make this a shared data source check box at the bottom of the screen allows you to share the data source with other reports. You can choose either to create a shared data source, which can be used by several different reports, or to create a data source that will be used only by the report that you are currently creating. If you plan to create several reports that all come out of the same database, creating a shared data source is a good idea, as it can be freely used by all of the reports in your solution and will make it unnecessary to create a unique data source for each report. Clicking Next displays the Design the Query dialog.

From the Design the Query dialog, you can manually enter a SQL statement that will define the dataset used by the report, or else you can click the Query Builder button to display the Query Builder shown in Figure 8-10.

Figure 8-10: Query Builder

The Query Builder is an interactive query design tool that enables you to build SQL queries without needing to be a SQL expert. However, to effectively use the Query Design tool you still need to have a good basic knowledge of your database’s design and scheme. You can select tables from your database by right-clicking in the top portion of the Query Builder and then selecting Add Table from the pop-up menu to display the Add Tables dialog. There you can select one or more tables (multiple tables are selected by holding down the CTRL key and clicking the desired table). The Query Builder will automatically detect any relationships between the tables based on like column names and data types and will draw links between the tables visually showing the relationships.

After selecting the tables, you then select the desired columns from each table by putting a check in the check box that precedes the column name. As you might have guessed, checking the * (All Columns) entry will automatically select all of the columns from the table. As you interactively select the tables and columns and define the relationships between the tables, the Query Designer automatically builds the SQL statement that you can see at the bottom of Figure 8-10.

It’s easy to overlook the fact that the Query Builder can also be used to build parameterized queries where the end user supplies a value to the query at run time. To build a parameterized query using the Query Builder, you simply type a question mark into the Filter column that’s in the row of the database column name that you want to use with a parameter. The Query Builder will automatically convert the question mark character to the =@Param value that you can see in the middle of Figure 8-10.

You can test the query by clicking the exclamation icon (!) shown in the toolbar. After you’ve completed designing the query, you can save the query and continue by clicking OK. The SQL statement that was created by the Query Builder will be written into the Design The Query dialog. Clicking Next displays the Select The Report Type dialog that you can see in Figure 8-11.

Figure 8-11: Select the report type

While the Report Designer allows you an incredible degree of flexibility in designing reports, the Report Wizard is more restrictive in the type of reports that it will build for you. The Report Wizard will generate either of two different types of reports: a tabular-style report or a matrix-style report.

Note 

While the styles of reports are limited, the Report Wizard makes a great starting point for building a base report that you can later customize in the Report Designer.

The Tabular report follows your traditional report layout where headers are presented at the top of the page and the detail information is listed below in the body of the report. The Matrix report style presents a crosstab style report where there are headers across the top of the page and down the left-hand side of the page. In Figure 8-10, you can see that the tabular style of report is selected. If you look closely at the bottom of the dialog, you’ll notice the Finish button. Clicking Finish allows you to quickly complete the report formatting by selecting all of the default values. Clicking Next displays the Design The Table dialog that is shown in Figure 8-12.

Figure 8-12: Design the table

Initially, all of the available columns from the query are shown in the Available Fields list that you can see in the left side of Figure 8-12. From that list of available fields, you can selectively drag fields to the area of the report where you want them to appear. If you want a field to be displayed in the page header, you drag it to the Page section shown in the upper-right side of the screen. If you want a field to be used for group totals, you drag it to the Group section. And if you want a field to be a data field, you drag it to the Details section. In Figure 8-12, you can see that the Name field is used as the page header; the CustomerID field is used as the group header; and the SalesOrderID, OrderDate, DueDate, OrderQty, ProductID, UnitPrice, and LineTotal fields are used in the details area. Once you have laid out the fields that will be used in the report, clicking Next displays the next Report Wizard dialog that you can see in Figure 8-13.

Figure 8-13: Choose the table style

The Choose The Table Style dialog allows you to select the general design of the report that will be generated by Reporting Services. The different table styles are similar, but each style uses a different color scheme and slightly different formatting. In Figure 8-13, you can see that the Corporate style was selected. The Choose The Table Style dialog is the final report creation dialog that’s displayed by Report Wizard. Clicking Next displays the Completing The Wizard screen that you can see in Figure 8-14.

Figure 8-14: Completing the wizard

The Completing The Wizard dialog allows you to review all of the selections that were made in the previous wizard dialogs. At this point, you can either use the Back button to page back and made corrections to the report specifications, or you can click Finish to generate the report. Selecting the Preview check box will render the report for you to view in the Report Designer’s Preview window. After the report is generated, it’s added to the Reporting Services solution shown in the Business Intelligence Development Studio.

Deploying a Reporting Services Report

After the report has been created, the next step in creating a Reporting Services application is to build the report and deploy it to the Report Server. Building the report creates a .NET DLL assembly, and deploying the report copies that assembly to the Reporting Services Report Server. You can deploy reporting solutions from the Report Designer by selecting the Build | Deploy Reports option that you can see in Figure 8-15.

Figure 8-15: Deploying the Reporting Services solution

If you select one of the deployment options and the report has been changed, the Report Designer will automatically build the report before it is deployed. The output from the build and deployment processes is shown in the Output window that you can see in the bottom of Figure 8-15. Any errors or problems will be listed in the window. Likewise, if the report deployment succeeds, then the success message is listed in the Output window.

Running a Reporting Services Report

Reporting Services reports can be run by accessing their URL or by embedding them in your applications. You can access and run Reporting Services reports by pointing your browser to the http://<servername>/ReportServer URL, where all of the Reporting Services reports are listed. Figure 8-16 shows the ReportServer web page.

Figure 8-16: Accessing Reporting Services reports from a URL

The ReportServer URL lists all of the reports that have been deployed to the Report Server. Each different solution is stored in its own subdirectory. To test the Reports that have been deployed, simply click the link and the Report Server will render the report inside the browser. Figure 8-17 shows the example report in the browser.

Figure 8-17: Running Reporting Services reports

The report that’s rendered in the browser follows the format that was set up in the report design phase. Since this report uses parameters, the param1 field is displayed at the top of the screen. The end user needs to enter a value into this field and then click the View Report button to render the report. In Figure 8-17, you can see that the value of A Bike Store was entered for the replaceable parameter.

In addition, because this report was generated using the drill-down option, a plus sign is displayed in front of the detail line shown on the report. Clicking the plus sign (+) displays the row detail lines that go into that top-level summary line. In this example, clicking the plus sign (+) expands the display, as Figure 8-18 illustrates.

Figure 8-18: Drilling down into the report

In Figure 8-18, you can see the details that went into the summary line shown in the previous figure. Clicking the minus sign (–) retracts the detail display and shows the summary line.

Running the reports directly from the Reporting Services URL is great for testing, but when your application goes live, you’ll want to embed the report URL in your application or access the Report Server via web services calls.