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

• 4. 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:

1. My printable report horror story
2. Your basic printing options
3. 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
4. Create paginated report in
   Power BI Service
5. Jerry-rig something in Excel
6. Manually print your Power
   BI Desktop report off
7. 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

1. Report Parameter list
   is generated from
   hidden dataset query
2. 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