How to create a SQL Server Reporting Services (SSRS) report

SQL Server Reporting Services (SSRS) is part of Microsoft SQL Server services since version 2000. If you are an ordinary user with a basic knowledge of SQL, this article will help you in creating your first SSRS report.

Requirements for creating a SSRS report

• Microsoft SQL Server 2014 database engine
• SQL Server 2014 reporting service (SSRS)
• SQL Server data tools
• AdventureWorks2012 database is used for this article, as data source.

Create a report server project

The first thing to do is to create a report server project because we will need this later for saving report definition files and any other files that you need for creating SSRS reports.

• The first step is to open SQL Server Data Tools. Click on the File menu, find New and then Project.
• After this, you need to click on the Business Intelligence.
• Click on the Reporting Services and then Report Server Project.
• If you want to display Report test project to get you started, you can type Report test in Name
• The last step is to click OK to finish.

Create a new report definition file

• In the View menu find the Solution Explorer, and then right-click on the Reports folder. In the Add section click New Item.
• In the window Add New Item, click Report.
• As you can see Report Designer has two available views. In Design view you define your report layout, and in the Preview view you can run your report.

Defining data source and dataset

Setting up a connection

In order to retrieve data from a database or from some other resource, you will need to define the data source. In the following section, you will see how you can define the data source. We will use AdventureWorks2012 database as an example.

• In the View menu find and click Report Data, then New and after that Data Source
• Type AdventureWorks2012 in the field Name
• Select Embedded connection
• As Type select Microsoft SQL Server
• In Connection string field type:
  • Data source=localhost; initial catalog=AdventureWorks2012

  

Note: If the database is not on the local computer, replace localhost with name of your database server instance.

• After this, click on the Credentials and then Use Windows Authentication.
• Click OK, and you’re done.

Define a T-SQL query for report data

In this section you will learn how to specify a dataset. You will need some basic SQL knowledge in order to create a query and define what information you will need from your database. The query you will see is just an example, and for your own purposes and database, you will have to change that step.

• In the Report Data pane find and click New, and then Dataset.
• In the Dataset Properties dialog box as Name type DataSet1
• Make sure to check Use a dataset embedded in my report.
• Then select a AdventureWorks2012 as your data source,
• Check Text as a Type, and type this into the Query input: