SSRS Reports Development in Simple Terms

The article is dedicated to the fundamentals of SQL Server Reporting Services (SSRS) development and aimed at beginners and professionals interested in database development.

A direct method in the form of a walkthrough is used to discuss the core concepts and their implementation in regards to SQL Server Reporting Services (SSRS).

The main focus of the article is to give the basic concepts of reports development rather than discuss the latest SQL Server Reporting Services (SSRS) versions and their features.

About SQL Server Reporting Services (SSRS)

First, let’s concentrate on a few important facts about SQL Server Reporting Services (SSRS) in the light of Microsoft documentation.

Microsoft Definition (SSRS 2016 and Later)

Considering SSRS 2016 and later, SQL Server Reporting Services (SSRS) provides a set of on-premises tools and services that are used to create, deploy, and manage mobile and paginated reports.

Simple Definition

SQL Server Reporting Services (SSRS) facilitates database reports development, deployment, and management.

In other words, SQL Server Reporting Services (SSRS) helps you quickly create, deploy, and manage database report(s).

Development Tools

An SSRS report can be created using one of the following tools:

• SSDT (SQL Server Data Tools)
• Report Builder
• 3^rd Party Report authoring tools (including dbForge SQL Server Report Builder).

Сhoice of a Tool

Despite the fact that the 3^RD party report authoring tools offer out of the box features with fancy GUI support, in this article, our aim is not to choose the most convenient report building tool for beginners, but to choose a tool allowing to become familiar with the basics of reports development.

Report Server

Once you have developed the report using one of the development tools, you need to deploy your report to a server called a reporting server that is configured to match your requirements and host all the deployed reports in an organized way more like Windows folder style.

Report Manager

As the name implies, Report manager helps you manage your deployed reports in the form of a web-based portal.

SSRS Report Development

Next, let’s discuss the pre-requisites and steps to quickly create an SSRS report.

Pre-requisites

SSRS reports development assumes the following things:

1. You can write and run basic T-SQL scripts
2. You have basic understanding of SSDT (SQL Server Data Tools) or report builder
3. You have a background in development or have exposure to T-SQL development.

Although it is not mandatory at this point, it is better if you have a readily available SSRS server configured to host your reports.

Report Development Steps

Please consider the following steps while building your SSRS reports when authoring reports using SQL Server Data Tools (SSDT):

1. Create a new Report Server Project in SQL Server Data Tools (SSDT)
2. Create a data source to be selected for your desired database
3. Create a dataset which contains T-SQL to run behind the report
4. Drag drop fields from the dataset to the report designer
5. Test run the report
6. Deploy the report (if you have configured a reporting server).

Setting up a Sample Database

First, set up a sample database which is going to be the data source for your new SSRS report.

You can set up a sample database called SQLDevBlogV4 by using the following script:

-- Create sample database (SQLDevBlogV4)
CREATE DATABASE SQLDevBlogV4;
GO


USE SQLDevBlogV4;

-- (1) Create Article table in the sample database
CREATE TABLE Article (
  ArticleId INT PRIMARY KEY IDENTITY (1, 1)
 ,Category	VARCHAR(50)
 ,Author VARCHAR(50)
 ,Title VARCHAR(150)
 ,Published DATETIME2
 ,Notes VARCHAR(400)  
)

GO

SET IDENTITY_INSERT [dbo].[Article] ON
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (1, N'Development', N'Atif', N'Introduction to T-SQL Programming ', N'2017-01-01 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (2, N'Testing', N'Peter', N'Database Unit Testing Fundamentals', N'2017-01-10 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (3, N'DLM', N'Sadaf', N'Database Lifecycle Management for beginners', N'2017-01-20 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (4, N'Development', N'Peter', N'Common Table Expressions (CTE)', N'2017-02-10 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (5, N'Testing', N'Sadaf', N'Manual Testing vs. Automated Testing', N'2017-03-20 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (6, N'Testing', N'Atif', N'Beyond Database Unit Testing', N'2017-11-10 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (7, N'Testing', N'Sadaf', N'Cross Database Unit Testing', N'2017-12-20 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (8, N'Development', N'Peter', N'SQLCMD - A Handy Utitliy for Developers', N'2018-01-10 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (9, N'Testing', N'Sadaf', N'Scripting and Testing Database for beginners ', N'2018-02-15 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (10, N'Development', N'Atif', N'Advanced Database Development Methods', N'2018-07-10 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (11, N'Testing', N'Sadaf', N'How to Write Unit Tests for your Database', N'2018-11-10 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (12, N'Development', N'Peter', N'Database Development using Modern Tools', N'2018-12-10 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (13, N'DLM', N'Atif', N'Designing, Developing and Deploying Database ', N'2019-01-01 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (14, N'DLM', N'Peter', N'How to Apply Database Lifecycle Management  ', N'2019-02-10 00:00:00', NULL)
SET IDENTITY_INSERT [dbo].[Article] OFF

Creating an SSRS Database Report

To create a new SSRS database report you need to create a new Report Server Project in SQL Server Data Tools (SSDT).

Creating a New Report Server Project

Open Visual Studio to create a new Report Server Project called Articles Report Project under Business Intelligence template provided you have installed SQL Server Data Tools (SSDT):

Adding a New Data Source

The first thing you need to do is to select the source of data for the report which is the sample database SQLDevBlogv4 in our case.

Right-click Shared Data Sources under Articles Report Project and Click Add New Data Source:

Connect to the required SQL instance, select sample database SQLDevBlogV4, and Click OK:

Name the data source DS_SQLDevBlogV4 and Click OK again:

Check the newly created data source:

Test Run Dataset Query

Open a new query window and run the following script against SQLDevBlogV4 to make sure that it pulls the desired data from the database:

-- Report dataset query to view all the articles
SELECT [ArticleId] ,
       [Category] ,
       [Author] ,
       [Title] ,
       [Published] ,
       [Notes]
FROM   dbo.Article;

Run the query to see the results:

Adding a New Dataset

Next, we are going to add a dataset in the form of T-SQL script to run behind the report.

Right-click Shared Datasets under project node and Click Add New Dataset:

Name the dataset DSet_SQLDevBlogV4 and add the query tested above in the input box:

Adding and Building a New Report

Right-click Reports under Articles Report Project node and Click Add New Report:

Skip the welcome screen by clicking Next button and then click Next again after making sure that your shared data source DS_SQLDevBlogV4 is already selected:

Write the same dataset query (which may seem to be an extra step) and Click Next:

Select (if not selected previously) Tabular report type and Click Next:

To design your report drag the fields from the list onto Displayed fields by Clicking Details and then Click Next:

Name the report ArticlesReport and Click Finish:

Go to Report Data and select Use a shared dataset under Datasets, then name the dataset Dset_Articles, Click Refresh Fields, and then OK:

Running the Report

Before running the report you need to do some formatting.

Click headers and detailed fields by holding down CTRL key and then using the toolbar align the text and the change the font and its size as follows:

Click Preview tab to run the report:

Congratulations! You have successfully created an SSRS report to show all the articles written by the authors.

Tasks to Do

Now that you are familiar with SSRS development fundamentals

1. Keeping in mind the example given in this article, please create an SSRS report based on the sample database named TechnicalTraining mentioned in this article.
2. Try to create a report to view duplicates based on the sample database mentioned in this article.