The last month of the year already. December is all about tradition. We will do things we have done before again, and even seem to enjoy the nostalgia of it. We will watch Home Alone, listen to Mariah Carey, and have dinner on Christmas eve, which might change based on the location, but year-on-year won't change that much.
In that light, I thought it might be interesting to have a challenge on Paginated Reports, which is a distinct type of report in Power BI. It started in 2004 as SQL Server Reporting Services (SSRS). So, it has the history we all seem to like during this month, and it still delivers some useful functionality today. Actually, it was announced last month that this functionality will become part of the Power BI Pro license. All the reasons to learn more about this product this month.
Challenge Objectives
🎯 Understand when to use Paginated Reports
🎯 Use Power BI samples
🎯 Create a Paginated Report
🎯 Send the report as a PDF
🤓 Why Paginated Reports?
You might have created a Power BI reports once. The beauty of a Power BI report consumed through the online service is that you can make it interactive. You can filter the visuals on the page based on a selection. This allows different end-users to consume the same report and find the information they are interested in. Even though a Power BI report can be embedded to a SharePoint site, a Teams tab, and even as a live page to PowerPoint, there is still quite a big demand for PDF output.
You can export the Power BI report to a PDF, which will show 1 page with the visuals on it. However, this PDF does not facilitate the interactive capabilities of the report consumed through the online service. In most cases, this means that the report is not as useful as required. To give you an example, in the interactive report you can drill down into sections to get detailed insights. This is simply impossible with the generated PDF.
First Things First
Install Report Builder
As mentioned in the intro, Paginated Reports started as SQL Server Reporting Services (SSRS). The technology to create a paginated report differs from a Power BI report. Practically this means you will create this type of report in a separate application called Power BI Report Builder. You can download it here.
Premium per user workspace
Paginated reports are a Power BI premium feature. Earlier, this required a tenant level premium capacity, which had financially quite a high entry barrier. Later came the Power BI Premium Per User, license that resolved the entry barrier issue, but required all consumers of the Paginated report to have that license. Last month there was an announcement that Power BI Pro licenses can also consume the Paginated reports, which makes the financial consideration for paginated reports on par with Power BI reports.
The only difference is that the report must be within a Premium workspace. This license is only required for the person creating the report.
Log in with your developer account
Go to app.powerbi.com
Click on your profile image in the top right, and select Start trial
After refreshing the page, create a new workspace
Name the workspace Challenge 012 and make sure to select Premium per user as the License mode. Expand the Advanced section to find this setting.
Your new workspace should have the Premium per User icon next to it.
Procurement Analysis sample for Power BI
Although you can build a paginated report on practically any data set, for this challenge we will solely focus on the creation of a paginated report. To get a head start, we will install a sample that comes with a Power BI dataset, a Power BI report, and a Power BI dashboard.
Navigate to your new Premium per user workspace
Press the Add content button
Select Samples on the lower part of the screen
Select the Procurement Analysis Sample option
Press Connect and the sample will be deployed to your workspace
Now open the report to better understand what this report is about. The top-left visual shows an invoice trend line for each category. The total of those invoices is shown in the bottom-left visual. The combination of these two visuals gives really good insights on what and when money is spent.
Now, let's get to the part where the Power BI report won't suffice when we will generate a PDF of it. The categories listed in the bottom-left visual have a hierarchy. The top-left options in the visual indicate that. If you right-click on a subcategory (e.g. Hardware), you can select the drill down option. The visual will update and now shows the Commodities that are part of the subcategory Hardware. You can drill down even further to commodity details.
So, if we want all this detail in a report, we basically need to create a paginated report. This is exactly what we are going to do.
Build a Paginated Report
Report Builder Intro
Open Power BI Report Builder. Make sure that you have all the different panels set to visible. You can do this under the View tab of the ribbon. For some reason, on my machine the default report is a letter sizing and using the insanely impractical imperial system. Unless you are a Brexiteer or live in the US, Myanmar, or Liberia, you probably want to change this. you can reset this to your preference using the report property pages.
I set the units to centimeters, orientation to portrait, paper size to A4, all the margins to 2cm. It is recommended to use easy to calculate margins. It is recommended to use easy to calculate margins. The reason for this is that everything within the report body will be dynamically populated. This means that not only vertically, but also horizontally the report can be dynamic. In most cases, you don't want to span the total width of the report over more than one page. That's why you will always keep an eye on your report width. Our report page now has a width of 21cm, and both left and right a margin of 2cm. This results in a usable width of 17cm.
To see this usable width reflected in our report body, you can select the report body and manually set the width to 17cm in the properties panel. To give you a tip, the ReportTitle (the item that shows Click to add title) is embedded to the Body. The top of the properties panel will tell you what has been selected.
Now search for the Page footer. Just like a word document, this is a section that is repeated on every page. By default, there is a text box that will show the time when the report is generated. This is a built-in field. Other options can be found in the report data panel (left one). Because this came with the imperial template, I prefer to remove the text box and replace it later on with something else. Now, if you select the page footer, you can see that only the height is a sizing property. Remember that this section will be repeated on every page. You could opt for a smaller margin and use the page footer for that part, just like word does. But for now, just set the height to 1cm. This means that you will have sufficient spacing.
At the moment we don't have a page header yet. You can add it on the Insert tab of the ribbon. Set the Page header height also to 1cm. The body height should be adjusted too, but we will do this at a later stage. This way we have some space to work with. Your report will now look something like this.
Connect to data source
Now that we somewhat know the interface, it's time to get the data in so we can actually start building. On the Data tab of the ribbon, you can see there are lots of options listed. Because we are using Power BI sample data, this is the option we will select here. Feel free to use other options if you are interested in connecting to a source of your own. Make sure to log in with your developer account and select the Premium per user workspace we earlier created.
After you select Next, the Dataset Properties panel will be shown. At first, nothing will be visible. This is because we have to ask (query) the data source to give us the required information. Although we will not import a query, I do want you to know this is a possibility. I case you are creating a report on SQL data, you probably have a data specialist somewhere that you can ask to create the right and efficient queries. He can share those with you so you can just import it and focus on building the report. I think this is a great example of where Pro code and Low code can effectively collaborate.
During this challenge, we don't have such a person to our disposal, so we will have to do it ourselves. But luckily, there is a Query Designer. Once opened, you will see the Power BI dataset as a whole and there is a message that we can drag levels and measures to the query. To know which items to drag into the query, you can open the report in the Power BI online service that came with the sample. Click on edit and select the bottom left visual. the Fields pane will show you exactly which fields are used for this visual. You can see the Sub Category, Commodity, Commodity Detail, and Total Invoice are the items used for this visual.
But before we drag all these items to the query, let's check if the numbers we get from the query are similar to the numbers from the sample report. The top level of the hierarchy is the Sub Category and the Total Invoice measure is used for the Y axis. let's add those to the query and execute it.
The hardware number from the query editor is 140732213.0879 and the one in the report is 70736081.95. 🤔🤔🤔
Well, the whole Power BI report page is filtered on the year 2013. We can do the same thing in the Query Editor. Just drag Year from the Date table to the smaller top section and select the year 2013 in the Filter Expression column. Now run the query again. Notice that now the numbers are equal (taking the decimals into account). Another cool feature of the filter mechanism is that you can parameterize this filtering by just checking the check box. Press OK to see what happens.
As shown in the image above, a dataset will be added, and the Year parameter is now visible in the Parameters panel. To add all the field, just right-click the newly created dataset and update the query. I added the Category too, as that is the level used in the top left visual in the sample Power BI report.
Start visualizing
It's finally time to show something. Add a table to the body and set the Left and Top property to 0cm. Do you remember we discussed the sizing of body items? You should know that you can set the width of the table to 17cm.
Now, hover on the left data field. A small icon pops up. If you press it, you can map a field from your dataset to this field. Select the Commodity Detail. Notice that the column header is generated for you. Set the second column to Total Invoice. Let's see what the report will look like if we would like to print it now. You can do that by selecting Run on the ribbon's Home tab, or select the tiny run icon on the bottom right.
On my machine I did not see anything, until I selected Print Layout. See how you can easily adjust the report by selecting 2014 as the year parameter. It's powerful, but it doesn't look great yet, so let's go back to the Design mode.
Our aim was to get more granularity in our report. At this stage, it only shows the lowest level. In the bottom section, you see Row Groups and Column Groups. here we can make what we basically want. In the Row Groups section, you see the details listed. Select the dropdown, Add group, Parent group... . The level above the Commodity Detail is the Commodity, so make sure to select that one, and check the Add group header option. Notice a column is added, just like an empty row. Now do exactly the same for Sub-Category and Category. I want to point out that when the table is selected, the grouping will be visualized on the left.
Run the report again to see the current result. Notice that because of the additional columns, the width of the table has gotten bigger, and now the table horizontally spans two pages. This is done for you, so always double check the sizing before publishing. We will fix this in a minute. There is some grouping going on now, but it is far from ideal. Let's improve that too.
Remove the grouping columns. Notice that the body with stays wider than 17cm. You sould reset it to 17cm. Because we've created grouping headers, we can visualize the grouping there. You can do that with the hover function we used earlier. At the Total invoice column, we can just select the total invoice, and it will sum it for you. To make the report a bit easier to read, apply some formatting to the table. The thing I did:
You can set the Total invoice column to currency and align the text to the right
Vertically align all fields
Make all the grouping bold
Create a gray fill gradient for the grouping
set the headers one shade darker than the category. To keep the text legible, I made the text white.
I increased the left padding for every child level by 4pt to visually represent the hierarchy.
My report now looks like this. Notice that the third column is still on a separate page. This is because of the border width of the table. Something we will fix too. However, I think it's going in the right direction. There is one thing that I do want to add to improve the report, and that is to repeat the Commodity grouping header on each page, so you instantly understand to which commodity the detail relates if you are on a new page.
To fix the page width, set the first column width to 10cm and the second to 7cm. Remove the third column.
To get the headers correct, we need to enable Advanced Mode at the Column Groups dropdown (bottom section). Notice that the Row Groups now shows a little more. Select the (Static) bar that is nested in the (Commodity) bar. Now, you only have to set the RepeatOnNewPage property to True. Rerun the report and see that the headers are now on every page.
To finish the report, add a text box with Challenge 012 as a header. in the footer you can add a text box with the built-in field Page Number and Total Pages. You can separate these two with the text of or a /. Now you will see the pages. Do a final check on the page width, remove the space below your table, and publish it to the Power BI online Service. I named my report Challenge 012 report.
Send the Report
In our case, we already have a Power BI report that can be shared for interactive insights. This Paginated report is great for sharing the information as a PDF. This is what we will do. Luckily, there is a Power BI connector that does exactly this.
Go to make.powerautomate.com and create a new manually triggered flow
Add an action called Export To File for Paginated Reports
Select your Premium per user workspace and select the paginated report you just published
Set the Export Format to PDF
Add another action to the flow, Send an email (V2)
Direct the email to yourself. Select Show advanced options and set the Attachments name - 1 to Report.pdf and select the File Content from the Dynamic content for the Attachments Content - 1
When you trigger your flow, A PDF will be generated and sent to your mailbox.
Obviously, there are much more features in Power Bi Report Builder, but I think this is enough for now. You now know how to create basic reports designed for paper.
I hope you've enjoyed this traditional reporting approach. Merry Christmas and see you next year!
Additional Information
The Power BI team created a YouTube playlist for a Paginated Report in a Day course that could be of interest if you want to know more about it. You can add charts to the table and do lots more than we did in this challenge. For me it was a helpful resource to get this challenge out.
Key Takeaways
👉🏻 Paginated Reports are great for paper-based reports
👉🏻 From now on, only a Power BI pro license is required to consume paginated reports
👉🏻 A premium (per user) workspace is required