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.
Follow the below steps to build your report using Microsoft Report Builder.
Open Report Builder. The below screen will pop up.
Step 2
Click on Add Data Sources and then Add data Source.
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.
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.
Then click on Build.
Step 5
Provide Server Name, Authentication: User Id, Password, select Database, and then click Test Connection.
Clicking on OK will popup the success message box.
Step 6
Subsequently, click on OK on the below screen
Step 7
The next step is to add a Dataset. For this, Click on Add Datasets
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.
Step 9
Then click on Query Designer as depicted below
Step 10
Click on Run Command, it runs the command and gets all the selected values in the list. After that click on OK.
Step 11
Now your dataset is ready to use. All the selected columns will be listed in your dataset
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.
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.
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.
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.
Report Data in Table
Right-click on body, go to insert, and click on the table.
Select the column that you want to show in the table and drag it and drop inside the table column as illustrated below.
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.
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.
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.
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.