Join Dotnetcodes DotnetCodes.com is online Discussion Forum for Software professionals . It lets you find friends around the world and Create professional network that share similar interests as you. Get help on ur projects by industry specialists. Also get answers to all ur technical/placement related querries.Get an edge over others.
Already MemberClick here to login
ASP.net MVC Interview Questions Answers Interview Questions
Get Started Developing for Android Apps with Eclipse Articles
Serial Number in SSRS Articles
.Net framework 4.0 Interview Questions Answers Interview Questions
SQL server reporting services Interview Questions (SSRS) part 1 Articles
Whats New in ASP.NET 4.0 Part 2 Articles
How to Print a Crystal Report direct to printer Articles
Difference between Encapsulation and Abstraction Interview Questions
Explaining SDLC -System Development Life Cycle Articles
Html5 interview questions and answers Interview Questions
SharePoint 2010 interview Questions Answers Interview Questions
Infosys Interview Questions Interview Questions
Dynamic Menu using HTML List Tag and CSS in ASP.Net Articles
Populate or bind Dropdownlist in Asp.net using Jquery and Json Articles
ASP.NET MVC Overview Interview Questions
Submit Articles | More Articles..

Creating a Report Server(SSRS) Project

Posted By: rakesh On:9/6/2011 2:54:12 AM in:Articles Category:SSRS Hits:2599
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.....

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.

  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.

  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.

    Add New Item dialog box
  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: DataLayout, 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.

  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 OKAdventureWorks is added to the DataSets pane.

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

Data Source dialog box

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.

  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.

Report query in generic query designer

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.

    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.

    Table data region with fields

    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.

    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.

    Report in Preview window

comments powered by Disqus
User Profile
Rakesh Sinha
Sr. Technical Lead
New Delhi , India
Email :You must Log In to access the contact details.
Latest Post from :rakesh
Response.Redirect vs Server.Transfer
View: 436 | Submitted on: 12/27/2015 2:26:38 PM
The test form is only available for requests from the local machine.
View: 503 | Submitted on: 11/3/2015 9:54:36 PM
Difference between web service WCF and Web API
View: 5711 | Submitted on: 10/28/2015 9:23:51 PM
Which party you want to vote in Delhi assembly elections 2015?
View: 1496 | Submitted on: 12/18/2014 4:19:24 AM
How to Calculate sum of a column in a dataset
View: 1170 | Submitted on: 9/17/2014 6:48:19 AM
Submit Articles | All Post of This User..

All rights reserved to dotnetcodes. Logos, company names used here if any are only for reference purposes and they may be respective owner's right or trademarks.
Best viewed at 1024 x 768 resolution with Internet Explorer 5.0 or Mozila Firefox 3.5 or Google Crome and higher