ost report design tools attempt to please both developers and non-technical end users by combining high levels of flexibility and power and ease-of-use. This accommodation naturally requires various sacrifices on both sides. With the Reporting Services Report Designer, Microsoft has abandoned this strategy and made a commitment to the developer. The most obvious indicator of this is that the design-time environment has been integrated into Visual Studio rather than it being a standalone application.

Reporting Services is a Web-enabled reporting tool that delivers reports via a Web browser. It also, or can be used to exports to a variety of formats, including Adobe Acrobat (PDF), Microsoft Excel, and XML. It ships with an easy-to-use, Web-based report manager which allows users to schedule and run reports and export them to a variety of formats.

How You Get It
SQL Server Reporting Services is an add-on to SQL Server 2000?you must own a license for SQL Server on a server in order to use Reporting services on that same server. Existing SQL Server owners can get it at no additional license cost (other than postage and handling).

MSDN subscribers can download Reporting Services from the MSDN subscriber downloads site. Everyone else can contact their local Microsoft office to order Reporting Services on a CD?check out the Web site for more information.Components
Reporting Services consists of five components:

• Reporting Services Web Service: The main engine of this service is its reporting Web service. It provides access to all functionalities, including the rendering engine, scheduling components, and report database. Use it to programmatically add and remove folders, deploy reports, execute reports, and schedule reports.
• ReportingServices Service: This component is a Windows service. It handles scheduled execution and delivery of reports.
• Report Manager Web Site: The Web-based report manager allows users to submit and organize reports, execute and schedule reports. You can access the report manager using the URL http://yourserver/reports.
• Report Designer: The report designer is integrated into Visual Studio 2003. It adds a new project type “Report Project” inside the category “Business Intelligence Projects” which you use to create reports. Your Report project stores data source definitions as .rds files and report definition files as .rdl files on your local file system. You can deploy these files to a reporting server from within Visual Studio, from the Report Manager or with your own code that uses the Reporting Services Web Service.
• Reporting Services Database: Reporting Services uses SQL Server to store your reports. The .rdl files that you save in the development environment are parsed and stored in the database tables in the “ReportServer” database, which is created during setup. Setup also creates a database called ReportServerTempDB.

The Reporting Services Web Service and Report Manager Web Site are installed to your default Web site in IIS.

You can install all of the Reporting Services components on to your development system if you have SQL server and Internet Information Systems installed, or you can install just the report designer component on your development system and install the rest of Reporting Services on a server.

Creating Reports
Creating a report is very similar to creating an ASP.NET Web page or a Windows form?you draw controls on the report surface, and set the properties of the controls to determine their appearance and behavior. The language used for formulas (or “expressions” in Reporting Services) is VB.NET, which is also immediately familiar.

The concept of a report with one or more group headers, followed by a report details section, followed by group footers is replaced with the more free-form, controls-based approach. The ability to produce “banded” results is still available (using the list control), but it is no longer the fundamental core building block of a report.

Another limitation of some of the existing reporting tools that Reporting Services has improved upon is the ability to use multiple datasets. Reporting Services allows you to create and use multiple datasets within a single report, which do not need to be related to one another. Because you link datasets to controls in the report rather than to the report itself, different controls can reference entirely different datasets.

Figure 1. Report Projects: To create a new report project from Visual Studio, select “New Project,” and select “Report Project” or “Report Project Wizard” from the “Business Intelligence Projects” project type.

Data Sources and Datasets
To create a new report project from Visual Studio, select “New Project,” and select “Report Project” or “Report Project Wizard” from the “Business Intelligence Projects” project type.

The first thing you do to create a new report project is to create a data source (if you use the wizard you will be led though this process). Reports projects can have as many data sources as you need?they are simply XML (.rds) files containing ADO connection strings. To create a data source, right-click the “Shared Data Sources” project item and select “Add New Data Source.”

Figure 2. Data Sources: To create a data source, right-click the “Shared Data Sources” project item and select “Add New Data Source.”

You will be prompted to select a database server and database. Your data source can be any ADO data source?it does not have to be SQL server.

Once you have created a data source you can create your first report. Right-click the Reports item in the solution explorer, then select “Add New Report” to run through the New Report Wizard, or select “Add ? Add New Item?” and choose “Report” from the “Add New Item” dialog.

The report wizard will prompt you for the data source to use, then prompts for a query string. You can click “Edit” to use the query builder to create an ad-hoc query (which automatically creates a dataset), then follow the wizard to automatically generate a report. I prefer to design my reports from scratch, particularly because the wizard does not allow you to use a stored procedure for your data source.

To add a dataset to your report, select the “Data” tab, then choose the “New Dataset” item in the “Dataset” drop-down list. You will be prompted for a dataset name, data source, command type and query string.

Figure 3. Adding a Dataset: To add a dataset to your report, select the “Data” tab, then choose the “New Dataset” item in the “Dataset” drop-down list.

The dataset name is whatever you want to call your dataset, and the data source will be the name of the dataset you created earlier. The command type will be one of the following options:

• Text: An ad-hoc SELECT statement, which you can type directly into the query string text box.
• StoredProcedure: The name of a stored procedure. You have to enter the stored procedure name in the query string textbox.
• TableDirect: For reading from a single table only. Enter the name of the table in the query string textbox.

In my opinion, the dataset property page dialog is missing some key productivity enhancements. For the Text query type, the query designer is not available, and for Stored Procedures and TableDirect, the query string textbox should really be turned into a drop-down list to save you the trouble of entering a stored procedure or table name.

As you create datasets, the IDE automatically adds report parameters to your report. These provide data for the parameters of your stored procedure or select statement. Report parameters can be manipulated in the “Layout” view. You can specify available ranges from a pre-defined list or from another dataset, and the selection pages that are automatically generated by Reporting Services will display a drop-down list with appropriate values.

Basic Controls
There are five controls you can use to render data in Reporting Services.

• Text Box: The text box is most like a label in ASP.NET or Windows Forms. You can set the text box value property to a static value (as a label for a field or column), or you can link to a data source and field (see “formulas” later in this article). Text boxes can be placed directly in the report body or inside a list or table control.
• List: The list will be the most familiar control to users of existing reporting tools, being a repeating set of output that can be sorted and grouped. Lists can contain any of the Reporting Services controls, including other lists. Use lists inside lists to produce grouped results.
• Table: Use the table control to render data in columns. The table control serves solely as a container for other controls?you will generally place text box controls inside table cells. The table control is a good way to controls in columns aligned, and the table control is quite flexible?you can merge cells in the same way you can use the COLSPAN and ROWSPAN attributes in HTML.
• Chart: The chart control provides basic graphing abilities.
• Matrix: A matrix is similar to a pivot table in Excel, and allows you to display multi-dimensional data.

Formulas
A key element that makes Reporting Services powerful is its use of expressions. An expression is a formula written in VB.NET that returns a value. Expressions can be used to control nearly every property of a report control; the most obvious one of these is the value the control displays.

Figure 4. The Expression Editor: The Expression Editor displays most of the available data collections in a tree view.

Many control properties expose the ability to control their values using an expression. For example, the properties a text box exposes as an expression include its displayed value, background color, background image, font style, family, weight and size, format (and the list goes on). Table rows and columns expose their “hidden” property as an expression and lists use expressions to control grouping.

Expressions can access all of the data sources and their fields, as well as report parameters, custom functions, and other report items like the page number, number of pages in the report, and report name. The Expression Editor displays most of the available data collections in a tree view you can use to add code to your expression.

Accessing Data in Expressions
Reporting services exposes data to expressions by way of a number of collections?that is, objects that contain named values, which work just like the collection and hashtable types in VB.NET.

In general, you access collections in code using the syntax collection!field. For example, to use the Globals collection to access the page number, use Globals!PageNumber. For items that return an object, use collection!field.property. Use an equals (“=”) sign as the first character of the expression to indicate that your expression returns a value.

