Reporting Services: It's De-lightful

With apologies to composer Cole Porter, this is the song that Microsoft might be singing about its new SQL Server Reporting Services product. Slated for release by the end of 2003, Reporting Services handles the complete reporting life cycle—from a report's design, to its deployment in a managed-server environment, to delivery to the end user. The information we present here is based on the second beta release of the product; the final version might be somewhat different.

Microsoft originally intended to deliver Reporting Services as part of the upcoming Yukon release of SQL Server. However, users who received early descriptions and demonstrations of Reporting Services were extremely excited about what they saw and told Microsoft that they wanted these features as soon as possible. Microsoft listened, making Reporting Services an add-in to SQL Server 2000 as well as building the features into Yukon.

What is everyone so excited about? Without Reporting Services, the only way to deliver up-to-the-minute business information over the Internet or company intranet is to code dynamic Web pages or use a third-party reporting tool. But coding dynamic pages is time-consuming and usually requires an experienced developer, and third-party reporting solutions can be expensive.

Reporting Services will let users of varying skill levels create their own dynamic reports. Although you can add code to a report for more control over formatting and data, you don't need any programming experience to build sophisticated reports. You can make reports available over the Internet or an intranet in several formats, including PDF and TIFF, that look good both in a browser and in print. And Reporting Services provides the infrastructure that makes accessing these reports convenient and secure.

Rather than creating a new development environment for authoring Reporting Services reports, Microsoft used its existing integrated development environment (IDE), Visual Studio .NET. You can create reports through any edition of Visual Studio .NET 2003. You deploy completed reports from Visual Studio .NET to a Report Server, which manages security, data caching, and other reporting functions. The Report Server delivers reports to recipients in a variety of formats through either a pull (requested by the user) or a push (scheduled delivery) method.

The "glue" that links the report you design in Visual Studio .NET to the report the Report Server delivers is Microsoft's new Report Definition Language (RDL). This XML-based language contains all the information about the report design. You create a report as an RDL document in Visual Studio. Visual Studio then deploys the RDL to a Report Server, which stores it in a SQL Server database. When Reporting Services delivers a report to a user, it processes the RDL report definition and renders it into a more familiar format such as an HTML page or an Adobe PDF document.

Design: You're the Top

You design reports by selecting the Business Intelligence project type in Visual Studio .NET 2003. This project type, which installs during Reporting Services setup, offers two project templates: Report Project Wizard, a step-by-step walk-through of report creation, and Report Project, which takes you directly to the Report Designer. The Report Project lets you build your report from scratch, without any guidance. Both templates ultimately result in a report project. You'll likely want to use the Report Wizard for most reports, especially your first few, letting it create the initial report layout and database connectivity. You can then customize the report by editing it in the designer. You'll use the Report Project more often as you become more familiar with the report structure and begin to build more complex reports.

No matter which method you use to create your report, the first step in creating a report is defining a data source. The data source is the connection string that the report will use to communicate with SQL Server, Analysis Services, or another OLE DB data source. You can define a data source in each report, or you can define it within the report project and let multiple reports within that project use it. In addition, a report can contain multiple data sources if it needs to pull information from more than one location. When you're using the wizard, you define the data source on the Select the Data Source page. You can type in the connection string to reference your data source, or you can create a reference to your data source by clicking Edit and using the Data Link Properties dialog box that appears.

If you want to let reports run unattended, make sure you include the password in the data source. If you're using the Data Link Properties dialog box, select the Allow Saving Password check box to encrypt the password in a User Options file that Visual Studio creates locally. Reporting Services also encrypts the password when you deploy the report to the Report Server. Alternatively, you can use Windows credentials and Integrated Security to access the data source. This approach works fine when a user is logged in to the Report Server and retrieving a report interactively. It doesn't work when the Report Server produces a scheduled report because scheduled reports have no interactive user to provide Windows credentials.

