Here’s a detailed step-by-step guide on Creating a Paginated Report in Power BI Report Builder:
Introduction to Paginated Reports
Paginated Reports in Power BI are pixel-perfect, structured reports designed to be printed or shared as PDFs, Excel, or other formats. These reports allow for multi-page layouts, precise control over formatting, and are ideal for financial reports, invoices, operational summaries, or complex data tables. Unlike interactive Power BI reports, paginated reports display data in a fixed layout that adapts to multiple pages.
To create a Paginated Report, we use Power BI Report Builder, a standalone tool that allows you to create highly formatted reports with headers, footers, tables, charts, and more.
Step 1: Install Power BI Report Builder
Before creating a paginated report, you need to download and install Power BI Report Builder:
- Open your browser and go to the official Power BI website.
- Navigate to the Power BI Report Builder download page.
- Click Download and install the software by following the on-screen instructions.
- Once installed, launch Power BI Report Builder.
Step 2: Create a New Paginated Report
- Open Power BI Report Builder.
- Click File > New.
- Select Blank Report to start a new report.
Now, you will see an empty canvas with a “Report Data” pane on the left, where you can add data sources, datasets, parameters, and visual elements.
Step 3: Connect to a Data Source
A data source is where your report retrieves information from, such as a Power BI Dataset, SQL Server, SharePoint, or Excel.
- In the Report Data pane, right-click Data Sources and select Add Data Source.
- In the Data Source Properties window:
- Enter a name (e.g., “Sales Data”).
- Select a Connection Type:
- Power BI Dataset (for cloud data from Power BI Service).
- Microsoft SQL Server (for databases).
- Excel, SharePoint, or other sources.
- Click Build and enter your server/database credentials if connecting to SQL Server.
- Click Test Connection to ensure everything is working.
- Click OK to save your data source.
Step 4: Create a Dataset
A dataset is a structured query that fetches data from the data source.
- In the Report Data pane, right-click Datasets and select Add Dataset.
- Give your dataset a name (e.g., “SalesReportData”).
- Select Use a dataset embedded in my report.
- Choose your previously created Data Source.
- Click Query Designer to build your query:
- For SQL Server, write a SQL query (e.g.,
SELECT * FROM SalesTable WHERE Year=2023
). - For Power BI Dataset, use DAX queries.
- For SQL Server, write a SQL query (e.g.,
- Click OK to save.
Step 5: Insert a Table for Data Presentation
To display your dataset in a structured format, use a table.
- Click on the Insert tab in the ribbon.
- Select Table and drag it onto the canvas.
- In the Table Data pane, drag columns from your dataset into the table cells:
- Example: Product Name, Sales Amount, Quantity Sold.
- Resize columns and adjust formatting (bold, font size, color, etc.).
Step 6: Format the Report
Make your report visually appealing and readable:
Adding a Report Title
- Click Insert > Text Box and place it at the top.
- Enter the title (e.g., “Monthly Sales Report“).
- Change font, size, and alignment.
Adding Headers and Footers
- Click View > Header & Footer.
- Add elements like:
- Company logo
- Date and Time
- Page Number (
=Globals!PageNumber & " of " & Globals!TotalPages
).
Applying Conditional Formatting
To highlight high or low sales, use conditional formatting:
- Click on a column in the table.
- Right-click > Text Box Properties.
- Select Font > Conditional Formatting.
- Use an expression like:
=IIF(Fields!SalesAmount.Value > 10000, "Green", "Red")
- This changes the text color to green if sales exceed $10,000.
Step 7: Add Charts for Data Visualization
- Click Insert > Chart.
- Choose a Bar, Column, or Pie chart.
- Drag fields from your dataset into the chart.
- Example: Sales by Region.
- Adjust formatting (colors, labels, legends).
Step 8: Add Report Parameters for User Input
Parameters allow users to filter data dynamically.
- In the Report Data pane, right-click Parameters > Add Parameter.
- Enter a name (e.g.,
StartDate
). - Set Data Type (e.g., Date/Time).
- In your Dataset Query, modify it to use the parameter:
SELECT * FROM SalesTable WHERE SaleDate >= @StartDate
- When running the report, users can now select a date.
Step 9: Preview and Test Your Report
Before exporting or publishing, preview the report:
- Click Run (▶) on the toolbar.
- Verify:
- Data is loading correctly.
- Layout and formatting are correct.
- Pagination and page breaks work as expected.
Step 10: Export the Report
Paginated reports are often exported to PDF, Excel, Word, or PowerPoint.
- Click File > Export.
- Select a format:
- PDF for printing.
- Excel for data analysis.
- PowerPoint for presentations.
- Choose a location and save the file.
Step 11: Publish to Power BI Service
To share your paginated report, publish it to Power BI Service.
- Click File > Save As > Power BI Report Server.
- Enter your Power BI Service URL.
- Click Save & Upload.
- The report is now available in Power BI Service under Paginated Reports.
Conclusion
By following these steps, you have successfully created a Paginated Report in Power BI Report Builder. These reports are useful for financial statements, invoices, inventory reports, and operational summaries, providing highly structured, page-formatted data for printing and export.
Would you like additional customizations, automation, or dynamic filtering?