How to Connect SQL Server to Power BI Report Builder to Create Paginated Report

By - Ankit

Introduction:   

In this blog, we will learn how we can connect SQL Server to Report Builder, create a table in Report Builder to display our data, and to export that data. 

Power BI paginated reports are useful when we need our report in PDF/Excel format or want to print report data. 

Paginated report also provides us with the ability to produce highly formatted, print-ready layouts so, paginated reports are ideal for operational reports, like sales invoices. 

Connect SQL Server & create a table in Power BI Report Builder. 

1. Open Power BI Report Builder 

Launch Power BI Report Builder. 

2. Create a New Report 

   Click on "Blank Report" option to create a new report. 

3. Add a Data Source  
   

   • In the Report Data pane, right-click on "Data Sources" and select "Add Data       source." 
     

   

   • Choose a name for the data source. 

   • Select the connection type and choose "Microsoft SQL Server" as the connection type. 

4. Configure SQL Server Connection  

   • Now click on “Build” then we will see a dialog box in where we will add our SQL Server name.  

• Enter your SQL Server name and select the appropriate authentication method. 

• Choose the database you want to connect (We are using Adventure workbook for this blog). 

• D. Click on "Test Connection" to ensure the connection is successful. 

• E. Click "OK" to save the connection. 

F. We will see the server's name and database name in the Connection string  

G. Then Click on Test Connection and then Ok 

5. Add a Dataset  

Right-click on "Datasets" in the Report Data pane and select "Add Dataset." 

6. Name dataset  

   • Choose the data source we created earlier. 

•  Click "Query Designer" to retrieve the data or table you need. 

• Query Designer window will appear. 

• Expand “Tables” to see all of the tables. 

• Click on the check mark to select which data or column you want. We can expand the table to see the available columns from which a user can select the required columns.  The selected column will show in “Selected fields” 

• Click "OK" when we are done selecting our data. 

• Then we will see the SQL query; we can edit this query if needed. 

•  Click "OK" to create the dataset. 

 We have successfully connected SQL Server to Power BI Report Builder and created a dataset. With our data source and dataset configured, now we are ready to start building our report. 

Now we will learn how we can create a table in Report Builder for a Paginated Report.   

7. Create a Table 

   • Our Dataset is ready, now click on Insert Table from Insert Tab 

After clicking on Insert Table, drag your mouse in the white screen to create a table. 

• Then, insert columns in the table by drag and drop. (Step: 2) 

• To add a new column, right-click on the last column and then insert a column.  (Step: 2) 

Now, our paginated report is ready with a simple table. 

• Click on “Run” to see the report. 

Our paginated report is ready  

We can print this report and export. 

• We get multiple options to export paginated report  

Congratulations! We have successfully connected your SQL Server to Power BI Report Builder, and created a dataset and a report.