You create and edit reports in Visual Studio .NET through the Report Designer. This authoring environment contains three areas. The Data area lets you construct and edit the queries that will retrieve data for your report. You construct your report in the Layout area, which Figure 1 shows, by dragging controls from the Toolbox and fields from the Fields window and dropping them onto the report design. The Preview area shows how the report will look when the user retrieves it.

In the Data area, you query data from the data sources to create one or more data sets within the report. Reporting Services executes the query that you define for the data set against the data source. The rows and columns that result from this query become the data set. You build the query by using a query-builder interface specific to the data-source type or by typing in the query directly.

In many cases, you'll want to let the user specify parameter values for the data-set query to control which rows to return. For example, you might want the user to specify the date range for the information that will appear on a report. If your data source is SQL Server, you give the user this ability by including the parameters in the WHERE clause, using an at (@) sign followed by the parameter name. The code in Listing 1 queries sales information from the Pubs database and includes three parameters: @StartDate, @EndDate, and @StoreID. (Other types of data sources might use different parameter syntax.) Including these query parameters in a T-SQL query string causes the Report Designer to create three corresponding report parameters, which appear as input fields in the report. Figure 2 shows the Report Parameters dialog box, which you access by choosing Report Parameters on the Report menu. To let the user select a value from a drop-down list, we enhanced the StoreID report parameter by creating a second data set called StoreNames in the report. We selected stor_name in the Label field and stor_id in the Value field. This data set queries the store name and store ID from each store record, then populates the drop-down list in the center of Figure 3. This configuration presents the store names to the user in a drop-down list and uses the corresponding store ID as the @StoreID parameter.

When Reporting Services serves the report, a Content Manager or Publisher can specify constants for the parameter values if a report on a given Report Server always runs against a predetermined subset of data. For instance, you might always run a particular report on one Report Server for the Eastern Region and the same report on another Report Server for the Western Region. If you haven't specified any such parameters, the user can specify the parameter values in the URL that requests the report. Alternatively, a parent report can pass parameter values to a subreport. If you don't specify the parameters through any of these methods, Reporting Services will prompt the user for them, as the center portion of Figure 3 shows.

De-Lovely: After You, Who?

After defining the data sets, you're ready to create the visual aspect of your report in the Layout area of the Report Designer. Report controls, the building blocks for the report design, appear in the Toolbox, which Figure 4 shows. As you examine the Toolbox controls, be aware that you can bind only four of the controls directly to a data set. These four controls, called data regions, are the Table, Matrix, List, and Chart controls. Most data regions also contain other controls that form the content of the report, as we show in a moment.

The Table control produces a familiar column-and-row report with optional grouping and subtotaling. The Matrix control produces a crosstab or pivot table report, in which both the rows and the columns are tied to values in the data set and the intersecting cells contain computed values. The List control produces repeating blocks of free-form data. The Chart control also interacts directly with a data set, but it can't contain other report objects. In most cases, one of these four data regions will serve as the foundation for each report you create. But by nesting data regions inside each other, you can create more complex report formats, binding each nested data region to a different data set.

While the data regions define the report's structure, the remaining controls in the Toolbox—Textbox, Line, Rectangle, Image, Subreport, and potentially ActiveX—form the visual elements of the report. The Line control is self-explanatory, but the other controls have some details worth mentioning.

The Textbox control is the only way to display textual information in a report. Textboxes can contain literal strings or expressions beginning with the equals (=) sign. When you use the Textbox inside a data-region control, the Textbox's value can display the value in a field in the data set associated with that data region. You set the Textbox's value by using the ADO.NET Fields! syntax that Figure 1 shows. This syntax comes from the Visual Basic (VB) construct for accessing members of a collection—in this case, the Fields collection. In fact, you can use any valid Visual Basic .NET expression to define the value you want to appear in a Textbox. You can also use Visual Basic .NET expressions to determine the value of formatting properties, such as font characteristics or background color, on any of the visible report controls.

