Microsoft Sql Server 2005 Reporting Services: Basic Report

Tutorial
Lets start with creating a basic report:

Prerequistes:

  • 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.

Report Design

  • 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.

Report Preview

  • 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! 😉

    Advertisements

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    Blog at WordPress.com.

    Up ↑

    %d bloggers like this: