How to Create A Table Report Using Visual Studio Report Designer

Introduction

In this tutorial, you will use the Report Designer tool in Visual Studio / SQL Server Data Tools (SSDT). You will create a SQL Server Reporting Services (SSRS) paginated report.

The report contains a query table, created from data in the sample database.

As you progress in this tutorial, you're going to learn how to

• Create a report project.
• Set up a data connection.
• Define a query.
• Add a table data region.
• Format the report.
• Group and total fields.
• Preview the report.
• Optionally publish the report.

System requirements

Your system must have the following components installed to take this tutorial

• Microsoft SQL Server database engine.
• SQL Server 2019 Reporting Services or later (SSRS).
• The Sample database. For more information, see Sample Databases.
• SQL Server Data Tools for Visual Studio along with the Reporting Services extension installed to enable access to the Report Designer.

You must also have read-only permissions to retrieve data from the sample database.

Creating a Report Server project ( Reporting Services)

In this lesson, you create a report server project and a report definition (.rdl) file using Report Designer.

Note. SQL Server Data Tools (SSDT) is a Microsoft Visual Studio environment for creating business intelligence solutions. SSDT features the Report Designer authoring environment, where you can open, modify, preview, save, and deploy Reporting Services paginated report definitions, shared data sources, shared datasets, and report parts.

When you create reports with Report Designer, it creates a report server project that contains the report files and other resource files used by the report(s).

To create a report server project

From the File menu, select New > Project.

Select Reporting Services. In some cases, it may be under the group Business Intelligence.

Important

In Visual Studio, if you don't see Reporting Services in the left column, add the Report Designer by installing the SSDT workload From the Tools menu, select Get Tools and Features and select the SQL Server Data Tools from the workloads displayed.

If we don't see the Report Services objects in the center column, add the Reporting Services extensions. From the Tools menu, select Extensions and Updates Online. In the center column, select Microsoft Reporting Services Projects Download from the displayed extensions. For SSDT, See Download SQL Server Data Tools (SSDT).

1. Select the Report Server Project in the center column of the New Project dialog box.
2. In the Name text box, type "Tutorial" for the project name. By default, the Location text box displays the path to your "Documents\Visual Studio 20xx\Projects" folder. Report Designer creates a folder named Tutorial below this path and creates the Tutorial project in this folder. If the project doesn't belong to a VS solution, then VS also creates a solution file (.sln).
3. Select to create the project. The Tutorial project is displayed in the Solution Explorer pane on the right.

Creating a report definition file (RDL)

1. In the Solution Explorer pane, right-click on the Reports folder. If you don't see the Solution Explorer pane, select View menu Solution Explorer.
2. Select Add New Item.
   
3. In the Add New Item window, select the Report icon.
4. Type "SampleOrder.rdl" into the Name text box.
5. Select the Add button on the lower right side of the Add New Item dialog box to complete the process. Report Designer opens and displays the SampleReport report file in Design view.
   

Next steps

So far you've created the Tutorial report project and the SampleReport report. In the remaining lessons, you're going to learn how to

• Configure a data source for the report.
• Create a dataset from the data source.
• Design and format the report layout.

Specifying connection information (reporting services)

In this example, we're going to define a data source, the connection information the report uses to access data from a relational database or other sources.

For this report, you're going to add the Sample database as your data source. This tutorial assumes that the database is located in the default instance of the SQL Server Database Engine and installed on your local computer.

To set up a connection

1. In the Report Data pane, select New Data Source. If the Report Data pane isn't visible, then select View menu Report Data.
   
2. The Data Source Properties dialog box opens with the General section displayed.
   
3. In the Name text box, type "Sample".
4. Select the Embedded Connection radio button.
5. In the Type dropdown selection box, select "Microsoft SQL Server".
6. In the Connection string text box, type the following string Data source=localhost; initial catalog=sample.

Note. This connection string assumes that SQL Server Data Tools (SSDT), the report server, and the sample database are all installed on the local computer.