The Textbox control's properties let you set all the style characteristics for the text that the Textbox contains. You need to keep a couple of Textbox requirements in mind when designing your reports. First, the Textbox control doesn't support rich text. You can apply font attributes to the entire Textbox, but you can't apply them to individual characters or words in the Textbox. Second, Textboxes have a Can Grow property that lets them expand to contain the text placed in them. When you set this property to True, the Textbox can grow vertically but not horizontally, so it pushes the controls below it further down the page.

The Rectangle control is just that—a rectangular area in the report that can contain any number of other report controls. This attribute is useful when you have a group of controls that you want to keep together in a report. For example, you might have two Textboxes beside a Table. The first Textbox is aligned with the top of the Table, but in design mode, the second Textbox appears below the Table definition. When Reporting Services renders the report, the Table grows taller, pushing the lower Textbox down so that it remains below the bottom of the Table. To keep both Textboxes beside the Table, you need to place them inside a Rectangle. Because the Rectangle doesn't start below the Table, the Table doesn't push the Rectangle down when Reporting Services renders the report. Note that the Rectangle can have a border style of None so that it isn't visible on the report.

The Image control can display an image that exists in any one of three locations: embedded in the report definition, stored as a file on the Report Server, or stored as a binary large object (BLOB) in a database. The Image control supports several file types, including BMP, JPEG, GIF, and PNG.

The final standard Toolbox control is the Subreport. The Subreport is a container object, but instead of containing other report objects, it contains an entire independently designed report. Subreports are most useful when your report contains multiple one-to-many (1:N) relationship sections or when you want to reuse a report definition in several different reports. For example, if your report is going to show orders linked to four different sales regions, you can include the same subreport in the report four times, providing a different parameter to the report each time to select the orders for the appropriate sales region. By using Subreports, you can also nest reports to facilitate user drilldown in interactive environments.

You can get much of the same functionality of a Subreport by embedding one data region within another. Because the Report Server processes each instance of a Subreport as a separate report, performance of reports that contain large Subreports can be poor. Nested data regions have better performance than Subreports, but this difference will be noticeable only with large or complex Subreports.

You can also add ActiveX controls to the Toolbox, then use them in your report layout. You add an ActiveX control by right-clicking the Toolbox and selecting Add/Remove Items. The control must comply with the .NET Common Language Specification (CLS). In other words, if you can use the ActiveX control in a .NET application, you can use it in a report.

Reporting Services' support for embedded ActiveX controls lets you add many custom capabilities—such as a calendar or multimedia elements—to your reports. Keep in mind that users can interact with an ActiveX control only when viewing the report from the report preview screen or as HTML. Users can't interact with an ActiveX control (which might not even appear) when the report is in other formats such as TIFF and PDF.

For a better understanding of the interaction between data regions and the report controls they contain, let's return to the Report Wizard­generated report design from Figure 1. This report consists of two main controls: a Textbox containing the report title and a Table containing the report body. Each column in the Table control represents a column that will appear on the report. Each row in the Table represents a line of information that will appear on the report as follows.

The first row contains the column headings, which appear once at the beginning of the report. Following the column headings is one row for each data grouping in the report. In this report, we're grouping by store name and by order date. You set the grouping information in the table's Grouping/Sorting property. These rows repeat whenever a grouping value changes. The last row in the table contains the detail information. This row repeats for each detail record in the report.

Each cell in a Table can contain only one reporting control, but this limitation isn't severe because a Rectangle is also a container control and can contain combinations of multiple controls. In this report, each cell contains a Textbox that references a field in the Table control's data set. Figure 3 shows the resulting report.

Two properties called PageBreakAtStart and PageBreakAtEnd handle report page breaks. You can set these properties on a Table, Matrix, List, Rectangle, or Chart control. PageBreakAtStart causes the reporting object to start on a new page. PageBreakAtEnd ensures that no other reporting object follows the reporting object on the current page. You can also set a Table, Matrix, or List control to begin a new page every time it begins a new data grouping. The TIFF and PDF output formats note the specified physical dimensions of the page and add page breaks if your report extends beyond the size of one page either horizontally or vertically.

