Lets start with creating a basic report:
- Microsoft SQL Server 2005 Reporting Services.
- SQL Server 2005 with the AdventureWorks OLTP database.
- SQL Server 2005 Business Intelligence Development Studio.
Creating a report server object
- Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click Business Intelligence Development Studio.
- 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 Server Project.
- In Name, type Tutorial.
- Click OK to create the project.
Creating a report definition file
- In Solution Explorer, right-click Reports, point to Add, and click New Item.
- In Add New Item, click Report.
- In Name, type Sales Orders.rdl and then click Add. A view opens that contains Data, Layout, and Preview tabs. This is the Report Designer component of Business Intelligence Development Studio. The report opens in Data view.
Setting up a connection
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.
In Name, type AdventureWorks.
In Type, select Microsoft SQL Server.
In Connection string, type the following:
Data source=(local); initial catalog=AdventureWorks
Click OK. AdventureWorks is added to the DataSets pane.
Define a Transact-SQL (T-Sql) query for report data
- 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
- To view the results of the query, click the Run (!) button on the query designer toolbar.
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.
In the Datasets window, expand the report dataset to display the fields.
Drag the OrderDate field from the Datasets window to the middle (detail) row of the first column in the table.
Drag the SalesOrderNumber field from the Datasets window to the middle (detail) row of the second column in the table.
Drag the TotalDue field from the Datasets window to the middle (detail) row of the third column in the table.
Save the report project. On the File menu, click Save All.
Click the Preview tab. Report Designer runs the report and displays it in Preview view.
Next: Adding, Grouping, Sorting, and Formatting to a Basic Report
Tutorial is from Microsoft SQL Server 2005 Reporting Services Documentation. Sorry, Microsoft if it is not permissible, but I like to have your (Microsoft Sql Server) Reporting Services should be easily available to everyone from my blog and continue to do so, you (Microsoft) know why! 😉