Walkthrough - Creating a Basic Report
This tutorial is designed to help you create a basic table report based on the AdventureWorks2000 database. In this tutorial, you will create a report project, set up connection information, define a query, add a table, and preview the report.
Your system must have the following installed to use this tutorial:
- Microsoft SQL Server 2000 Reporting Services.
- SQL Server 2000 with the AdventureWorks2000 OLTP database.
- Microsoft Internet Explorer 6.0 or later.
- Microsoft Visual Studio® .NET 2003 with Report Designer.
You must also have permissions to retrieve data from the AdventureWorks2000 database and to publish reports to the report server.
Create a Report Project
Reports are contained within a report project in Microsoft Visual Studio .NET 2003.
- Click Start, point to Programs, point to Microsoft Visual Studio .NET 2003, and then click Microsoft Visual Studio .NET 2003.
- On the File menu, point to New, and then click Project.
- In the Project Types list, click Business Intelligence Projects.
- In the Templates list, click Report Project.
- In Name, type Tutorial.
- Click OK to create the report project.
Create a Report
Follow these steps to create a report file.
- In Solution Explorer, right-click Reports, point to Add, and click Add New Item.
Note If the Solution Explorer window is not visible, in the View menu, click Solution Explorer.
- In Add New Item, click Report.
- In Name, type Sales Orders.rdl and then click Open. A view opens that contains Data, Layout, and Preview tabs. The report opens in Data view.
Set Up Connection Information
Follow these steps to create a dataset.
- In Dataset at the top of the view, select New Dataset. The Data Link Properties dialog box appears.
- In Select or enter a server name, type the name of the computer on which the AdventureWorks2000 database resides.
Note If Report Designer, the report server, and the AdventureWorks2000 database are all installed on the local computer, type (local).
- For Enter information to log on to the server, select Use Windows NT Integrated security.
- For Select the database on the server, select AdventureWorks2000.
- To verify the connection information, click Test Connection.
- Click OK.
Define a Query
After the data source is defined, Report Designer creates a dataset and displays a query designer that you can use to design the query. For this tutorial, you will create a query that retrieves sales order information from the database.
- Switch to the visual design tool by clicking the Generic Query Designer button on the query designer toolbar.
Note The Generic Query Designer button is a toggle button. When the button is selected, the generic query designer is displayed. Otherwise, the visual design tool is displayed. You may use either designer for this tutorial; however, these instructions assume the visual design tool.
- Type, or copy and paste, the following query into the SQL pane of the visual design tool. The SQL pane is the third of four panes in the visual design tool.
SELECT OrderDate, SalesOrderNumber, TotalDue, FirstName, LastName FROM SalesOrderHeader INNER JOIN Employee ON SalesOrderHeader.SalesPersonID = Employee.EmployeeID
- To view the results of the query, right-click in any pane and then click Run. Alternatively, click the Run (!) button on the query designer toolbar.
Add a Table
After the query is defined, you can start defining the report layout.
- Click the Layout tab.
- 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.
Note If the Toolbox is not visible, in the View menu, click Toolbox.
- Drag the OrderDate field from the Fields window to the first cell of the middle (detail) row in the table.
Note If the Fields window is not visible, in the View menu, click Fields.
- Drag the SalesOrderNumber field from the Fields window to the second cell of the middle (detail) row in the table.
- Drag the TotalDue field from the Fields window to the last cell of the middle (detail) row in the table.
Note You will not use all of the fields in the query in this tutorial. The additional fields are used in a later tutorial.
Preview the Report
At this point in the tutorial, you can preview the contents of the table.
- Click the Preview tab.
You can also debug the report and view it in a separate window.
- In the Solution Explorer window, right-click the Tutorial project, and then click Properties.
- Click Configuration Manager.
- In the Configuration Manager dialog, in Active Solution Configuration, select DebugLocal.
- Click Close.
- In the Tutorial Property Pages dialog, in StartItem, select Sales Orders.rdl.
- Click OK.
- Save the report project. On the File menu, click Save All.
- Preview the report. On the Debug menu, click Start. The report is displayed in a separate preview window.