As we mentioned earlier, the Report Server stores the report definitions as RDL in a SQL Server database. To view the RDL from within the Report Designer, right-click the report name in the Solution Explorer and select View Code. You'll see RDL code, as Figure 5 shows. If you prefer to code ASP.NET in Notepad, you'll be happy to know that you can make changes (e.g., adjusting coordinates or tweaking font properties) directly in the RDL from the View Code window and have those changes reflected in the designer. Just don't tweak the code too much until you have a solid understanding of RDL, or you might end up with an invalid XML document. Both the XML schema and element definitions for RDL are in Books Online (BOL) for Reporting Services.

Deployment: From This Moment On

When your report design is complete, you can begin deploying it to a Report Server, in preparation for delivering it to users through the Report Server's Web interface and email subscriptions. The first step in this deployment is to configure the reporting project by right-clicking the project in Visual Studio and selecting Properties. In the Property Pages dialog box that Figure 6 shows, select the report to start debugging. The report that you select for debugging will automatically run on the Report Server as the final step in the deployment process. Next, in the TargetFolder field, type the name of the folder you want to place the report in on the Report Server. If this folder doesn't already exist, Reporting Services will create it for you. Finally, enter the URL of the Report Server. This will be http://machinename/ReportServer if you used the default virtual directory when you installed Reporting Services.

Once you've set these properties for the project, your reports are ready for deployment. In Visual Studio .NET, either select Deploy Solution from the Build menu or run the solution in Debug mode. Using Deploy Solution publishes all the reports in the project to the Report Server. If you debug the solution, Reporting Services publishes all the reports to the Report Server and executes the report you selected to start debugging. Except for the automatic report execution, there's no difference between running in Debug mode and the Deploy Solution option.

Alternatively, you can deploy a report by uploading its RDL file directly from the Report Manager, the Web-based administration tool included with Reporting Services. To use this method, you must locate the RDL file for the report you want to deploy. This approach has the advantage of letting you deploy a single report from a project that might contain several reports. This method of deployment might also make placing the report in the correct location in the Report Server's folder structure easier.

After deployment, the Report Server stores the report information in a report catalog, which resides in a SQL Server database. Note that although Reporting Services can include data from several different sources in a report, it requires a SQL Server database to store the reports and other meta information. As we mentioned earlier, Reporting Services encrypts the report connection information when it's placed in the database, thereby protecting sensitive information.

Delivery: Anything Goes

After you've designed and deployed a report, it's available for distribution. You can distribute reports in the Report Catalog through the Report Manager, Microsoft's SharePoint Portal Server, or a custom application. The Report Manager lets you organize reports into folders and lets users access the reports on demand or subscribe to them by email. Reports can be live reports in which the data is requeried each time they run, or they can be snapshots of standard reports that are updated regularly.

The Report Manager lets an administrator set access rights for reports and lets you create push distribution schedules to email reports to subscribers. When Reporting Services distributes reports, it renders them in human-readable formats such as HTML, TIFF, PDF, Microsoft Excel, and Web Archive (MIME encapsulation of HTML) and machine-readable formats such as XML and comma-delimited (CSV). Figure 7 shows the Report Manager Web application. Clicking a folder displays that folder's contents; clicking a report displays that report. Once you've viewed a report in the browser, you can export it to any of the aforementioned formats by selecting a format in the drop-down box that Figure 8 shows, then clicking Export.

Clicking the Subscriptions tab above a report view displays the current subscriptions to the report, as Figure 9 shows. You can also create a new subscription on this screen. Each subscription lets one or more users receive a report by email. The Report Server can send the subscriptions out on a scheduled basis or any time a snapshot report is updated. The subscription email can be in any of the available report formats.

