SSRS (RDL) Report Design Using Microsoft Report Builder

Introduction

 
Microsoft Report Builder, which is provided by Microsoft,  is an easy tool to use to build dashboard and graphical reports with less effort. This article will describe simple steps to build a report using the Microsoft report builder tool. In this part, the article will explain the concept of report generation with an example starting from the beginning steps: How to connect with data source or database from MS report builder, steps to create datasets, and how to build SSRS Reports using SQL Server database. Additionally, it will show how to write simple expressions in reports and represent report data in tabular form. Learners will be able to build reports including expression and representation of data in table form from scratch.
 
Prerequisites
  1. SQL Server
  2. SSRS (SQL Server Reporting Services)
  3. MS Report Builder
Follow the below steps to build your report using Microsoft Report Builder.
 
Step 1
 
Open Report Builder. The below screen will pop up.
 
Then select a Blank Report. However, you can opt to Table or Matrix Wizard, Chart Wizard, and Map Wizard from Report builder.
 
Build SSRS (RDL) Report From Microsoft Report Builder
Step 2
 
Click on Add Data Sources and then Add data Source.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Step 3
 
Give the name of your Data source and select Use a connection embedded in my report. However, if you want to use a shared connection or report model you can select that also.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Step 4
 
Select Connection Type: You can select connection type as depicted in the below image. In my case, the database is in MS SQL Server, So I have selected Microsoft SQL Server.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Then click on Build.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Step 5
 
Provide Server Name, Authentication: User Id, Password, select Database, and then click Test Connection.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Clicking on OK will popup the success message box.
 
Step 6
 
Subsequently, click on OK on the below screen
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Step 7
 
The next step is to add a Dataset. For this, Click on Add Datasets
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Step 8
 
Insert Dataset Name, select a Data source, write a query to get data from your database. For demo purposes, I am going to use the Employee Table from my database.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Step 9
 
Then click on Query Designer as depicted below
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Step 10
 
Click on Run Command, it runs the command and gets all the selected values in the list. After that click on OK.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Step 11
 
Now your dataset is ready to use. All the selected columns will be listed in your dataset
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Step 12
 
Now it’s time to design the report. Firstly, give the title of your report. Click on Click to add title area and give your report name. In my case, I am giving Employee Report as Title of the report.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
MS Report Builder
 
If you go to Insert option then, you can see a lot of options to design reports. MS Report builder provides varieties of drag and drop options to design interactive graphical reports with less effort.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
If you right-click on body and go to insert option from there also you can get the option to format your report as well as insert graphical tools such as a table, chart, line text, header, footer, etc for the report.
 
 

How to Show the Total Number of Employees

 
Select the Rectangle, fill the color of the rectangle as per your choice, insert Text Box inside it, and insert another text box for Expression to get a total number of the count. The purpose of using two text boxes is that one is to show Label and another is to show the total number of count using an expression.
 
Right-click on empty textbox as shown below and click on the expression.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
To get the total number of count write the following expression,
 
=Count(Fields!Id.Value)
Or
=Count(Fields!Id.Value,"YourDatasetName")
 
If you have two or more datasets, then it is necessary to provide a dataset name. I have a single dataset so I don't need to provide a dataset name in my expression.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Report Data in Table
 
Right-click on body, go to insert, and click on the table.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Select the column that you want to show in the table and drag it and drop inside the table column as illustrated below.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
To add more columns in the table, select the column -> right click and go to insert column and then choose where you want to add on the right side of the selected column or left side.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
In my tabular report, I have selected Name, Citizenship No, Nationality, Marital Status, Gender, Branch, and Telephone No. in the table, and during the run, my tabular report looks like as depicted below.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 
Table Design Format
 
We can format the design of the table according to our wishes. To change the foreground color of heading: select the first row of the table and go to fill option and choose the colour of your choice. Furthermore, we can change the style of the font from the available options. Besides this, we can drag and stretch the size of the column as well as drag and move the whole table and relocate it according to our needs.
 
Build SSRS (RDL) Report From Microsoft Report Builder
 

Conclusion

 
Hence, this article described the simple steps to build a report using Microsoft Report Builder using the SQL Server database. The article demonstrated steps to define data source or connect database from MS report builder, create datasets, and build SSRS Reports using the SQL Server database which would be helpful for beginners. Learners will be able to write/understand the basic knowledge of expressions and show the report data in tabular format in the report. In the next part, I will explore and demonstrate more about bar graph and pie chart design for dashboard and graphical reports.