Are you looking for the steps to generate SSRS reports? We can help you with it.

SSRS is SQL Server Reporting Services which build custom reports from data sources like SQL databases.

At Bobcares, we often get requests regarding the SSRS report generation as a part of our Server Management Services.

Today, let’s see how our Support Engineers generate the SSRS report for our customers.

Explore more about SSRS

The SSRS is a server-based report generating software which generates formatted reports with tables in the form of data, graph, images, and charts.

Main advantages of SSRS are

• It is faster and cheaper.
• It efficiently reports the information that is residing in both the MS SQL Server database or Oracle.
• SSRS displays data in a variety of formats which include tabular, free-form, and charts.

This SSRS service provides an interface into Microsoft Visual Studio. As a result, developers, as well as SQL administrators, can connect to SQL databases and use SSRS tools to format SQL reports.

How to Generate SSRS reports

We often get requests from our customers to generate SSRS reports. Let’s now see how our Support Engineers help the customers to create the SSRS report.

For creating an SSRS report we must have the following components installed in the system.

• Microsoft SQL Server database engine.
• SQL Server Reporting Services (SSRS).
• SQL Server data tools
• AdventureWorks database

Create a report server project

Initially, we have to create a report server project. This saves the report definition files and any other files need to create the SSRS reports.

1. Initially, we open SQL Server Data Tools that is the Visual Studio.
2. Then click on the File menu, find New and then Project.
3. After this, we click on Business Intelligence.
4. Then we select the Reporting Services and then Report Server Project.
5. In the Name section, we type the display name and click ok to finish.

Create a new report definition file

The next step is to create a report definition file. A report definition contains layout information for a report.

1. In the View menu, we find the Solution Explorer and then right-click on the Reports folder.
2. Then we select the Add section click New Item.
3. In the window Add New Item we click Report.
4. We type a name into the Name text box.
5. We select the Add button on the lower right side of the Add New Item dialog box to complete the process.

Defining data source and dataset

Here we have to define a data source and it is used to retrieve data from a database or from some other resource. We use AdventureWorks as a database.

1. Initially, we click Report Data from the View menu. Then click New and Data Source.
2. In the Name text box, we type AdventureWorksX.
3. Select the Embedded connection.
4. As Type, we select Microsoft SQL Server.
5. Next in the Connection string we type ‘Data source=localhost; initial catalog=AdventureWorksX’. If the database is not on the local computer, we replace the localhost with the name of the database server.
6. After this, click on the Credentials and then Use Windows Authentication.
7. Click OK

After that, we define a T-SQL query for report data.

In this, we need to specify the dataset. The dataset includes a pointer to a data source and a query.

1. In the Report Data pane we click New, and then Dataset.
2. Next in the Name text box, we type the name for the Dataset.
3. Also, we make sure to select the Use a dataset embedded in my report radio button.
4. Then from the Data source dropdown box, we select AdventureWorksX.
5. For the Query type, we select the Text radio button.

Here we write a query about the information that we need from our database and click OK.

Add a table and fields to a report layout

In this step, we add tables and fields to our report.

1. Firstly in the View menu click Toolbox.
2. Then select the Table object and drag it to the report design surface.
3. From the left pane, we expand our dataset in order to see all the fields.
4. Then Drag one of the fields to the column in the table and table will automatically add more columns

We preview the report by click the tab Preview.

Formatting the Report

In this, we can format the date, time, percentage, etc. To format the Date field we follow the steps below:

1. In the Design tab, right-click the desired cell, then click Text Box Properties.
2. Then in the Number tab, we select Date under the Category.
3. we select the date format and click Ok to finish.

Likewise, we can format time, currency, date, etc.

Adding Grouping and Totals

Grouping is to group the data set in the SSRS report. For that, we do the following steps.

• We Click the Design tab and then choose pane Row Groups.
• From the Report Data pane, we drag the needed fields to the Row Groups pane.

To add totals for a group, we do that by clicking Add Total for the group in the Grouping pane.

To preview the format changes, we select the Preview tab.

Publish the report to the report server

Finally, we want to publish the SSRS report.

After the configuration of the report server, we need to set the properties of the project. For that right-click on the project then select the Properties option.

Then we configure the target server URL.

http://Servername/reports

Servername is the server where the Report Manager is deployed.

Finally, we deploy the report.

[Need any assistance to generate SSRS Report? – We’ll help you]

Conclusion

In short, SSRS is SQL server reporting services that build custom reports from data sources. Today, we saw how our Support Engineers generate SSRS reports for our customers.