The Properties tab of a report view shows general information about the report, including its parameters and data sources, execution caching, snapshot generation, and security properties. The Execution link, which Figure 10 shows, lets you deliver snapshots of the report by using a cached copy of the report. The Report Server generates the copy of the report according to a schedule. The History link determines whether these cached report copies are saved long-term in a snapshot history. After you create a snapshot history, a History tab above the report view displays the stored historical reports.

In addition to using the Report Manager, you can programmatically access and manage Reporting Services features through a Web service or Windows Management Instrumentation (WMI), or you can access a report directly through its URL. You can use a Web service to publish, manage, and access reports. You can use WMI for server configuration. And URL access lets you integrate reports into other Web sites through direct hyperlinks.

De-End: Ev'ry Time We Say Goodbye

If you've created reports in Microsoft Access, you'll be happy to note that you can convert these reports into Reporting Services reports. Currently, this is the only conversion path available for Reporting Services. Bear in mind, however, that the RDL structure that contains Reporting Services reports is a public standard. So, others will be able to create translation programs that let you move other existing report formats into Reporting Services.

Reporting Services provides a one-stop resource for handling your reporting needs in a distributed environment. Its ability to deliver reports in a secure, managed environment and its multiple rendering formats will elicit a cheer from anyone who's ever had to deliver business data to a far-flung user base.

Discuss this Article 11

James Manning (not verified)

on Jan 21, 2004

I can not find the link you refer to in this article (InstantDoc ID 40529).
http://www.microsoft.com/sql/reporting/default.asp, then click the Register for Reporting Services Beta link.
Can you verify and email the new link to me?
Thanks

The best article i have seen so far in terms of practicle knowledge on Reporting Services.
I've added your site to my favourites as soon as i finished reading this article.
Would like to see such article more in furture.
And it would have been great had it include screen shots as well.
Anyways thanks for such a nice article.

This article is very good and comprise about the product.
I have a requirement to change Report manager look and feel, do you have any idea of how to change the RM UI / ReportManager web application. thanks in advance.

Hi!
I am in Japan.
My superior wanted me to test for reporting services.
So I tested it.
I have a problem in reporting services.
I wanted to send a mail with a rdl fild in reporter manager.
But I have a error massage like this.
"TimedSubscription 2004/04/12 15:08 Failure sending mail: The Report Server has encountered a configuration error; more details in the log files"
or When I edit a report It have an error like this.
"user or group'name 'riXX\administrator' is not comfirmed or recognized"
Do you know how to solve this problem.
I am using the other machine's mail server.
Have a nice day!

Very detailed article! You guys have done your homework. Easy to follow and understand. I just learned about this yesterday, and I can't wait to get started with Reproting Services! Thanks for the helpful insight!

Hi, For a BI tool meta data service is the primary need. But that is missing in this tool. It is not mentioned any where in this article...i suppose both pros & cons needs to be put in a review article.

I have been playing with the Beta version of Reporting Services and I love it. But what I want to know is if there is a tool or a way to take existing Crystal Reports .rpt files and convert them to the .rdl format so that all of our reports do not have to be rewritten.
Does anyone have any information on this?

I don't see your new front-end as an improvement at all. It requires alot more eye
movement and it's more difficult to interpret.
The feature you used to have, "most recent discussion questions" is missing. This was my main interest in your front page!

From the Blogs

Don’t let bad data sneak up on you when and where you least expect it. Ferret out bad data with Melissa Data’s newest Profiling Component for SSIS. Learn how to take control of your data using knowledge-base-driven metadata. The truth shall set you free!...More

Now that we’ve outlined the process to let servers in a SQL Server AlwaysOn Availability Group "talk to each other" by means of setting up linked servers, it’s possible to set up some additional or improved checks on Availability Group Health....More

In my previous post, I provided a high-level outline of the core logic (and rationale behind that logic) that would be needed to set up regular synchronization checks on SQL Server Agent Jobs for servers where AlwaysOn Availability Groups have been deployed. In this post, I’ll walk through the steps--and the code--needed to setup those checks....More