SSRS Report Builder introduction and tutorial

The SSRS report builder is a powerful tool for data visualization. In this article, we will take a first step into familiarizing ourselves with the SSRS report builder and we will also demonstrate a very basic SSRS report example. Additionally, we will review the self-service business intelligence approach just because this approach can be implemented through the SSRS report builder very easily.

Self-Service BI

1. We can hire more developers
2. We can hire freelancers
3. Or we can implement the Self-Service BI approach

#C is the right and effective solution for this issue because non-technical persons can learn to develop their own reports and dashboards through prepared datasets. Key stakeholders can gain the ability to develop and create their own reports so that the IT department dependency and waiting time will decrease. To accomplish this, though, report and BI tools must be so easy and understandable by non-technical users.

The purpose of the Self-Service BI approach is to provide report or dashboard development capability to non-technical users thus, will give more freedom and responsibility. The main question is how we can implement this approach with SQL Server Reporting Services? Microsoft has obviously taken this into consideration because the SSRS report builder is a very simple report design tool and they can be easily learned and used by non-technical staff. At the same time, BI developers can provide key users with shared datasets, to abstract users from learning how to retrieve data from desired data warehouse or relational database. Additionally, SQL Server Reporting Services offers a significant feature which is the My Report Folder. This feature offers a private and personal storage to users so that users can deploy and use their SSRS report in this folder. As a result, Self-Service BI allows non-technical staff to generate report by themselves so that it can decrease the costs which related to business intelligence implementation.

Preparing to sample data

Before we start our SSRS report builder demonstration, we need a sample dataset. For this, we will download the sample sales csv file and load it to Azure SQL database. It is very basic operation so we can complete the following steps easily. Additionally, you can apply the following steps on premise installation of any SQL Server version.

• Connect to Azure SQL or any premise installation through SQL Server Management Studio
• Right click in your test database and then navigate to Tasks >Import Flat File…

Chose the csv file and specify a new table name to which the data will be imported.

Click Next in the Preview Data step

Click Next in the Modify Columns step

Click Finish in the Summary step

The operation will be completed successfully

Create our first report with SSRS Report Builder

The SSRS Report Builder is a report creation tool which allows users to create, manage and publish reports to SQL Server Reporting Services. We can also create shared datasets with the help of the report builder. The Report builder has a standalone installation so we can easily setup and configure it. We can find out the installation link in the web portal of SQL Server Reporting Services. When we click this link it redirects to download page.

We will skip the SSRS report builder installation steps because it is pretty self-explainable. When we launch the report builder, “Getting Started” screen is displayed. In this screen, we have four options;

1. New Report: In this tab, we can quickly create reports through the various wizard options or we can choose the blank report option. In the blank report option, we need to set some options manually
2. New Dataset: This option enables creating shared-datasets
3. Open: With help of this option, we can open reports which are stored in the folders or in the SQL Server Reporting Services
4. Recent: In this tab, we can find out the reports created previously

We will select the Blank Report option and then report builder design page will appear.

The Report builder main screen is not very complicated and it allows us to create reports easily. Let’s demonstrate creating a report and learn much more about the SSRS report builder. In this demonstration, we will accomplish the following steps;

• Create a datasource connection
• Create a dataset
• Design report with report builder
• Deploy created report builder report to My Folder path

Create a datasource

In the right side of the report builder we can see the Report Data panel which helps to manage Data Sources and Datasets and also in this panel we can manage report parameters and built-in fields. Now, we will create a connection between Azure SQL and report builder. Right click in the Data Sources folder and then select Add Data Source.

SQL Server Reporting Services allows us to use two types of connection methods;

• Use a shared connection or report model: In this method we can use a shared data source. This type of data sources helps us to avoid repeated data source because this type data source can be used number of times according to report user authorizations
• Use a connection embedded in my reports: This method creates individual connection and also, we have to create connection in the report. Now, let’s return to our demonstration after this step, the Data Source Properties screen appears so we can add target data source which we want to connect. In this screen, we can find out various data sources, however we will select Microsoft Azure SQL Database and Use a connection embedded in my reports. We build click Build to set connection properties and credentials

Fill the Server name and other required connection settings and then check the connection with Test Connection.

Create a dataset

In this step we will create a dataset, we use datasets in order to retrieve data from target data source. Right click on Datasets folder and select Add Dataset… and then we will select a datasource which we created in the Create a datasource connection step and write a very basic query which returns all data of the SalesTbl