To create a report in SQL Server, you must first create a report server project where you will save your report definition (.rdl) file and any other resource files that you need for your report. Then you will create the actual report definition file, define a data source for your report, define a dataset, and define the report layout. When you run the report, the actual data is retrieved and combined with the layout, and then rendered on your screen, from where you can export it, print it, or save it.

In this lesson, you will learn how to create a report server project in Business Intelligence Development Studio. A report server project is used to create reports that run on a report server.

To create a report server project

---

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click Business Intelligence Development Studio.

2. On the File menu, point to New, and then click Project.

3. In the Project Types list, click Business Intelligence Projects.

4. In the Templates list, click Report Server Project.

5. In Name, type Tutorial.

6. Click OK to create the project.

   The Tutorial project is displayed in Solution Explorer.

To create a new report definition file

---

1. In Solution Explorer, right-click Reports, point to Add, and click New Item.

2. In Add New Item, click Report.

   The following diagram illustrates the Add New Item dialog box, used for adding reports and other items to a project.

   

3. In Name, type Sales Orders.rdl and then click Add.

   The Report Designer opens and displays the new .rdl file in the Data view.

Report Designer is a Reporting Services component that runs in Business Intelligence Development Studio. It has three views: Data, Layout, and Preview. Click each tab to change views.

You define your data in the Data view. You define your report layout in the Layout view. You can run the report and see what it looks like in the Preview view.

Setting Up Connection Information

After you add a report to the Tutorial project, you need to define one data source that provides data to your report. In Reporting Services, data that you use in reports is contained in a dataset. A dataset includes a pointer to a data source and the query to be used by a report.

In this tutorial, you will use the AdventureWorks sample database as your data source. This tutorial assumes that this database is located in the default instance of SQL Server Database Engine installed on your local computer.

To set up a connection

---

1. In the Data tab, expand the contents of the Dataset drop-down list at the top of the page, and select New Dataset. The Data Source dialog box appears.

2. In Name, type AdventureWorks.

3. In Type, select Microsoft SQL Server.

4. In Connection string, type the following:

   Data source=(local); initial catalog=AdventureWorks
   

   This connection string assumes that Business Intelligence Development Studio, the report server, and the AdventureWorks database are all installed on the local computer and that you have permission to logon to the AdventureWorks database.

   If you are using SQL Server 2005 Express Edition with Advanced Services or a named instance, the connection string must include instance information:

   Data source=localhost\SQLEXPRESS; initial catalog=AdventureWorks
   

   For more information about connection strings, see Connecting to a Data Source and Data Source (General Tab, Report Designer).

5. Click OK. AdventureWorks is added to the DataSets pane.

The following diagram illustrates the Data Source dialog box used for specifying a connection to a data source.

Defining a Query for the Report

After the data source is defined, Report Designer creates a dataset and displays the generic query designer, which you can use to design the query. For this tutorial, you will create a query that retrieves sales order information from the database.

The generic query designer is the default query design tool because it accommodates complex Transact-SQL statements and it does not format or validate the statements until you run the report. However, if you are more familiar with the graphical query designer, you can use it instead of the generic query designer. A toggle button on the query designer toolbar is provided so that you can switch tools. For more information about query designers, see Query Design Tools in Reporting Services.

To define a Transact-SQL query for report data

---

1. Type, or copy and paste, the following query into the SQL pane of the generic query designer. The SQL pane is the top pane in the design tool. The diagram located below these steps show you where the query should be specified.

   SELECT  S.OrderDate, S.SalesOrderNumber, S.TotalDue, C.FirstName, C.LastName
   FROM    HumanResources.Employee E INNER JOIN
           Person.Contact C ON E.ContactID = C.ContactID INNER JOIN
           Sales.SalesOrderHeader S ON E.EmployeeID = S.SalesPersonID
   

2. To view the results of the query, click the Run (!) button on the query designer toolbar.

The following diagram shows the generic query designer with the query copied into the SQL pane. Notice that the toggle button for enabling the generic query designer is selected. You can click this button if you want to use the graphical query designer instead.

Adding a Table Data Region

1. After the query is defined, you can start defining the report layout. The report layout consists of tables, text boxes, images, and other items that you want to include in your report. In Reporting Services, items that contain repeated rows of data from underlying datasets are data regions. You create a report layout by dragging and dropping data regions and other report items onto the design surface in the Layout tab. Once you add data regions, you can choose which fields to add to each data region.

   To add a table data region and fields to a report layout

   ---

   1. Click the Layout tab.

   2. In the Toolbox, click Table, and then click on the design surface. Report Designer draws a table, with three columns, spanning the width of the design surface.

   3. In the Datasets window, expand the report dataset to display the fields.

   4. Drag the OrderDate field from the Datasets window to the middle (detail) row of the first column in the table.

      When you drop the field into the middle cell, two things happen. First, the detail cell will contain the following text: "=Fields!OrderDate.Value". This text is a field expression that specifies data values for the OrderDate field. Fields that you add to a Detail row are always specified as expressions. Second, a column header value is automatically placed in the first row, just above the field expression. By default, the column is the name of the field.

   5. Drag the SalesOrderNumber field from the Datasets window to the middle (detail) row of the second column in the table.

   6. Drag the TotalDue field from the Datasets window to the middle (detail) row of the third column in the table.

   The following diagram shows a table data region that has been populated with these fields: OrderDate, SalesOrderNumber, and TotalDue.

   

   
   

   Previewing the Basic Report

   At this point in the tutorial, you can preview the contents of the table. Previewing a report allows you to easily view the appearance of the report without having to go through the extra steps of publishing it to a report server.

   To preview a report

   ---

   1. Save the report project. On the File menu, click Save All.

   2. Click the Preview tab. Report Designer runs the report and displays it in Preview view.

   The following diagram shows the report in the Preview window.