Change the connection string and replace localhost with the name of your database server/instance if the assumption isn't true. If you're using SQL Server Express or a SQL Server named instance, you need to modify your connection string to include instance information. For example,

Data Source=localhost\SQLEXPRESS;Initial Catalog=sample

For more information about connection strings, you can refer to the See also section below.

1. Select the Credentials tab, and under the section Change the credentials used to connect to the data source, select the Use Windows Authentication (integrated security) radio button.
2. Select OK to complete the process.
3. Report Designer adds the data source Sample to the Report Data pane.
   

Defining a Dataset for the Table Report (Reporting Services)

After you define the data source, you need to define a dataset. In Reporting Services, data that you use in reports is contained in a dataset. A dataset includes a pointer to a data source and a query to be used by the report, calculated fields, and variables.

Use the Query Designer in Report Designer to define the dataset. For this tutorial, you're going to create a query that retrieves sales order information from the Sample database.

Define a SQL query for report data

1. In the Report Data pane, select New Dataset The Dataset Properties dialog box opens with the Query section displayed.
   
2. In the Name text box, type sample.
3. Below that, select the Use a dataset embedded in my report radio button.
4. From the Data Source dropdown box, select Sample.
5. For the Query type, select the Text radio button.
6. Type, or copy and paste, the following SQL query into the Query text box.

Syntax

SELECT StudentName, StudentAddress
FROM StudentDetail re
INNER JOIN OrderDetails ON re.StudentID = re.StudentID;

1. Select the Query Designer button -- the query is displayed in the text-basedQuery Designer. View the results of the query by selecting the run button on the Query Designer toolbar. The dataset displayed contains six fields from four tables in the sample database
2. The query makes use of SQL functionality such as aliases. For example, the SalesOrderHeader table is called soh.
3. Select OK to exit the Query Designer.
4. Select OK to exit the Dataset Properties dialog box.

The Report Data pane displays the sample dataset and fields.

Adding a Table to the Report (Reporting Services)

After the dataset is defined, you can start designing the report. You create a report layout by dragging and dropping report objects from the Toolbox pane to the Design surface. Some of the types of report objects include,

• Table
• Text Box
• Image
• Line
• Rectangle
• Chart
• Map

Items that contain repeated rows of data from underlying datasets are called data regions After you add a data region, you can add fields to the data region. A basic report will have only one data region. You can add additional ones to display more information such as a chart.

Add a table data region and fields to a report layout

1. Select the Toolbox tab in the left pane of the Report Designer. With your mouse, select the Table object and drag it to the report design surface. Report Designer draws a table data region with three columns in the center of the design surface. If you don't see the Toolbox tab, select View menu >Toolbox
   
2. You can also add a table to the report from the design surface. Right-click the design surface and select Insert Table.
3. In the Report Data pane, expand the SampleDataset to display the fields.
4. Drag the [StudentName] field from the student pane to the first column in the table.
   

Important

When you drop the field into the first column, two things happen. First, Report Designer displays the field name, known as the field expression, in brackets: [StudnetId] in the data cell.

Second, it adds a column label to the header row, just above the field expression. By default, the column label is the name of the field.

You can select the column label and type a new value if you want to change it.

1. Drag the [StudentId] field from the Report Data pane to the first column in the table.
2. Drag the [StudentName] field from the Report Data pane to the second column in the table.
3. Drag the [StudnetAddress] field from the Report Data pane to the third column in the table.
4. Drag the[StudentCity] field to the right edge of the third column until you get a vertical cursor and the mouse pointer displays a plus sign [+]. When you release the mouse button, a fourth column is created for the [StudntCity] field expression.
5. Add the[StudentCity] field in the same way, creating a fifth column. The column label is added as StudentCity. Report Designer automatically creates a friendly name for the column by splitting StudentCity into two words.

The following diagram shows a table data region that has been populated with these fields: StudentId, StudentName, StudentAddress, and StudentCity

Summary

In this article, you learned how to use SQL to create a basic Table Report with various options.