Express Essentials: Using Reporting Services

The powerful and free Reporting Services component of SQL Server 2005 Express with Advanced Services lets you quickly and easily create reports in a variety of formats based on the data stored in one or more SQL Server Express databases. As I explained in "Preparing to Use Reporting Services," the Reporting Services subsystem that comes with SQL Server Express has many of the same characteristics as the Reporting Services subsystem that’s shipped with full-blown editions of SQL Server. One notable limitation, however, is that Reporting Services in SQL Server Express 2005 with Advanced Services can connect to data only from a local SQL Server Express database--you cannot use it to connect to remote data sources.

To create reports by using SQL Server Express Reporting Services, you first need to open Business Intelligence Development Studio (click Start, All Programs, Microsoft SQL Server 2005, SQL Server Business Intelligence Studio). Then click File, New, Project to display the New Project dialog. Select Business Intelligence Projects from the Project Types list, then select Report Server Project Wizard from the list of installed templates. Use the Name field to give your Reporting Services project a name. For learning purposes, you can simply accept the default name of Report Project1. If you wish, you can use the Location field to change where your project will be stored. Clicking OK starts the Report Wizard.

Click past the initial Welcome dialog to display the Select the Data Source dialog. You can rename your new data source or use the default name of DataSource1. Click the Edit button next to the Connection String text box to display the Connection Properties dialog. From the "Server name" drop-down list, select the instance name of your SQL Server Express with Advanced Services system. If you used the default installation value for SQL Server Express, that name will be SQLEXPRESS.

Next, use the "Select or enter a database name" drop-down list to select the local SQL Server Express database from which you want to create a report. For sample purposes, I’ll use the new AdventureWorksLT database. Download AdventureWorksLT.

The next step is to build a query to retrieve the data that you want your report to use. If you’re handy with T-SQL, you can simply enter the appropriate T-SQL SELECT statement. Otherwise, click Query Builder to build your query interactively. Click the Add Tables icon on the far right side of the toolbar, then select the desired tables. To keep my example simple, I'll select just one table--SalesOrderHeader--then click Close. To add all the columns to my report, I select the * (all Columns) check box and click OK, which completes the query portion of the report design process.

To format the report, click Next when you’re returned to the Design the Query menu; you'll see the Select the Report Type dialog. Choose a report type--I'll choose Tabular--then click Finish twice. The report will be displayed in the Business Intelligence Development Studio Layout window, where you can make formatting changes. Click the Preview tab to render the report in the Report Designer window, then save your project.

That’s it for basic report creation. Reporting Services provides a variety of formats for reports; I'll tell you more about report formatting and about your options for producing reports in my next column.