Introduction
This article describes what is Power BI Report Builder and how to get started with Power BI Report Builder to design a paginated report. In this article I will describe power BI Report Builder, it is the purpose, and steps to build a report using it with an example. We will use SQL Server Database as the data source in the demonstration to design the report.
Prerequisites
- SQL Server
- Power BI Report Builder
You can download the Power BI Report Server from
here.
What is Power BI Report Builder?
Power BI Report Builder is a tool to design paginated reports. It is a standalone tool to build reports which is formattable to fit on the page and called a paginated report. We can connect to a plethora of data sources from Power BI Report Builder and develop reports. We can design a report using the following data sources,
- SQL Server
- SQL Server Instances
- Azure SQL database and Data Warehouse
- Azure Analysis Services
- Power BI Datasets
- Oracle
- Teradata and so on
Furthermore, we can design graphical reports and visualize data using Power BI Report Builder and can publish it to Power BI Service and Power BI Report Server. It has run option, so that we can run and preview the reports while building the report without publishing. Additionally, in Power BI Report Builder, we can modify SSRS reports built by using SQL Server Data Tools (SSDT) Report Designer.
Features of Power BI Report Builder
- Reports from variety of Data sources
- Filter, Group, data sorting, formulas or expression are available.
- Interactive features are available for example document maps, sub reports, drill through reports, parameters for filters to filter data to customize the view.
Purpose of Power BI Report Builder
As article has already described the major purpose is to design paginated reports for Power BI users.
The main benefit of paginated report is that you can print all the data available on the table of the report. If you have large data in the table and you need to scroll the table to view all record in non paginated Power BI report. When you print this non paginated report, you can only be able to print the data that are visible, or you saw in the page . All the hidden data inside scroll bar will not be printed. But when you use paginated report for same data in the table then you can print all the data available inside the table of the report. That is the major benefit of paginated reports.
Same thing happens to export to PDF, you can export all the data available in the table to PDF format when you use paginated reports. On the other hand, only visible part of table data can be exported to pdf with reports designed from Power BI Desktop and Power BI Service.
Designing Paginated Report
Let us design paginated report using Power BI Report Builder. Follow below steps,
Step 1
Open Power BI Report Builder, you will get below screen and click on New Report as depicted.
Then you will get below screen, where you can give name of your report in Title area and can expand the body area by dragging from corner area just below the Execution Time as shown below.
Step 2
After giving name of report, you can format text size color and font as depicted in below image.
Step 3 - Data source
Right click on Data source you will get option to connect to Power BI service dataset which is already there in the Power BI service. In this article I will connect to SQL Server database so to connect it click on Add Data Source option as demonstrated below.
Step 4
In this step give name of data source and we can see here options to connect with different types of data sources in connection Type. Select Microsoft SQL Server as highlighted.
Then click on Build button.
You will get below screen where you have to give connection property of your database such as server name, authentication type, username, password and database name of your data source.
Then test the database connection clicking on Test Connection button. If the connection is succeeded you can click on OK. Otherwise your database credential is incorrect need to correct it.
Now, you will get the previous screen containing connection string in the connection string text area. Click on OK.
In this way, data source is created. Next step is to add Dataset.
Step 5 - Add Dataset
Right click on Data source and click on Add Dataset as shown below.
You will get below screen, give name of dataset, and write query to get data from your database table. Here, you can simply write select Text and write query in the Text as well as you can get from store procedure also.
In this example, I am getting data from Employee Detail table as depicted above. Then click on Query Designer.
You will get below screen where you can change your Test query and query type again if you want. Execute the query by clicking on execute icon then it executes the query and data will be loaded which you can see in the below picture. Subsequently, click on OK as illustrated. You will redirect to previous screen, there also click on OK.
Your Dataset is created successfully, you can see in left side as shown below.
Step 6 - Design Table
To insert table, right click on body of report and select insert then select Table. However, you can insert table from top bar from Insert section as depicted below.
First row of table contains table header and next row contains the data. Your blank table looks like as shown below.
By default, it creates table with three columns: however, we can add more according to our need. To add Data in the table you can just drag and drop the column from dataset to the table layout.
Here, in my example I have dragged and drop few columns such as Name, Date of Birth, NID, Email, Address, Telephone Nationality, Gender, Branch etc. You can add column in table according to your requirement. Power BI Report builder has facility to run and see report. You can run and see the report by click on Run option.
Additionally, we can format, change, and design the table. Here I have changed the background and font color of header which you can see in below image.
![What Is Power BI Report Builder And How To Design Paginated Report Using Power BI Report Builder]()
Finally, while running report looks like this.
Eventually, Employee Detail report is designed we can publish it and share to the users. Moreover, we can design reports with visualization for example, Pie chart, Bar chart, Gauge, Map, Data bar, indicator and many more are available in Power BI Report Builder.
Conclusion
In a nutshell, this article has described about power BI report Builder, its purpose, features, what is paginated reports as well as demonstrated how to create paginated report using Power BI Report Builder with an example of Employee Details report. Additionally, it has explained steps to connect and get data from SQL database to design the report. I hope, it gives you idea about Power BI Report Builder and how to use it to design Power BI paginated report.
References