Chapter 9: Creating Reports
Finally! Yes, in our book about Microsoft's awesome reporting tool, Power BI, it's not until Chapter 9 that we're talking about creating reports!
There's a reason for that: to tell a story with data, you need to have data. Once you have the data, measures, hierarchies, security, and all the other things that go into modeling your data, the storytelling becomes very easy.
Power BI provides an easy-to-use canvas for you to tell your data story. It has a wealth of visualizations that can be added to reports from an easy-to-use application, Power BI Desktop.
Power BI has numerous visuals you can use to tell your story. From tables and matrixes to pie and donut charts, to scatter plots and histograms, odds are that Power BI has the perfect visual to tell your story.
In this chapter, we will cover the following:
- Understanding the reporting capabilities of Power BI
- Adding visualization items to reports
- Choosing an appropriate visualization type
- Formatting and configuring visualizations
- Importing a custom visual
- Configuring conditional formatting
- Configuring small multiples
- Applying slicing and filtering
- Adding an R or Python visual
- Adding a smart narrative visual
- Configuring the report page
- Designing and configuring for accessibility
- Configuring automatic page refresh
- Creating a paginated report
- Using Power BI datasets in Excel PivotTables
Technical requirements
The following are the prerequisites in order to complete the work in this chapter:
Understanding the capabilities of Power BI
Reporting and visualization capabilities are the core functionalities of Power BI. After sourcing data and modeling the data, you are now well positioned to take full advantage of it.
Imagine you want to show your boss gross revenue by state by product item group. You could create a table of data, much like what you would see in Excel.
Figure 9.1 – Data represented as a table
This is a decent way to show data, but it is not compelling. We could instead use the data storytelling capabilities in Power BI and create something like this:
Figure 9.2 – Data represented as a bar chart
Notice how you get a much better "feel" for the data when it is visualized like this. It's less confusing and drives your eyes and brain to understand how gross profit changes between states.
Or, even better, we could use the map visualization and have something such as this:
Figure 9.3 – Data represented as a map
This allows us to visualize our sales across the entire United States.
The great thing about all three choices is you get to choose. It's your story to tell. Sometimes a table of data is the correct choice, while sometimes you need a more visual way of displaying your data.
Adding visualization items to reports
Power BI installs with many visuals you can use to tell your data story. These visuals allow you to highlight the data you want in order to explain insights you have discovered in your data that lead to actions your business can take.
Reporting is a lens on your data, allowing non-technical people to understand your business's data. By visualizing your data, you make that data more accessible to a wider audience.
In Power BI, each visual you can use, from pie charts to funnel charts to some of the more esoteric visualizations, such as decomposition tree and key influencers, are all represented by their own icons in the Visualizations pane.
Figure 9.4 – Built-in visuals for building Power BI reports
The interface is often referred to as a clicky-clicky, draggy-droppy interface. The way you visualize your data is you select the visualization you want from the Visualizations pane. This will place the visualization on the reporting surface. Then, you add the columns you want to visualize.
If you want to visualize Gross Revenue by State Name and Product Item Group as a stacked bar chart, you can start by selecting the stacked bar chart visual from the Visualizations pane.
Figure 9.5 – Adding a stacked bar chart
This will place an empty stacked bar chart visualization on the reporting surface.
Figure 9.6 – Empty stacked bar chart added to report canvas
You then add the field you want to visualize to the values area. These options will change depending on the visualization you have chosen.
Figure 9.7 – Adding data fields to the stacked bar chart visual
Then, you will end up with the same visualization you saw in Figure 9.2. Power BI makes it easy and quick to visualize your data and tell your data story.
Now that we have learned how to add visualization items to reports, let's learn how to choose the appropriate visualization for any report.
Choosing an appropriate visualization type
Power BI offers many options for visualization using out-of-the-box capabilities that are available directly in the Visualizations pane. By selecting the fields you'd like to use in the visualization, you can drag and drop the fields to the various configurations and quickly see the outcome in the visualization displayed in the report canvas to find the visualization and configuration that best tells the data story you'd like to communicate.
Additionally, there are visuals available in Microsoft AppSource that provide further visualization beyond the capabilities built in. You can even create your own custom visuals if you cannot find the exact visualization that tells your story the way you want it to be told.
Figure 9.8 – Custom Power BI visuals in Microsoft AppSource
When selecting the visualization type, it's best to consider the data being presented. For example, geographic data may not be best displayed using a pie or line chart. Some data can be presented in multiple ways, such as geographic data being displayed in a map visual but also in a table. The best visual in a scenario such as this will depend on the specific story you're telling with the data.
Selecting the best visualization type may take some time to get optimized but it can make a big impact on the way your data is communicated.
Table and matrix visualizations
Tables and matrixes are some of the most elemental reporting visualizations. They provide the report consumer with a nice grid of data. As you will see later in this chapter, you can "decorate" your tables and matrixes with conditional formatting, making "boring" data grids quite informative.
The table visual is a rectangular grid that displays data in rows and columns. The table has two dimensions. It has headers and, by default, a row for totals. It looks like a table in an Excel spreadsheet.
Figure 9.9 – The table visual and its configuration
The matrix visualization looks similar to the table visualization; however, it allows you to display rows, columns, and values. It looks and acts much like a PivotTable from Excel.
Figure 9.10 – The matrix visual and configuration
Bar and column charts
Another set of fundamental visualizations is the bar and column charts. Power BI provides several different bar and column chart visualizations that allow you to present data in different ways using clustered and stacked formats. The stacked bar chart will group data categories into single bars, as shown in Figure 9.11:
Figure 9.11 – Stacked column chart visual
The clustered column chart visual will group categories of data in multiple bars rather than stacking. Figure 9.12 shows an example clustered column chart showing the same data as Figure 9.11, but you can see how the data is presented in both stacked and clustered formats:
Figure 9.12 – Clustered column chart visual
Stacked and clustered bar and column charts can be used in many cases, but they present the data differently, so it's good to experiment with both to best determine which is best to use in your use case.
Line and area charts
The line chart and area chart visuals are useful for displaying data over time. The area chart is like the line chart, with the addition of the area between the line and the x axis filled in. You will see similarities between the line and area charts but sometimes area becomes the best visual to use if you need to emphasize change over time.
Figure 9.13 – Line and area chart visuals
Pie chart, donut chart, and treemaps
The pie treemap, donut chart, and pie chart visuals allow you to depict the relationship between subsets of data and the whole. These visuals can be very colorful and are useful when portraying percentages of a whole, such as which regions of the country make up the majority of the total sales for a country.
The pie chart is like pie charts in any other plotting tool, made up of a filled-in circle, while the donut chart is a circular bar that plots the size of each value based on the size of the whole circle. It is slightly easier for most people to see the percentage differences in a donut chart compared to a pie chart.
Figure 9.14 – Pie and donut chart visuals
A common problem with these visualizations is trying to present too many categories at once. It is recommended to refer to your requirements to ensure that you're only adding categories that are needed and that you're not adding additional categories of data that will make the visuals overly complex. When presenting many categories of data, it's recommended to use multiple visuals and typically use another kind or style of visual.
Figure 9.15 – Pie and donut chart visuals show date data
While the pie and donut charts use circles to represent an entire dataset, the treemap visual instead uses a box or rectangle. Like the pie and donut charts, the treemap also uses colors to represent different categories of data and likewise, the treemap is useful when depicting a part of the whole. For example, if the whole box represents the total sales for the country, the colored boxes inside will be sized to represent each region and each region will use different colors to distinguish between them. The treemap visual will arrange the boxes inside from top left to lower right in largest to smallest order.
Figure 9.16 – Treemap visual
We can see in Figure 9.16 how a treemap visual is used to represent gross revenue by region and product item group.
Combination charts
There are multiple combination chart visuals that pair line and column charts into one visual. This allows you to see multiple values plotted over a common axis. For example, Figure 9.17 shows gross revenue in different product categories over time and shows us gross profit across the same time periods. This is useful because we can now not only see the impact of sales (revenue) for each product category but also link those sales to profit. Using a combination chart visual like this can sometimes allow you to save space by combining the data and using one visual to show what might normally need two visuals to show:
Figure 9.17 – Combo chart with bars and lines
Card visualization
Sometimes you just want to display a single value. For that, Power BI has the card visualization. This is great for displaying important data that you want to keep an eye on with your Power BI report, such as YTD sales, gross revenue for different product categories, or year-over-year manufacturing output. For multiple values, there is the multirow card visualization. When multiple values are used with the card visualization, the visual will show multiple rows for each data point.
Figure 9.18 – Card visual showing $82.11M in gross revenue
The following figure shows another card visualization that shows various revenues:
Figure 9.19 – Card visual showing gross revenue for multiple rows (Helicopter and Airplane)
Funnel visualization
The funnel visual is great for displaying steps in a linear process over time. Funnel charts are most often seen displaying workflows or processes such as an order being fulfilled or a sales opportunity moving through various stages from envisioning with a potential customer to onboarding the product to the customer.
Though that is what it is designed for, that does not mean you have to use it that way. If a funnel chart can add context to your data story, use it.
Figure 9.20 – Funnel visual showing gross revenue by year
Funnel charts can be useful in these contexts:
- Depicting staged or sequential movement of data through a process
- Illustrating the work to be done in various stages, such as when work items increase or decrease across multiple stages
- Showing challenges that might occur in a linear process
Gauge chart
Often, business reports need to tell the report consumer what is happening at a glance. The gauge chart is designed to do just that. Much like a dial you might find on the dashboard of your car, the gauge chart is a semi-circular arc showing a single value that progresses toward a target value.
Minimum, maximum, and target values are set using the visual configuration where data fields are applied to each on the Visualizations pane. You can use conditional formatting to change the color of the gauge depending on whether it is higher or lower than the Target value.
The data value compared to the minimum, maximum, and target is shown by color shading on the gauge. The visual is plotted such that the left-most side of the gauge will show the minimum value, while the right-most side of the gauge will show the maximum value. The target value and the current value will be shown somewhere between the minimum and maximum values.
Figure 9.21 – Gauge visual showing gross and net revenue
While gauge visuals only really show one value, they communicate things such as progress toward a goal. It's important to have a balance of different visuals so as not to overwhelm the report consumers and use the best visual for the requirements in your data story.
Waterfall chart
The waterfall chart visual is similar to a line chart; however, it combines bars to indicate increases or decreases in value. Typically, the x axis shows time, such as months in a fiscal year, while the y axis is used to show increases or decreases in value that happen during those months. For example, you can show changes to revenue each month using the waterfall visual, where the first month may have an increase and the second month has a decrease, both from a starting value and then ending at a value shown on the y axis. The columns are color-coded for increases and decreases, so this visual is useful to communicate these changes in a value over time.
Scatter chart
A scatter chart is a visual that plots many data points using an x and y axis for typically two categories or variables that you select. Scatter charts are the same as scatter plots, which are often used in statistics for visualizing data. Scatter charts can also be color-coded to show different categories of values that can be plotted across the same x and y axes, which will help show the relationship between the two categories of values.
Power BI can render up to a maximum of 10,000 data points in a scatter chart.
Scatter charts can also be used instead of a line chart when you want to change the scale of the horizontal axis.
Figure 9.22 – Scatter chart showing gross profit across date and regions
You can also have a date field as a play axis. This will allow you to "play" your data. The play will change over time and the data points will move as the data changes by date.
Map visuals
Power BI provides multiple ways of communicating geographical information. The first is the map visual, which allows you to plot data on a map in a very straightforward way, where numeric values or a percentage of the whole values can be overlayed on a map showing a geography. The map visual uses Microsoft Bing Maps as the core geography data.
There are also other map visuals, such as filled map, shape map, Azure map, and ArcGIS map visuals, which allow plotting data on a map in different ways. The shape map, for example, is useful when you only want the shape of a geographical region and not the whole map. For example, when plotting values by state in the United States, the map visual will show both Alaska and Hawaii on a map and then plot the values over the map. The shape map will start with just the outline of the contiguous states and then add Alaska and Hawaii to the corners of the visual, as shown in Figure 9.23.
The filled map visual combines the shape map's ability to fill in space in a mapped geographic region but overlays it on top of the Bing map of the regular map visual. The Azure map visual uses the Azure service Azure Maps and uses supplemental data from other parties, such as road, satellite, night, and terra layers, for displaying maps. ArcGIS Maps for Power BI uses the ArcGIS service developed and maintained by Esri.
Figure 9.23 – Map and shape map visuals showing population data for the United States
Q&A visualization
The Q&A visual provides a question-and-answer interface that uses natural language processing to generate visual representations of your data. By asking questions in natural language, this makes it easier for report consumers or even report designers to quickly generate insights from the data used in this report.
Power BI will attempt to choose the "best" visual to display your data. As with all visuals, you can choose to change the visual just by highlighting the visual and choosing another one from the build visual area.
Figure 9.24 – Q&A visual showing gross revenue by sale product category
The Q&A visual has these core characteristics:
- A question entry box where the natural-language query is entered.
- A list of suggested questions will be generated based on the data model.
- A button that takes the query result and generates another visual displaying the same information.
- A settings button that allows you to change the Q&A settings. For more information on this, please see Chapter 6, Using Data Model Advanced Features.
Now that we've reviewed the different kinds of visualizations, let's look at how we can configure those visualizations to meet the reporting needs of our organization.
Formatting and configuring visualizations
There are many ways you can customize the visuals in Power BI. There are options for fonts, colors, and sizes to name a few. There are near-constant improvements in visual customization, so be sure to take the time to explore the options that are available for each visual.
To get better acquainted, look at the clustered column chart visual and see how the formatting options are grouped; notice the default configurations. It's helpful to know the defaults for the visual and how each can be customized.
Each visual can be configured independently, so select a visual on the canvas and then click the Format button to view the Format pane and change the configuration of the selected visual.
Formatting options for a visualization
Each visual will have different options for formatting. The new Visualizations pane separates visual-specific formatting from formatting options that apply to most, if not all, visuals.
General formatting
In the General formatting option area, you will find the following:
- Properties
- Title
- Effects
- Header icons
- Tooltips
- Alt text
Each section has options under it that we will explore.
Figure 9.25 – General formatting for visuals
The Properties section allows you to specify the size and location, in pixels, of the visualization. These options allow you to place the visual where you want it to be on the canvas. It can also help when it comes to aligning visuals on the canvas. You can also lock the aspect ratio here as well. The advanced options are not covered as part of the exam, but they let you control responsiveness and layer order.
The Title section is where you make modifications to the title of the visual. The title describes the visual using text, which can help communicate the relevance and differentiate visuals on the report page. Titles can be formatted by selecting the text size, color, and font as well as styles such as bold and italic.
The Effects section allows you to select background options, such as color and transparency, as well as visual borders and shadows for the visual. In many cases, you will want to follow corporate style guidelines for things such as colors and shadows as well as fonts and text styles.
As with Title, the options in the Effects section can be defined by the value of a measure. You can easily create a measure that changes the border of a visual to red or green depending upon the measure's value.
Defining the header icons allows you to change the colors of the icons that appear when you hover over or click on a visual. You can even choose which icons to display, if any.
Figure 9.26 – Color formatting for a visual
The Tooltips section provides a way to customize the tooltip that appears when the mouse cursor hovers over a visual. Tooltips provide more context and detail to data points on a report visual. Power BI will automatically generate a tooltip based on the visual and fields selected, but in this area, you can customize this to fit your needs. You can even have a tooltip that displays other visuals.
Visual formatting
The Visual tab allows you to customize items that are specific to the visual selected. The categories of settings in this section will vary for each type of visual but most of the time, you can modify things such as the colors used. As previously mentioned, it's best to stick to consistent styling for colors and also to be inclusive of those who may have challenges distinguishing between some colors.
If there is a section called Data labels, then you can change fonts, size, and colors for the labels used for data points in the visual. Using solid colors is usually recommended but depending on the use of the visual or report, other options may be required. Keep in mind the background that will be behind the data label in order to ensure that they will stand out.
Every visualization will have options that apply only to that visualization. If you have selected a slicer visual, you can change the slicer orientation to horizontal. In a pie or donut visual, you can rotate the visualization, if needed. It wouldn't make any sense to change the orientation of a pie chart to horizontal, so you will not see that option.
When changes are made in the Visualizations or format pane you will instantly see the changes made to the visual on the canvas.
Importing a custom visual
The visuals included in Power BI out of the box will cover many use cases and be enough for most scenarios. If there are scenarios where the built-in visuals are not solving the requirements, then you should explore the custom visuals that have been made by many different developers found in AppSource, Microsoft's marketplace for Power BI visuals. If you can't find a visual you like, it is also possible to build your own custom visual for Power BI.
Microsoft AppSource contains visuals that have been created by Microsoft and Microsoft partners. Visuals can go through a certification process, so some visuals in AppSource have been certified and some have not. A certified visual has been tested and investigated by the Microsoft Power BI team to ensure it doesn't access external services or resources and that it follows secure coding patterns and guidelines. Certification is not a required process, but many custom visual developers will choose to go through the process to further validate their work.
To create your own custom visual, you should start with the custom visual Software Development Kit (SDK), which is open source and based on Node.js (a JavaScript programming language). The development kit is available on GitHub. For the exam, you need to know that it is possible to create a custom visual but actually creating one is not required.
To add custom visuals from AppSource, click Get more visuals in the ellipsis menu on the Visualizations pane. The Power BI visuals window will display, where you can search and filter to find the visual you'd like to import into your report. Once the visual is located, click Add to add it to your report.
Figure 9.27 – Power BI visuals in Microsoft AppSource
Newly added custom visuals appear below the built-in visuals on the Visualizations pane. Once added to the report, visuals are added to the report canvas and configured similarly to the built-in visuals. In addition to built-in and custom visuals, each visual has formatting options that can be configured, but another powerful feature is conditional formatting based on data values. In the next section, we will explore how conditional formatting can be configured.
Configuring conditional formatting
Power BI allows you to apply conditional formatting visuals. In table and matrix visualizations, you can customize cell colors, including gradients, that are based on field values. Conditional formatting can also be used to represent data values with bars, icons, or web links.
You may use this to draw your report consumer's attention to outlier data or to differentiate different parts of the business based on performance.
You can configure conditional formatting on a table or matrix by right-clicking on the column in the Values area and then selecting Conditional formatting and what you want to format.
Figure 9.28 – Changing the summarization setting for values in a visual
You can change the background color or font color and add data bars or KPI icons. You can even dynamically generate a web URL, sending your report consumers to other reports or web pages, depending on the values.
Figure 9.29 – Changing color configuration for a visual
You will be presented with either a rule-based option or a divergent series option.
As you can see, you can make the data in a table really stand out:
Figure 9.30 – Data bars added to a table visual
Now that we know how conditional formatting can be applied, in the next section, we will look at how we can configure small multiples.
Configuring small multiples
Sometimes you want to show multiple visualizations that all share a common axis, or you want to break a crowded line chart into smaller, more easily understood pieces.
The small multiples option will allow you to do just this on some visuals. If the option is available, just add the common axis you want to break your data into.
Figure 9.31 – Small multiples configured for gross revenue by year, product, and region
Small multiples are a great capability to help users think about data in smaller subsets and also make it easier for report designers to build multiple-visual reports without having to manually configure each visual. For example, breaking up by Region Name, as shown in Figure 9.31, allows you to see the same visual by each region in a simple step.
Applying slicing and filtering
The ability to focus your report to show exactly what you want to see is one of the biggest advantages of using a modern reporting platform. Filtering allows you to remove data so you can focus on the data that is needed for a particular scenario. Filtering can be applied directly to all pages in the report, only the current page, or only the selected visual. Additionally, you can use a slicer visual to build custom filtering directly on the report page.
Slicers and filters are similar; both will enable you to filter data in your report. Though both are similar, filters are generally faster and do not take up space on the report page. Slicers, though slower, can have conditional formatting and some users find them more intuitive, as they do appear on the report page. Slicers can have more advanced filtering options than the filters pane, which is designed for more basic operations.
Slicers can be customized to include lists, drop-down selectors, or button selectors, and they can be formatted to allow the single-selection or multi-selection of values.
Figure 9.32 – Slicer configured for a report
Slicers are great for the following cases:
Slicers can be configured in many ways, and this happens the same way it does with other visuals, on the Format your visual tab of the Visualizations pane. Options for slicers include the following:
- Single select – Off by default. This option allows the slicer to be configured to only allow one selection at a time.
- Multi-select with CTRL – On by default. This option allows the slicer to be configured to allow the selection of multiple values by holding down the Ctrl key while clicking.
- Show "Select all" option – Off by default. This configuration will provide another value that the user can select to automatically select or check all values in the slicer.
While slicers are useful, Power BI includes basic filtering capabilities using the filters pane as well. The filters pane can handle basic slicer operations. Using the filters pane allows you to maximize the use of the report canvas for visuals rather than using the canvas space for slicers.
The filters pane will enable the filtering of visuals that have been added to the report canvas and have configurations for filters that can be applied to the visual, the page, or all pages in the report.
Report designers can customize the filters pane by doing the following:
- Adding or removing fields users can use to filter
- Adjusting the fonts and colors of the filters pane
- Setting the default state of the filters pane (open or closed)
- Making the filters pane or specific filters visible or hidden
- Setting a lock on filters that you don't want users to change
Next, let's look at how we can use R or Python visuals to add additional visualization capabilities to Power BI reports.
Adding an R or Python visual
In addition to Power BI, the open source R and Python programming languages can also be powerful tools for transforming and visualizing data. Power BI has built-in capabilities to use R and Python environments to visualize data and use those visualizations in reports.
This is not the book to get into R or Python, but there are some things you should know for the test. If you would like to go deeper with data analysis with Python, then we suggest the Microsoft Learn module Explore and analyze data with Python, available here: https://docs.microsoft.com/learn/modules/explore-analyze-data-with-python/. For data analysis with R, we suggest the module R developer's guide to Azure, available here: https://docs.microsoft.com/en-us/azure/architecture/data-guide/technology-choices/r-developers-guide.
- You must have R or Python installed on the same computer as Power BI Desktop.
- If you have multiple versions of R or Python installed, Power BI Desktop will use the Path variable to identify the default version. You can change this in the Settings pane in Power BI Desktop.
- You can only use one version of R or Python for the report; different visualizations cannot use different versions of R or Python.
- Power BI Desktop can open your preferred integrated development environment for you to write your code in.
- You will need to install R or Python for R or Python visuals from the marketplace to work.
Adding a smart narrative visual
They say that a picture is worth a thousand words. That may be so, but sometimes you just might want some words to explain what is happening in your data. The smart narrative visualization will quickly summarize visuals and reports and put that summarization into text. It provides relevant innovative insights that you can customize.
Smart narrative summaries will build key takeaways and trends, and you can edit the language and format so the insights can be tailored to a specific audience. With smart narrative visuals, your report users will better understand the data, arrive at key points faster, and be able to explain the insights to others.
There are two different ways to generate a smart narrative summary. The first is to right-click on a visual in Power BI Desktop and select Summarize from the context menu. This will create a new smart narrative visualization.
Figure 9.33 – Smart narrative visual
If you have many visualizations on the page and you want a summary of all the data, you can select the smart narrative visualization from the Visualizations pane and it will generate a summary of all the visualizations on the page.
Figure 9.34 – Smart narrative visual summarizing all visuals on the page
You can edit the resulting text, adding text and configuring the formatting or any other text option you want. You can also add your own insights dynamically by adding your own values or calculations.
Figure 9.35 – Editing the smart narrative text
The smart narrative visualization will react to cross-filtering. For example, if you highlight Helicopter in the stacked column chart, the smart narrative will rerun, filtering for Helicopter.
Figure 9.36 – Smart narrative visual reacting to cross-filtering
We'll now check out how we can configure the report page so that we can customize it.
Configuring the report page
Much like visualizations, report pages themselves can be configured. As you are only configuring the page, you will not see a General or Visual area; instead, you will only see the Format page area.
Figure 9.37 – Configuration of report pages
You use the Page information area to change the page name, allow for use in Q&A by just double-clicking on the canvas, or set the page for use as a tooltip.
Figure 9.38 – Page information configuration
The canvas settings allow you to set the page size, in one of the preset configurations. The default is a 16:9 ratio, as most computer monitors are set to that. You can change the setting to 4:3, Letter, Tooltip, or Custom, which is a count of pixels.
The Tooltip option will shrink the page down to a size that is great to display a chart instead of just a grid of data in a tooltip. You don't have to turn Tooltip on in the Paging information settings, but it makes sense if you do.
Figure 9.39 – Canvas settings
You can also change the alignment of the report page in Power BI Desktop here. Your choices are Top or Middle, with Middle being the default.
Much like changing the background of a visual, you can change the background of the entire report page. The same ideas apply; follow the style guidelines of your organization and ensure colors, images, and shapes follow inclusive patterns so all users will be able to read and understand your reports.
As this is the entire page, you could choose a background image that "divides" the reporting area into sections.
Figure 9.40 – Dividing the canvas into areas
You can then layer your visuals on this grid and make your report really stand out. I created the preceding layout in PowerPoint and saved it as a JPG, then imported it as my background image.
This is also a great place to add a watermark, to identify whether the report is managed by a BI team or a data warehousing team. Watermarking reports can help your report consumers develop a level of trust that the report is accurate and follows your corporate guidelines. Just don't take up too much real estate with your watermark.
Designing and configuring for accessibility
It is important to think of all the different types of users and the methods with which users will interact with your Power BI reports. Some users may have visual or physical impairments and Power BI has the capabilities to make reports inclusive, as well as features to help ensure that building reports is also an inclusive activity.
Microsoft has recommendations for using Power BI with a screen reader that include turning scan mode or browse mode off.
To help in scenarios where screen reader software is used, context menus are available. You can move fields up and down in the list using the context menu. You can also move fields around between entries such as Value or Legend depending on the visual.
Figure 9.41 – Moving fields up and down
It's possible to build accessible reports in Power BI but it takes attention and effort by the report designer to make that a reality.
Power BI accessibility features can be either of the following:
- Built-in (no configuration required)
- Built-in (configuration required)
- Other tips and considerations
The built-in with no configuration required features include the following:
- Navigation using the keyboard
- Compatibility with screen reader software
- Viewing in high-contrast colors
- Showing data tables
These features require no special attention by the report designer but are available for all reports created in Power BI. These are basic features of Power BI and require no configuration by the report designer.
Accessibility features that do require configuration by the report designer include the following:
- Report themes
- Tab order
- Alt text
- Titles and labels
Report accessibility checklist
Power BI provides a checklist that report designers can use when creating reports. By using this checklist, you can help ensure your reports are accessible and usable by the largest audience. The checklist is available at the official Power BI documentation here: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-accessibility-creating-reports#report-accessibility-checklist.
Tip
For the exam, you should know that these features exist. Microsoft is making accessibility a key component of all its products going forward.
Configuring automatic page refresh
Reports are often only as valuable as the data they display. The frequency in which data is updated in the report can be critical to the value the report provides. When the most up-to-date data is required, it's best to consider using the automatic page refresh feature of Power BI.
When you enable automatic page refresh, you will be presented with an option of when to refresh the page. You can either select a fixed time interval or have Power BI detect changes in the underlying data source.
When using the fixed interval refresh type, all visuals in a report page will be updated at the same interval. You can refresh as often as every second or go as long as 5 minutes or more.
Another option besides fixed interval is to use change detection. Change detection will refresh visuals on a page when changes are detected in the data. This feature polls for changes to DirectQuery sources. When this is set up, you have defined how Power BI will detect changes by selecting the measure and you also have to select the frequency at which Power BI will check for changes. For published reports, this refresh type is only supported in workspaces that are part of a Premium capacity. LiveConnect data sources, such as Analysis Services and Power BI datasets, are not supported as they use LiveConnect. By using change detection, you may put less stress on the source system and on Power BI, as you only refresh the page if the underlying data changes.
When using either fixed interval or change detection, your Power BI tenant admin can set a minimum number of seconds or minutes that you can specify. If you publish your report with a lower value set, it will be reset to the tenant minimum.
Automatic page refresh is configured on the report page in the Format page pane, the same place where you can set the page background and name.
Figure 9.42 – Configuring change detection for page refresh
Change detection here is set to detect whether a new SalesOrderNumber field is added.
Creating a paginated report
Paginated reporting is another style of reporting than the interactive reports you can produce with Power BI. Paginated reports are reports that are meant to be consumed in a tightly controlled way. Often, this is referred to as "pixel perfect" because it allows reports to be rendered onscreen and printed on pages without losing the fidelity of the original design made by the report designer. Paginated reports are not meant to be interactive like typical Power BI reports.
Paginated reports can have headers and footers on every page and typically follow the design of reports that have been created for decades by enterprises and organizations using tools such as SQL Server Reporting Services.
When report requirements include user stories such as printing reports, or the data being displayed often includes dozens of columns or perhaps hundreds of rows of data, then it's typically a good idea to explore the use of Power BI paginated reports as the capability is well suited for those use cases.
Some organizations you are familiar with may already be generating paginated reports for you, either electronically or in the mail. Think of your bank statements, your credit card statements, or an itemized receipt. All of these are examples of paginated reports.
To create paginated reports in Power BI, you need to use an additional tool that is free to download called Power BI Report Builder. In order to publish paginated reports, you will need to publish to a workspace hosted in Power BI Premium.
Power BI Report Builder allows you to connect to data sources and create paginated reports. Although the number of data sources Report Builder can connect to is limited, it can connect to Power BI datasets, so the entire list of Power BI data sources and the refresh capabilities built into Power BI become immediately available.
Figure 9.43 – Creating a paginated report
Once you have created your report in Report Builder, you can publish it directly to the Power BI service from within Power BI Report Builder.
Figure 9.44 – Publishing a paginated report to the Power BI service
You will need to select a workspace that is backed by Premium capacity. Look for the diamond icon next to the workspace name.
Figure 9.45 – Diamond icon designating a Power BI Premium workspace
Once you publish your report, you can view it online.
Figure 9.46 – Published paginated report
Paginated reports are used throughout the business world.
This is not a book on Power BI paginated reports, so we won't go too deep into these extremely powerful report types.
For the test, you should know that Power BI paginated reports must be published to a workspace backed by Premium capacity and that these reports are designed for pixel-perfect rendering and interactivity.
Using Power BI datasets in Excel PivotTables
You can bring Power BI datasets into Excel with Analyze in Excel. You can then view and interact with your Power BI dataset using PivotTables, charts, slicers, and other Excel features.
To do this, you must go to the Power BI dataset in the Power BI workspace you want to connect to. Once there, click on the ellipsis and select Analyze in Excel.
Figure 9.47 – Analyze in Excel option for Power BI datasets
The Analyze in Excel feature should install automatically, if not already installed. Then, an Excel workbook will be downloaded. Once you open the workbook, you may need to select Enable Editing and Enable Content in Excel.
At this point, your Excel workbook is connected to the Power BI dataset and you can use the data as you need.
Figure 9.48 – Excel workbook connected to Power BI dataset
You get all the data cleaning, organization, and security of Power BI, and your data analysts get to use a tool they may be more comfortable with.
Summary
In this chapter, we checked out the reporting capabilities of Power BI. We explored various charts, custom visuals, and also learned how to format and configure visualizations to show what we need. We also checked out conditional formatting, and various advanced techniques in Power BI.
In the next chapter, we will explore Power BI dashboards, how dashboards are created from published Power BI reports, how they are different from Power BI reports, and how they can be used effectively.
Questions
- Power BI reports can use custom visuals added from what resource?
- Microsoft AppSource
- Microsoft Store
- Power BI VisualStore
- Office 365 templates
- Which visual makes the most sense to use for geographical information?
- Clustered bar chart
- Stacked bar chart
- Scatter chart
- Map
- If you want to make the color of data change based on its value, what feature will you use?
- The Edit tab
- Conditional formatting
- Bold formatting
- Conditional settings
button can be clicked to turn the visual shown into a standard visual in the report. This is one of the ways the Q&A visual makes it easier for report designers to build reports.