The main collections you access in reporting services are:

• Fields: The Fields collection provides access to the fields returned by the control?s dataset. Controls are related to a dataset by setting the DataSetName property of the control to the name of the dataset. Leave this property blank to inherit the DataSetName from the control that contains your control. For example, to access the Surname field of a dataset called Employees use:

  	=Fields!Employees.Surname 

• Globals: The Globals collection contains the page number, total pages, report name, and other report properties. To create a control that displays the current page number and count of pages in the report, use:

  	="Page " & Globals!PageNumber & " of " & Globals!TotalPages

• Parameters: The Parameters collection contains the Report parameters. To view a report parameter called “ID,” use:

  	=Parameters!ID.Value

  Parameters are defined in the property page of the report.

• ReportItems: The ReportItems collection provides access to the other controls in the current report. To access another control called txtName, use:

  	=ReportItems!txtName.Value

  Use the Me keyword to access properties of the current control.

• User: The user collection contains two items, Language and UserID. Language is the current user’s language code, and UserID is the name of the logged-on user. These are also available as properties of the Globals collection.

Figure 5. Adding functions: You can create functions using the VB.NET syntax.

Creating and Accessing Custom Code
To add your own code to a report, display the report properties page by right-clicking the gray section surrounding the report and click Properties. You can create functions using the VB.NET syntax.

To access your custom functions in an expression, use the syntax Code.Function. For example, to call the FormatDuration function above, use the code:

	=Code.FormatDuration(75)

You can also reference your own assembly and use its properties and methods in your code, although this requires some manual modification of configuration files on your report server.

The Expression Editor is very basic. There is no code coloring, syntax checking, or IntelliSense. The tree view does not display the ReportItems and User collections, and does not list any custom functions you have added.

Deployment
Deploying reports to a server from Visual Studio is very straightforward?just right-click your report in the solution explorer and select the “Deploy” option. In my test environment, the first time I used this option, the operation took a long time (several minutes), but subsequent deployments were fairly fast, so be patient the first time. You can configure test and production server URLs in the project properties page and use the configuration type?Reporting Services adds the “local debug” and “production” configuration types to the existing list that already includes “debug” and “release”?to control which server reports are sent to.

Writing code to automate the deployment of reports to a server (as in a custom action for your setup program) is straightforward using the methods of the Reporting Services Web Service, and the Web service documentation is very thorough.

You can run reports by browsing to the Report Manager (http://yourserver/reports by default) and selecting your report. A pre-generated ASP selection page is displayed which you use to select options and render your report.

You can also create your own custom selection page, or render the report from a Windows Forms application using the Reporting Services Web Service.

Observations
The story behind the initial release of Reporting Services is that it was targeted for launch as part of SQL Server 2005, but the beta was so popular that Microsoft chose to release it early. This story rings true because Reporting Services is missing a number of key usability and productivity features that should really be present in a full release.

As a VB.NET developer, I like that fact that the expression language is VB.NET, but Microsoft should have included C# support, and should ideally support any .NET language. Being a server-side application, there are no client-side runtimes (or related problems) to worry about?this is a truly “zero footprint” application. I was surprised to see no code colouring or syntax checking in the expression editor. Hopefully, this feature will be added soon.

On the positive side, the fact that the developers “eat their own dog food” by using use their own Web service from the Visual Studio IDE means that as developers, we can do everything Microsoft can do with the reporting engine. It is very straightforward to develop code to deploy and render reports, and this area is well covered in the Reporting Services Books Online documentation.

I also like the developer focus of Reporting Services, although I won’t be surprised if an end-user focused report designer is released at some stage in the future in order to round out the product.

I’m generally happy with Reporting Services, it is very much a useable and useful tool in it’s current form, and I intend on continuing to use it. It’s got flaws, but I believe my criticisms are very much “version one” issues. Other reporting tools I’ve used still have their problems despite having been around for years. .NET and SQL Server developers should go and get Reporting Services, and see if it helps to provide better reporting for your customers and users.