This page will guide you through the USF best practices of creating paginated reports that are user-friendly.
Pre-requisites
Understanding of creating a Power BI Paginated Report
Report Structure
Begin with a clear and concise report title.
Organize your report into logical sections using page breaks, headers, and footers.
Use consistent font styles, sizes, and colors throughout the report.
Maintain a consistent layout for elements such as tables, charts, and visuals.
Data Source and Queries
Use shared data sources whenever possible to ensure consistency across multiple reports.
Power BI Datasets are available as a source for paginated reports.
Optimize your queries to retrieve only the necessary data to minimize loading time.
Consider using query parameters to make your report more interactive and flexible.
(See Filters and Slicers section for additional information.)
Visualizations and Charts
This page will guide you through the steps to make the matrix more user-friendly and reduce the number of clicks to expand the whole matrix.
Visibility options
Here, I have selected the Visibility options according to my requirement.
In Hidden, choose one of the following options to set the visibility of this report item the first time you run a report:
Select False to display the report item.
Select True to hide the report item.
Select <Expression> to open the Expression dialog box to create an expression that is evaluated at run time to determine the visibility.
InitialToggleState gives you the ability to keep the toggle state on or off when you run the report.
In ToggleItem, from the drop-down box, select the name of a text box to which to add the toggle image.
Group Properties
In the Row Groups section, select the dropdown of desired column and click Group Properties
In General group the columns according to requirement
Click on Visibility and select Show option for When the report is initially run.
Check Display can be toggled by this report item and select the appropriate column and click OK.
Click Run and see the results
Filters and Slicers
The purpose of embedding Report Builder reports into Power BI is to merge the two tools using the capabilities of both to their fullest. Power BI makes it easy to create slicers and filters but does not do a good job for paginated reports. Report Builder is good for paginated reports, but to setting up slicers and filters is laborious and tedious. By using both you can leverage those strengths together.
Pre-requisites
These steps assume you already have Report Builder and Power BI desktop downloaded and installed on your computer. Both are available for download from the http://app.powerbi.com website on the top right side.
Understanding of creating a Power BI Paginated Reports
Here are two options for learning this:
Using Report Builder
Open Report Builder and create a new blank report.
Right click on Data Sources and choose Add Power BI Dataset Connection. In the picture below I chose Self-Service Dataset and the Fast Financial Analytics Dataset
Right click on Datasets and create a new dataset, give it an appropriate name and connect to the Data Source you created. Then click on Query Designer.
Add the fields that you want to see on your report. You need to include the fields that you want to pass filters from Power BI. In the case below I added FiscalYear, OperatingUnitID, DepartmentID, FundCodeID, ProductID, InitiativeID – that I will want to filter on. I also added in the two measures ActualAmount and BudgetAmount. Once you have those fields you can verify that it will work by clicking on Click to execute the query.
At the top of the query designer add each dimension and the field that you want to pass as a filter on the set. Choose equal as the operator. Filter the expression to all. Check the Parameters checkbox. This sets up the parameters, as well as the filters coming into this dataset.
Create the report with the data in it. This tutorial uses the Insert – Table – Table Wizard option. On the first screen that pops up choose the Dataset that was created and click ok. Place the fields in the Row groups section and the measures in the values section. Then click next.
Check Show subtotals and grand totals. Uncheck expand/collapse groups. Click next. Click Finish.
Resize Columns so that they are large enough to show the values. Change the amount columns to be currency. Click on the column to change, and select currency from the drop down for the entire column.
Your Report Data toolbar on the left should look like this.
Publish the report. Choose the correct workspace and name the report.
Open Power BI Desktop and create the slicers that you want to pass to the report. Connect to the same dataset that you used for the Report Builder report.
Add each of the slicers. Search for the OperatingUnitID field and add it to the report. Switch the visualization to slicer.
Switch the slicer to dropdown.
To force the slicers to affect each other slicer add a filter on the slicer and set the ActualAmount to show when the value is not blank.
Enable search on the slicer. Click on the … on the bottom right of the slicer and select search.
Copy and paste the slicer visual and replace the field with the next slicer field.
Follow the same steps to create all the other slicers.
Add the paginated report visualization. Place all the parameters into the parameters section. Make the blank visualization as large as possible and click on the green “Connect to report” button.
Find your published report in the correct workspace. Click on the Set Parameters button.
Map all the Report Builder Parameter to the correct Power Bi Field and click see report
Set your slicers and view your report
Important Considerations
Each time the slicer in Power BI changes an Apply Changes button will appear in the paginated report section. It is not suggested to set Auto-apply filters, because the paginated report section will need to reload which takes time. It is preferred to change all your filters then click apply changes on the paginated report section at the top left.
Trying to send too many parameters – you will get an error if you try to send too much information saying that the URL is too long. In that case if you click open report at the top of the paginated report section, you can reselect the parameters with the Report Builder interface and rerun the report directly within Report Builder
Report Performance
Minimize the number of visuals and data points on a single page to improve performance.
Use summarized/aggregated data to reduce the amount of data to process.
Optimize DAX calculations and expressions for better query and rendering performance. Watch this Guy in a Cube video.
Test your report's performance with different data volumes and optimize accordingly.
Documentation and Version Control:
Document your report's purpose, data sources, and any calculations or transformations applied.
Maintain a version control system to track changes and collaborate effectively.
Testing and Validation:
Test your report on different devices and screen resolutions to ensure responsiveness.
Validate your report's calculations and visualizations against known and reliable data sources.
Solicit feedback from users or stakeholders to identify areas for improvement.
Test to verify that it prints as expected.
Perform regular maintenance and updates to keep the report functioning properly.
Remember, these are general guidelines, and you can adapt them based on your specific requirements. Regularly reviewing and refining your report-building practices will help you create consistent, and user-friendly reports using Power BI Report Builder.