Make your Power BI Desktop report print-ready (in one hour or less)
Description
Need a printable version of an existing Power BI report fast? Learn how to convert a .pbix into a paginated .rdl by using the same semantic model in Power BI Report Builder. This session walks you through each step of converting an existing Power BI report into its paginated equivalent, then reviews how to address common paginated report issues with parameters, print layout, and grouping.
Key Takeaways
-
- Use Power BI Report
- Whenever you can, use a shared Power BI semantic model
- Maintain and reuse it for multiple reports
- Formatting is not important
- You need to give your colleagues some
- Report must be “pixel perfect"
- Query Filters: constraint will always be applied to the report
My Notes
Action Items
- [ ]
Resources & Links
Slides
Make your Power BI Desktop
Report Print Ready
in one hour (or less!)
Lenore Flower | Data Consultant & Trainer (MCT)
Session Agenda:
- My printable report horror story
- Your basic printing options
- Power BI Report Builder to the rescue!
Connect to your semantic model
Understand the parameter relay race
Get the darn thing to print
Bonus! Dynamic titles
Why bother making paginated reports?
A personal anecdote*
*(learn from my pain)
Power BI Desktop is designed to be elastic
•Reports can be interacted with using
screens of varying sizes
•Users need varied options to explore
data with interactive slicers,
drilldowns, what-if analysis, etc.
•Not ideal for printing.
Your Basic Printing Options - Create paginated report in
Power BI Service - Jerry-rig something in Excel
- Manually print your Power
BI Desktop report off - Use Power BI Report
Builder
…page break to page break
Whenever you can, use a shared Power BI semantic model
Build a good semantic model once
Maintain and reuse it for multiple reports
Make a Paginated Report in the Power BI Service when
The output required is a simple table
No Parameters are required
No Graphs are required
Formatting is not important
When you can’t talk the business out of using reports as a
“data dump” this is a solid option
Build something using Analyze in Excel if
•
You’re mainly doing ad-hoc analysis
•
You’re very comfortable using MDX in Excel
•
You need to give your colleagues some
alternative to “export to Excel”
Introducing Power BI Report Builder
Desktop
Creates a .pbix file
Vs
Creates a .rdl file (Report Definition Language)
Would
Standard
Power BI Reports
Hi There! How can I help?
Vs.
Paginated
Power BI Reports
I’ve generated your output.
End of conversation
Paginated/Power BI Report Builder
Basically SSRS & Power BI Desktop, mushed together
Report building
tool
Power BI
Paginated Reports
Power BI “Standard“
reports
Power BI Report Builder/
Paginated Report Builder
Power BI Desktop
Origin story
SQL Server Reporting
Services (2005)
Power Pivot (2010)
Native habitat
SQL
Excel
Query logic
DAX, SQL, or MDX
DAX (for visuals)
Data stored?
No
Yes (unless thin Report)
If you try to build your paginated
report like you do in Power BI Desktop...
You're going to have a
bad time
Build in Power BI Report Builder when…
Report must be “pixel perfect"
(either to print or to PDF & email)
Users need an operational report that
requires limited interaction or
exploration
The report information is needed in
environments with iffy wifi
Getting your data into Report Builder
Paginated data sources act as a bridge
Semantic Model in
Power BI Service
Dataset in
Power BI Report Builder
Data Source in
Power BI Report Builder
I contain multitudes
I output a table
Paginated data sources act as a bridge
Creating A Dataset
Right click on
Datasets then select
“Add dataset…”
“Dataset Properties” will
open after you click “Add
Dataset…”
If you’re using a Power BI dataset as
your data source, your finished query
will be written here in DAX
Click here to build query
Demo: Why implicit measures work in Power BI (Standard)
Use measures;
save sanity
Needs sort
field? Add sort
field
Query Designer:
drag and drop to
build your
dataset
Minimize the number of columns
Understanding the Parameter
Relay Race
Query filters vs. query parameters
Both:
•defined using the Query Editor
•constrain the data pulled into the report
Query Filters: constraint will always be applied to the report
Query Parameters: constraint is chosen by report user at
report run time
Visualizing a Query filter
When the report is run…
Power BI’s
Data Model within the
Power BI Dataset
Adding A Query Filter
Unchecked = Filter
Table Name
Field Name
Filter Type
(usually) Filter Values
Note: filters listed above are for illustrative purposes only
Understanding the Parameter Relay Race:
Query Parameters
The Parameter Relay Race: Report Parameters
5.
4.
The Parameter Relay Race: User Selection at Run-time
- Report Parameter list
is generated from
hidden dataset query - Report user selects the
parameter(s) and runs the report
Parameters!Region.Value
=“South”
The Parameter Relay Race:
User selection is fed back to the query
(in the dataset query)
(from the report)
RSCustomDaxFilter(
@CustomerStateRegionsRegion, =Parameters!Region.Value
EqualToCondition,
[Customer].[StateRegions.Region], “South”
Parameters!Region.Value
=“South”
Data Type: Text
String
)
Formatting from the default
Adding a Header
Adding a Page Number to your Footer
[&PageNumber]
Setting up your
Matrix:
Group Order
Select the sort field for
this grouping.
Setting up your Matrix: repeating column & row headers
-OR-
Getting your report print ready
Define Report Properties
• paper size & margins
• Set “Consume Container White Space”
to “True” (if it isn’t already)
Right click in the gray area of your report to see & update report
properties
Set your Body Properties to match
Set Body Width = Report Width less margin
Width 11 inches – 2 * (1 inch margin) = 9 inches
Body Height should leave no white space
between end of Tablix & footer
Not sure what page size to use? Check your office
printers
Bonus (if time)
Making your report title Dynamic
Creating a Dynamic Report Title
Example = Join(Parameters!Region.Value,", ")
Output = “Midwest, Northeast, South”
=InStr
Example= InStr(Join(Parameters!Region.Value,", "),",")
Output = 8
=InStrRev
Example= InStrRev(Join(Parameters!Region.Value,", "),",")
Output = 7
You’re already using Field Expressions (congrats!)
(Blue row are all headers – hard coded text boxes)
=Fields!StallNo.Value
=Fields!OwnerPhone.Value
Handy Expressions: Text Manipulation
=Left(“Paginated”,3)
Output: Pag
=Right(“Paginated,3)
Output: ted
=Len(“Paginated”)
Output: 9
Concatenate with “&”
=[SpeakerName]&” Loves ”&[Topic]&”!”
=Lenore Loves Paginated Reports!
Sound off.
The mic is all yours.
Influence the product roadmap.
Join the Fabric User Panel
Join the SQL User Panel
Share your feedback directly with our
Fabric product group and researchers.
Influence our SQL roadmap and ensure
it meets your real-life needs
https://aka.ms/JoinFabricUserPanel
https://aka.ms/JoinSQLUserPanel