Create 

paginated reports  
Paginated reports allow report developers to create Power BI artifacts that have
tightly controlled rendering requirements. Paginated reports are ideal for creating
sales invoices, receipts, purchase orders, and tabular data. This module will teach you
how to create reports, add parameters, and work with tables and charts in paginated
reports.

Learning objectives
In this module, you will:
 Get data.
 Create a paginated report.
 Work with charts and tables on the report.
 Publish the report.
StartSave

Prerequisites
None
This module is part of these learning paths
 Visualize data in Power BI
 Introduction to paginated reports2 min
 Get data3 min
 Create a paginated report3 min
 Work with charts on the report1 min
 Publish the report2 min
 Lab - Create a paginated report1 hr 30 min
 Check your knowledge3 min
 Summary1 min

Introduction to paginated reports

Completed100 XP

 2 minutes

Paginated reports allow report developers to create Power BI artifacts that have
tightly controlled rendering requirements. Paginated reports are ideal for creating
sales invoices, receipts, purchase orders, and tabular data. This module will teach you
how to create reports, add parameters, and work with tables and charts in paginated
reports.
Paginated reports defined

Paginated reports give a pixel-perfect view of the data. Pixel perfect means that you
have total control of how the report renders. If you want a footer on every sales
receipt that you create, a paginated report is the appropriate solution. If you want a
certain customer's name to always appear in green font on a report, you can do that
in a paginated report.

Power BI paginated reports are descendants of SQL Server Reporting Services (SSRS),
which was first introduced in 2004. Power BI paginated reports and SSRS have a lot in
common. If you're looking for information on paginated reports and can't find it,
searching the internet and Microsoft documentation on SSRS is an excellent idea
because you'll find numerous blog posts, videos, and documentation available to
you.
When paginated reports are the right fit

You can use paginated reports for operational reports with tables of details and
optional headers and footers.

Additionally, you can use paginated reports when you expect to print the report on
paper or when you want an e-receipt, a purchase order, or an invoice. Paginated
reports also render tabular data exceedingly well. You can have customized sort
orders, clickable-headers, and URLs in results, which allows for simple integration
with custom applications.
Power BI paginated reports can also display all of your data in a single report
element, such as a table. If you have 25,000 records, and you want the reports to
print over 100 pages, you can do that. If you want every third record to be printed
with a light pink background, you can do that as well.

Power BI paginated reports are not created in Power BI Desktop; they are built by
using Power BI Report Builder. Power BI paginated reports are a feature of Power BI
Premium.
In this module, you will:

 Get data.

 Create a paginated report. 

 Work with charts and tables on the report. 

 Publish the report. 

Next unit: Get data

Get data
Completed100 XP

 3 minutes

The first step in creating a report is to get data from a data source. Though this
process might seem similar to getting data in Power BI, it is different. Power BI
paginated reports do not use Power Query when connecting to data sources.

Getting data in a Power BI paginated report does not involve data cleaning steps. In
fact, data is not stored in a Power BI paginated report dataset. When data is
refreshed on the report, it is retrieved in an unaltered form from the data source,
according to the query that was used to retrieve it.

Data can be collected from multiple data sources, including Microsoft Excel, Oracle,
SQL Server, and many more. However, after the data has been collected, the different
data sources cannot be merged into a single data model. Each source must be used
for a different purpose. For instance, data from an Excel source can be used for a
chart, while data from SQL Server can be used for a table on a single report.
Paginated reports have an expression language that can be used to look up data in
different datasets, but it is nothing like Power Query.

Power BI paginated reports can use a dataset from Power BI service. These datasets
have used Power Query to clean and alter the data. The difference is that this work
was done in Power BI Desktop or SQL Server Data Tools prior to using Power BI
Report Builder, which doesn't have that tool in the user interface.

Create and configure a data source

To retrieve data, open Power BI Report Builder. From the Getting Started screen,

select New Report. You can choose whether to create a report with a table on it, a
chart, or a blank report. For the purposes of this example, a blank report has been
selected. These choices create a default visual on your a new report, which can be
changed at any time. Next, go to the Report Data window, which is typically on the
left side of the tool, though it can be moved around.

Right-click the Data Sources folder and select Add Data Source.

On the General tab, name the data source.

After naming the data source, choose the correct connection string by selecting
the Build button.
After you have selected Build, the Connection Properties screen appears. The
properties on this screen will be unique for each data source. The following figure is
an example of what you might see in the screen. The figure shows the properties of a
SQL Server connection that you, the report author, will enter:

1. Server name

2. Database name

3. A button for testing the connection

4. Select OK to continue
You can also enter username and password information on the Connection
Properties screen, or you can leave it on the default setting and use your Windows
credentials. Select OK again.

You've now created a data source.

Generally, authentication is beyond the scope of this course. Typically, you will
receive the connection information from your IT department, application specialist,
or the software vendor.

Create and configure a dataset

A data source is the connection information to a particular resource, like SQL Server.
A dataset is the saved information of the query against the data source, not the data.
The data always resides in its original location.
Right-click Datasets in the Report View window and select Add Dataset. Ensure
that the correct data source is selected. This action will run the query against the
correct data source.

From the window that displays, you can:

1. Name the query.

2. Choose whether to use a text command or a stored procedure.

3. Enter a query into the text box.

Next unit: Create a paginated report

Create a paginated report

Completed100 XP

 3 minutes

To create a report, you must add a visual to the design surface, similar to what you would do
in Power BI Desktop. Select the Insert tab from the ribbon at the top to see your options for
adding a visual.

For the purposes of this example, a table visual has been added to the design surface.

When you select the Table drop-down menu, you can choose from two options: Insert
Table or Table Wizard. Select Insert Table.

You can now draw a table on the design surface. From the Report data window, drag fields
from the dataset to the table on the design surface.
When you have finished, notice that the field is added to the lower portion of the table in the
square brackets. The header will also be added. You can rename or apply formatting to the
headers, such as bolding or underlining the text.

To test this simple report, select the Run button from the Home tab in the ribbon.

The report will run and display actual data in the table.
Notice that some items have changed in the report: a title was entered at the top, table headers
were renamed and are in bold font, and a background color was selected on the header. These
changes were implemented to help make using the report easier for the report reader.

If you want to return to the design surface, select the Design button.

Another aspect of creating a report is to add a parameter. Parameters can be used for different
reasons, for example, when you want the user to enter information that displays on a visual
on the report. The most popular reason to add a parameter is to affect which data is retrieved
from the data source.

Consider the scenario where you are creating a report that retrieves data from a sales
database. You only want sales data from between a begin date and an end date. In this case,
you would create two parameters and then modify the dataset query to include those
parameters in the WHERE clause of the query. Your first step in this situation is to add a
parameter.

Add parameters

To add a parameter, right-click Parameters and select Add Parameter.

On the General tab, name the parameter, select the data type, and then choose the prompt
that the user will see.
On the Available Values tab, enter options that the user can choose from. The Default
Values tab has the initial value of the parameter when the report loads, but it can be changed
by the user.

You can also get parameter values from a query. For more information, see the Microsoft
documentation on parameters.

After you have created a parameter, you can use it to interact with the report. If you return to
the dataset, you can connect that parameter with the query.
The parameter reference starts with the at (@) symbol. Add the parameter name to the query
text. Now, when the report refreshes, the data will be pulled from the data source according
to the WHERE clause and the parameter value.

Next unit: Work with charts on the report

Continue

Work with charts on the report

Completed100 XP

 1 minute
Two ways to add a chart to your report are: Select the Chart button, select Insert
Chart, and then draw your table on the canvas.

Right-click the report canvas, select Insert, and then select Chart.

Next, choose the type and style of your chart.

After you have selected a chart type, the chart will be added to the design surface.

When you select the chart, a new window appears to the right. The Chart
Data screen allows you to format the chart according to the values and axis
properties.

Select the plus (+) sign beside each section to select the required columns.
For more information on working with charts, you can search Microsoft
documentation regarding SSRS reports. All of the material in the SSRS
documentation will apply to Power BI paginated reports.

Next unit: Publish the report

Publish the report

Completed100 XP

 2 minutes
To publish your report, select File > Save as and then select Power BI Service. Your
report will now appear in Power BI service. For more information, go to Publish
datasets and reports from Power BI Desktop.

Best practices

Creating a report is meant to inform and drive action on the part of the report user. It
isn't enough to create a report with sales information on it; the report author should
always ask themselves several questions:

 What purpose is this report for?

 Who is using the report?

 How can I help people do a better job?

 What is the most important information and how can I highlight it?

 Is this report readable?

 Can people change the elements that they need to if their questions
change?

 Do I have visuals that are distracting from the core message of the
report?
  Is this report staying focused in a single topic or only a few topics?

 Am I providing all information that the user expects to see in the report?

Creating good headers and footers is an excellent way to help the user interpret the
report. You can provide guidance to the user by documenting why this report was
created. Adding a report implementation date and time is an excellent practice.
Occasionally, reports are run and then saved. People who are looking at a report will
not know that they are looking at an older version unless that fact is highlighted in a
footer.

Target the report for your appropriate region. English speakers read top-down, left-
to-right. Putting important information, like totals, at the top of the report will
highlight that information for English speakers. Europeans read dates differently than
users from the US. Localize data formats to the appropriate target user.

In addition to focusing on the visual aspects of the report, a good report author will
consider report delivery and data source usage. Good delivery focuses on how the
user wants to see the report. Therefore, report authors should ask themselves the
following questions to test the appropriate delivery format and ensure that the
report is rendering correctly in that format:

 Does the user want the report sent to them in an email message?

 Does the user want the report in a printable format?

 Does the user read the report in a web browser?

Pay attention to the height and width of the report page. Verify that the report is not
running off the page when the report renders for the user.

A good report author creates reports that are easy on the data source. If you
continue to recall data that you don't need from a data source, you will overburden
the data source and affect performance in unpredictable ways. Focusing on only
getting pertinent data will help you be a responsible teammate to others who are
using the same data.

Next unit: Lab - Create a paginated report

Lab - Create a paginated report

Completed100 XP

 90 minutes
This unit includes a lab to complete.
Use the free resources provided in the lab to complete the exercises in this unit. You
will not be charged.

Microsoft provides this lab experience and related content for educational purposes.
All presented information is owned by Microsoft and intended solely for learning
about the covered products and services in this Microsoft Learn module.

Launch lab

Access your environment

Before you start this lab (unless you are continuing from a previous lab),
select Launch lab above.

You are automatically logged in to your lab environment as data-ai\student.

You can now begin your work on this lab.

 Tip

To dock the lab environment so that it fills the window, select the PC icon at the top
and then select Fit Window to Machine.

In this lab, you will use Power BI Report Builder to develop a pixel-perfect paginated

report layout that sources data from the AdventureWorksDW2020 SQL Server
database. You will create a data source and dataset, and also configure a report
parameter. The report layout will allow data to be rendered over multiple pages, and
to be exported in PDF and other formats.
The final report will look like the following:

In this lab, you learn how to:

 Use Power BI Report Builder

 Design a multi-page report layout

 Define a data source

 Define a dataset

 Create a report parameter

 Export a report to PDF

Getting Started

In this exercise, you will open Power BI Report Builder to create and then save a
report.

Create the report

In this task, you will open Power BI Report Builder to create and then save a report.
1. To open Power BI Report Builder, click on the Power BI Report Builder
shortcut or type “Power BI Report Builder” in the Search box next
to Start button and press Enter.

2. In the Power BI Report Builder window, to create a new report, in

the Getting Started window, click Blank Report.

3. To save the report, click the File tab (located at the top-left), and then
select Save.
 4. In the Save As Report window, navigate to the D:\DA100\
MySolution folder.

5. In the Name box, enter Sales Order Report.

6. Click Save.

Developing the Report Layout

In this exercise, you will develop the report layout, and explore the final report
design.

Configure the report header

In this task, you will configure the report header.

1. In the report designer, notice the default report layout, which consists of
a body region and a report footer region.
 The body contains a single textbox ready for a report title, and the report
footer contains a single textbox describing the report execution time.

The default design will render the report title once, in the body, on the
first rendered page. However, you will now modify the report design by
adding a report header region, and by moving the report title textbox
into this region. This way, the report title will repeat on every page. You
will also add an image of the company logo.

2. To add a report header region, on the Insert ribbon tab, from inside

the Header & Footer group, click Header, and then select Add Header.

3. In the report designer, notice that a report header region has been
added to the report layout.

4. To select the body textbox, click the "Click to add title" textbox.

5. To move the textbox, click the four-headed arrow icon, and then drag it
into the header region to then drop it at the very top-left of the report
header region.
6. To modify the report title textbox text, click inside the text box, and then
enter: Sales Order Report

To resize the textbox, you will first open the Properties pane. For fine-
grained control of location and size properties, you will need use
the Properties pane.

7. On the View ribbon tab, from inside the Show/Hide group,

check Properties.

8. To select the report title textbox, first click an area outside the textbox,
and then click the textbox again.

The textbox is selected when you see the border of the textbox
highlighted and resizing handles (small circles) appear on the border.

9. In the Properties pane (located at the right), scroll down the list to

locate the Position group.
 The Position group allows setting exact values for the location and size
of report items.

It's important that you enter the values as directed in this lab. Pixel-
perfect layout is required to achieve the page rendering at the end of the
lab.

10. Within the Position group, expand the Location group, and ensure that

the Left and Top properties are each set to 0in.

The location and size units are in inches because the regional settings of
the lab virtual machine is set to the United States. If your region uses
metric measurements, centimeters would be the default unit.

11. Within the Position group, expand the Size group, and then set

the Width property to 4.

12. To insert an image, on the Insert ribbon tab, from inside

the Report Items group, click Image.

13. To add the image to the report design, click inside the report header
region, to the right of the report title textbox.

14. In the Image Properties window, to import from an image file,

click Import.
15. In the Open window, navigate to the D:\DA100\Data folder, and then
select the AdventureWorksLogo.jpg file.

16. Click Open.

17. In the Image Properties window, click OK.

18. In the report designer, notice that the image was added, and is selected.

19. To position and resize the image, in the Properties pane, configure the

following properties:

Property

Position | Location | Left

Position | Location | Top

Position | Size | Width

Position | Size | Height

20. To resize the report header region, first select the region by clicking a
blank area of the region.

21. In the Properties pane, set the General | Height property to 1.

22. Verify that the report header region contains a single textbox and image,
and looks like the following:
 23. To save the report, on the File tab, click Save.

 Tip

You can also click the disk icon located at the top-left.

You are now ready to configure the report to retrieve a database query result.

Retrieve data

In this task, you will create a data source and dataset to retrieve a query result from
the AdventureWorksDW2020 SQL Server database.

1. In the Report Data pane (located at the left), right-click

the Data Sources folder, and then select Add Data Source.

It is possible to retrieve data from cloud or on-premises databases, or a

Power BI dataset.

2. In the Data Source Properties window, in the Name box, replace the

text with AdventureWorksDW2020.

3. In the Select Connection Type dropdown list, notice

that Microsoft SQL Server is selected.
4. To build the connection string, click Build.

5. In the Connection Properties window, in the Server Name box,
enter localhost.

In the labs, you will connect to the SQL Server database by

using localhost. This isn't a recommended practice, however, when
creating your own solutions. It's because gateway data sources cannot
resolve localhost.

6. In the Select or Enter a Database Name dropdown list, select

the AdventureWorksDW2020.

7. Click OK.

8. In the Data Source Properties window, click OK.

9. In the Report Data pane, notice the addition of

the AdventureWorksDW2020 data source.
10. To create a dataset, in the Report Data pane, right-click
the AdventureWorksDW2020 data source, and then
select Add Dataset.

A report dataset is a different in purpose and structure from a Power BI

dataset.

11. In the Dataset Properties window, in the Name box, replace the text

with SalesOrder.

12. To import a pre-defined query, click Import.

13. In the Import Query window, navigate to the D:\DA100\Lab13A\
Assets folder, and then select the SalesOrder.sql file.

14. Click Open.

15. In the Query box, review the query, and be sure to scroll down to the
bottom of the query text.

It is not important that you understand the details of the query

statement. It has been designed to retrieve sales order line details. The
WHERE clause includes a predicate to restrict the query result to a single
sales order. The ORDER BY clause ensures the rows are returned by line
number order.

16. Notice the use of @SalesOrderNumber in the WHERE clause, which

represents a query parameter.
 A query parameter is a placeholder for a value that will be passed in at
query execution time. You will configure a report parameter to prompt
the report user for a single sales order number which will then be passed
to the query parameter.

17. Click OK.

18. In the Report Data pane, notice the addition of the SalesOrder dataset

and its fields.

Fields are used to configure data regions in the report layout. They were
derived from the dataset query columns.

19. Save the report.

Configure the report parameter

In this task, you will configure the report parameter with a default value.
1. In the Report Data pane, expand the Parameters folder to reveal
the SalesOrderNumber report parameter.

The SalesOrderNumber report parameter was added automatically

when the dataset was created. This is because the dataset query included
the @SalesOrderNumber query parameter.

2. To edit the report parameter, right-click the SalesOrderNumber report

parameter, and then select Parameter Properties.

3. In the Report Parameter Properties window, at the left, select

the Default Values pages.

4. Select the Specify Values option.
 5. To add a default value, click Add.

6. In the Value dropdown list, replace the text with 43659.

Sales order 43659 is the value you will initially use to test the report
design.

7. Click OK.

8. Save the report.

You will now complete the report header region design by adding textboxes to
describe the sales order.

Finalize the report header layout

In this task, you will finalize the report header region design by adding textboxes.

1. To add a textbox to the report header region, on the Insert ribbon tab,

from inside the Report Items group, click Text Box.
2. Click inside the report header region, directly beneath the report title
textbox.

3. Inside the textbox, enter Sales Order: followed by a space.

4. To insert a place holder, immediately after the space just entered, right-
click and then select Create Placeholder.

5. In the Placeholder Properties window, at the right of

the Value dropdown list, click the fx button.

The fx button allows entering a custom expression. This expression will

be used to return the sales order number.

6. In the Expression window, in the Category list, select Parameters.

7. In the Values list, double-click the SalesOrderNumber parameter.

8. In the expression box, notice that a programmatic reference to

the SalesOrderNumber report parameter was added.

9. Click OK.

10. In the Placeholder Properties window, click OK.

11. Click a blank area of the report header region, and then select the new
textbox.

12. In the Properties pane, configure the following position properties:

Property

Position | Location | Left

Position | Location | Top

Position | Size | Width

Position | Size | Height

13. To format part of the textbox text, inside the new textbox, select only
the Sales Order: text.
14. On the Home ribbon tab, from inside the Font group, click
the Bold command.

15. Add another textbox to the report header region, and then enter the
text Reseller: followed by a space.

 Tip

You can also add a textbox by right-clicking the canvas, and then
selected Insert | Text Box.

16. After the space, insert a placeholder, and then set the value of the
placeholder to use an expression.

17. In the Expression window, in the Category list, select Datasets.

18. Base the expression value on First(Reseller) value.

19. In the Properties pane, configure the following position properties:

Property

Position | Location | Left

Position | Location | Top

Position | Size | Width

Position | Size | Height

20. Format the Reseller: text in bold.

21. Add a third (and last) textbox to the report header region, and then enter
the text Order Date: followed by a space.

22. After the space, insert a placeholder, and set the value of the placeholder
to use an expression based on
the Datasets category, First(OrderDate) value.

23. To format the date value, in the Placeholder Properties window, select

the Number page.

24. In the Category list, select Date.

25. In the Type list, select a suitable date format type.

26. In the Placeholder Properties window , click OK.

27. In the Properties pane, configure the following position properties:

Property

Position | Location | Left

Position | Location | Top

 Property

Position | Size | Width

Position | Size | Height

28. Format the Order Date: text in bold.

29. Finally, click a blank area of the report header region.

30. In the Properties pane, set the Height property to 1.5.

31. Verify that the report header region looks like the following:

32. Save the report.

33. To preview the report, on the Home ribbon tab, from inside

the Views group, click Run.

Running the report renders the report in HTML. As the only report
parameter has a default value, the report will run automatically.

34. Verify that the rendered report looks like the following:
 35. To return to design view, on the Run ribbon tab, from inside
the Views group, click Design.

You will now add a table to the report body to display a formatted layout of the sales
order lines.

Add a table data region

In this task, you will add a table data region to the report body.

1. On the Insert ribbon tab, from inside the Data Regions group,

click Table, and then select Insert Table.

2. To add the table, click a blank area inside the report body.

3. In the Properties pane, configure the following position properties:

Property

Position | Location | Left

 Property

Position | Location | Top

4. The table will display five columns. By default, the table template
includes only three columns.

5. To add a column to the table, right-click inside any cell of the last
column, and then select Insert Column | Right.

6. Repeat the last step to add a second new column.

7. Hover the cursor over the cell in the second row of the first column to
reveal the field picker icon.

8. Click the field picker icon, and then select the Line field.

9. Notice that the table now includes a text value in the first row (header),
and a field reference in the detail row.

10. Add fields to the next four columns, in order, as follows:

o Product

o Quantity

o UnitPrice

o Amount

11. Verify that the table design looks like the following:

12. Save the report.

13. Preview the report.

The table includes a header and 12 sales order line rows. There are many
improvements that can be made by formatting the table layout.

In the next task you will:

 Format the table header by using a background color and bold font style

 Modify column widths to remove redundant space and to prevent long

text values from wrapping

 Left-justify the first column values

 Right-justify the last three column values

 Format currency values using a currency symbol (for USD)

 Add and format a total row for the table

Format the table data region

In this task, you will format the table data region.

1. Return to design view.

2. Click any cell in the table to reveal the gray cell guides.

The cell guides are there to help you configure entire rows or columns.

3. To format the table header, click the header row guide.

Selecting a row or a column guide selects all cells in the row or column.
Each cell is in fact a textbox. Formatting single textbox or a multi-
selection of textboxes can then be achieved by using
the Properties pane, or the ribbon commands.

4. In the Properties pane (or the ribbon), configure the following

properties:

Property Value

Fill | BackgroundColor DarkGreen (tip: hover the cursor over each color to reveal its nam

Font | Color White

Font | Font | FontWeight Bold

5. Select the first column guide.

6. In the Properties pane, set the Position | Size | Width property to 0.5.

7. Set the width of the second column to 2.5.

8. While pressing the Ctrl key, multi-select the last three column header

textboxes (Quantity, Unit Price and Amount).

9. In the Properties pane (or ribbon), set

the Alignment | TextAlign property to Right.

10. Set the Line detail textbox to left align.

11. On the Home ribbon tab, from inside the Number group, set the last

two detail (not header) textboxes (UnitPrice and Amount) to format
with a currency symbol.

12. To add a total row to the table, right-click the Quantity detail textbox,

and then select Add Total.
13. Notice that a new row, which represents the table footer, has been
added, and that the expression will evaluate the sum of Quantity values.

14. Repeat the last step to add a total for the Amount detail textbox.

15. In the first cell of the table footer row, enter the word Total.

16. Format all textboxes in the footer row to format as bold.

17. Verify that the table design looks like the following:

18. To remove any trailing space after the table, hover the cursor over the
dashed line between the report body and report footer region, and then
drag upwards to touch the bottom of the table.
19. Save the report

20. Preview the report.

21. Verify that the rendered report looks like the following:
22. In the Sales Order Number parameter box, replace the value
with 51721.

23. To re-run the report, at the right, click View Report.

This sales order has 72 sales order lines, and so the data will render over
many pages.

24. To navigate to the second page of the report, on the Run ribbon tab,

from inside the Navigation group, click Next.
 25. On page 2, notice that the table header does not appear.

You will address this issue in the next task.

26. Scroll to the bottom of the page, and then notice that the report footer
displays only the execution time.

In the next task, you will improve the footer text by appending the page number.

Finalize the report design

In this task, you will finalize the report design by ensuring multi-page reports render
appropriately.

1. Switch to the design view.

2. To ensure the table header repeats on all pages, first select any textbox
of the table.

3. In the Grouping pane (located along the bottom of the report designer),

at the far right of the Column Groups, click the down-arrow, and then
select Advanced Mode.

4. In the Row Groups section, select the first static group.

This selected the table header row.

5. In the Properties pane, set the Other | RepeatOnNewPage property
to True.

This ensures that the first static group (representing the table header)
will repeat on all pages.

6. In the table footer region, right-click the ExecutionTime textbox, and

then select Expression.

7. In the Expression window, in the expression box, append a space,

followed by & " | Page " &, to produce the following:

Visual Basic ScriptCopy

=Globals!ExecutionTime & " | Page " &

8. Ensure that a space follows the last ampersand (&).

9. In the Category list, select Built-in Fields.

10. To inject the page number value into the expression, in the Item list,
double-click PageNumber.

11. Verify that the complete expression reads as follows:

12. Click OK.

13. Drag the left side of the textbox to increase the width to the width of the
report page.

The design of the report is now complete. Lastly, you will ensure that the
page width is set to exactly six inches, and also remove the report
parameter default value.

14. To select the report body, right-click any table textbox, and then
select Select | Body.

As the table fills the entire report body, this technique must be used to
select the report body.

15. In the Properties pane, ensure that the Position | Size | Width property

is set to 6.

It is important the width is not greater than six inches, as rendering to

print format would break the table up across multiple pages.

16. In the Report Data pane, open the SalesOrderNumber report

parameter properties.
 17. On the Default Values page, select the No Default Value option.

18. Click OK.

19. Save the report.

Explore the final report

In this task, you will view the report in print layout mode.

1. Preview the report.

2. In the Sales Order Number parameter box, enter the value with 51721

3. On the Run ribbon tab, from inside the Print group, click Print Layout.

Print layout mode provides a preview of what the report will look like
when printed to the strict page size.

4. Navigate to pages 2 and 3.

In this lab, you won't publish the report. Paginated reports can only be rendered in
the Power BI service when they are stored in a workspace on dedicated capacity, and
when that capacity has the paginated reports workload enabled.

Next unit: Check your knowledge

Check your knowledge

200 XP

 3 minutes
Answer the following questions to see what you've learned.
1. 

Why are parameters important in Power BI paginated reports?

They allow the report developer to control the refresh interval of the report.

They allow the user to control aspects of how the report is rendered when the report
is run.

They are required so that Power BI can call the paginated report.
2. 

Power BI paginated reports are created by using which tool?

Power BI Desktop

Power BI service

Power BI Report Builder

3. 

Power BI paginated reports is an evolved technology that was built from which
original tool?

SQL Server Analysis Services

SQL Server Reporting Services

Microsoft SharePoint

Summary
Completed100 XP
 1 minute
You have learned about how to create a Power BI paginated report. Now, you can
connect to a data source, extract data into a dataset, and populate a table or chart
with that data. After you've finished creating the report, you can format your report
to make it visually appealing and informative, and then publish it to Power BI.

Create a Power BI Paginated Report

The estimated time to complete the lab is 45 minutes

In this lab you will use Power BI Report Builder to develop a pixel-perfect paginated report
layout that sources data from the AdventureWorksDW2020 SQL Server database. You will
create a data source and dataset, and also configure a report parameter. The report layout will
allow data to be rendered over multiple pages, and to be exported in PDF and other formats.

The final report will look like the following:

In this lab you learn how to:

 Use Power BI Report Builder

 Design a multi-page report layout
 Define a data source
 Define a dataset
 Create a report parameter
 Export a report to PDF
Lab story

This lab is one of many in a series of labs that was designed as a complete story from data
preparation to publication as reports and dashboards. You can complete the labs in any order.
However, if you intend to work through multiple labs, for the first 10 labs, we suggest you do
them in the following order:

1. Prepare Data in Power BI Desktop

2. Load Data in Power BI Desktop
3. Model Data in Power BI Desktop, Part 1
4. Model Data in Power BI Desktop, Part 2
5. Create DAX Calculations in Power BI Desktop, Part 1
6. Create DAX Calculations in Power BI Desktop, Part 2
7. Design a Report in Power BI Desktop, Part 1
8. Design a Report in Power BI Desktop, Part 2
9. Create a Power BI Dashboard
10. Create a Power BI Paginated Report
11. Perform Data Analysis in Power BI Desktop
12. Enforce Row-Level Security

Exercise 1: Get Started

In this exercise you will open Power BI Report Builder to create and then save a report.

Task 1: Create the report

In this task you will open Power BI Report Builder to create and then save a report.

1. To open Power BI Report Builder, on the taskbar, click the Power BI Report

Builder shortcut.

*Note: if prompted to update to the latest version of Power BI Report Builder,

click Cancel and proceed to step 2.

2. In the Power BI Report Builder window, to create a new report, in the Getting

Started window, click Blank Report.
 3. To save the report, click the File tab (located at the top-left), and then select Save.

4. In the Save As Report window, navigate to the D:\DA100\MySolution folder.

5. In the Name box, enter Sales Order Report.
6. Click Save.

Exercise 2: Design the Report Layout

In this exercise you will design the report layout, and explore the final report design.

Task 1: Configure the report header

In this task you will configure the report header.

1. In the report designer, notice the default report layout, which consists of a body region
and a report footer region.
 The body contains a single textbox ready for a report title, and the report footer
contains a single textbox describing the report execution time.

The default design will render the report title once, in the body, on the first rendered
page. However, you will now modify the report design by adding a report header
region, and by moving the report title textbox into this region. This way, the report
title will repeat on every page. You will also add an image of the company logo.

2. To add a report header region, on the Insert ribbon tab, from inside the Header &
Footer group, click Header, and then select Add Header.

3. In the report designer, notice that a report header region has been added to the report
layout.
4. To select the body textbox, click the “Click to add title” textbox.
5. To move the textbox, click the four-headed arrow icon, and then drag it into the
header region to then drop it at the very top-left of the report header region.

6. To modify the report title textbox text, click inside the text box, and then enter: Sales
Order Report
 To resize the textbox, you will first open the Properties pane. For fine-grained
control of location and size properties, you will need use the Properties pane.

7. On the View ribbon tab, from inside the Show/Hide group, check Properties.

8. To select the report title textbox, first click an area outside the textbox, and then click
the textbox again.

The textbox is selected when you see the border of the textbox highlighted and
resizing handles (small circles) appear on the border.

9. In the Properties pane (located at the right), scroll down the list to locate

the Position group.

The  Position group allows setting exact values for the location and size of report
items.

It’s important that you enter the values as directed in this lab. Pixel-perfect layout is
required to achieve the page rendering at the end of the lab.

10. Within the Position group, expand the Location group, and ensure that

the Left and Top properties are each set to 0in.

The location and size units are in inches because the regional settings of the lab
virtual machine is set to the United States. If your region uses metric measurements,
centimeters would be the default unit.

11. Within the Position group, expand the Size group, and then set the Width property

to 4.
12. To insert an image, on the Insert ribbon tab, from inside the Report Items group,
click Image.

13. To add the image to the report design, click inside the report header region, to the
right of the report title textbox.
14. In the Image Properties window, to import from an image file, click Import.

15. In the Open window, navigate to the D:\DA100\Resources folder, and then select

the AdventureWorksLogo.jpg file.
16. Click Open.
17. In the Image Properties window, click OK.
18. In the report designer, notice that the image was added, and is selected.
19. To position and resize the image, in the Properties pane, configure the following
properties:
o Position | Location | Left : 5
o Position | Location | Top : 0
 o Position | Size | Width : 1
o Position | Size | Height : 1
20. To resize the report header region, first select the region by clicking a blank area of
the region.
21. In the Properties pane, set the General | Height property to 1.
22. Verify that the report header region contains a single textbox and image, and looks
like the following:

23. To save the report, on the File tab, click Save.

Tip: You can also click the disk icon located at the top-left.

You are now ready to configure the report to retrieve a database query result.

Task 2: Retrieve data

In this task you will create a data source and dataset to retrieve a query result from
the AdventureWorksDW2020 SQL Server database.

1. In the Report Data pane (located at the left), right-click the Data Sources folder, and

then select Add Data Source.

It is possible to retrieve data from cloud or on-premises databases, or a Power BI

dataset.

2. In the Data Source Properties window, in the Name box, replace the text

with AdventureWorksDW2020.
3. In the Select Connection Type dropdown list, change the connection type from SQL
Server Analysis Services to Microsoft SQL Server.
4. To build the connection string, click Build.

5. In the Connection Properties window, in the Server Name box, enter localhost.

In the labs, you will connect to the SQL Server database by using localhost. This isn’t
a recommended practice, however, when creating your own solutions. It’s because
gateway data sources cannot resolve localhost.

6. In the Select or Enter a Database Name dropdown list, select

the AdventureWorksDW2020.
7. Click OK.
8. In the Data Source Properties window, click OK.
9. In the Report Data pane, notice the addition of the AdventureWorksDW2020 data
source.
10. To create a dataset, in the Report Data pane, right-click
the AdventureWorksDW2020 data source, and then select Add Dataset.

A report dataset is a different in purpose and structure from a Power BI dataset.

11. In the Dataset Properties window, in the Name box, replace the text

with SalesOrder.
12. To import a pre-defined query, click Import.
13. In the Import Query window, navigate to the D:\DA100\Labs\10-create-power-bi-
paginated-report\Assets folder, and then select the SalesOrder.sql file.
14. Click Open.
15. In the Query box, review the query, and be sure to scroll down to the bottom of the
query text.

It is not important that you understand the details of the query statement. It has been
designed to retrieve sales order line details. The WHERE clause includes a predicate
to restrict the query result to a single sales order. The ORDER BY clause ensures the
rows are returned by line number order.

16. Notice the use of @SalesOrderNumber in the WHERE clause, which represents a
query parameter.
 A query parameter is a placeholder for a value that will be passed in at query
execution time. You will configure a report parameter to prompt the report user for a
single sales order number which will then be passed to the query parameter.

17. Click OK.
18. In the Report Data pane, notice the addition of the SalesOrder dataset and its fields.

Fields are used to configure data regions in the report layout. They were derived
from the dataset query columns.

19. Save the report.

Task 3: Configure the report parameter

In this task you will configure the report parameter with a default value.

1. In the Report Data pane, expand the Parameters folder to reveal

the SalesOrderNumber report parameter.
 The  SalesOrderNumber report parameter was added automatically when the dataset
was created. This is because the dataset query included
the @SalesOrderNumber query parameter.

2. To edit the report parameter, right-click the SalesOrderNumber report parameter,

and then select Parameter Properties.

3. In the Report Parameter Properties window, at the left, select the Default

Values pages.

4. Select the Specify Values option.

 5. To add a default value, click Add.
6. In the Value dropdown list, replace the text with 43659.

Sales order 43659 is the value you will initially use to test the report design.

7. Click OK.
8. Save the report.

You will now complete the report header region design by adding textboxes to
describe the sales order.

Task 4: Finalize the report header layout

In this task you will finalize the report header region design by adding textboxes.

1. To add a textbox to the report header region, on the Insert ribbon tab, from inside
the Report Items group, click Text Box.

2. Click inside the report header region, directly beneath the report title textbox.
3. Inside the textbox, enter Sales Order: followed by a space.
4. To insert a place holder, immediately after the space just entered, right-click and then
select Create Placeholder.
5. In the Placeholder Properties window, at the right of the Value dropdown list, click
the fx button.

The  fx button allows entering a custom expression. This expression will be used to
return the sales order number.

6. In the Expression window, in the Category list, select Parameters.

7. In the Values list, double-click the SalesOrderNumber parameter.

8. In the expression box, notice that a programmatic reference to
the SalesOrderNumber report parameter was added.
9. Click OK.
10. In the Placeholder Properties window, click OK.
11. Click a blank area of the report header region, and then select the new textbox.
12. In the Properties pane, configure the following position properties:
o Position | Location | Left : 0
o Position | Location | Top : 0.5
o Position | Size | Width : 4
o Position | Size | Height : 0.25
13. To format part of the textbox text, inside the new textbox, select only the Sales
Order: text.

14. On the Home ribbon tab, from inside the Font group, click the Bold command.

15. Add another textbox to the report header region, and then enter the
text Reseller: followed by a space.

Tip: You can also add a textbox by right-clicking the canvas, and then selected Insert
| Text Box.

16. After the space, insert a placeholder, and then set the value of the placeholder to use
an expression.
17. In the Expression window, in the Category list, select Datasets.

18. Base the expression value on First(Reseller) value.

19. In the Properties pane, configure the following position properties:
o Position | Location | Left : 0
o Position | Location | Top : 0.75
o Position | Size | Width : 4
o Position | Size | Height : 0.25
20. Format the Reseller: text in bold.
21. Add a third (and last) textbox to the report header region, and then enter the
text Order Date: followed by a space.
22. After the space, insert a placeholder, and set the value of the placeholder to use an
expression based on the Datasets category, First(OrderDate) value.

23. To format the date value, in the Placeholder Properties window, select

the Number page.

24. In the Category list, select Date.

25. In the Type list, select a suitable date format type.
26. In the Placeholder Properties window , click OK.
27. In the Properties pane, configure the following position properties:
o Position | Location | Left : 0
o Position | Location | Top : 1
o Position | Size | Width : 4
o Position | Size | Height : 0.25
28. Format the Order Date: text in bold.
29. Finally, click a blank area of the report header region.
30. In the Properties pane, set the Height property to 1.5.
31. Verify that the report header region looks like the following:

32. Save the report.

33. To preview the report, on the Home ribbon tab, from inside the Views group,
click Run.

Running the report renders the report in HTML. As the only report parameter has a
default value, the report will run automatically.

34. Verify that the rendered report looks like the following:
 35. To return to design view, on the Run ribbon tab, from inside the Views group,
click Design.

You will now add a table to the report body to display a formatted layout of the sales
order lines.

Task 5: Add a table data region

In this task you will add a table data region to the report body.

1. On the Insert ribbon tab, from inside the Data Regions group, click Table, and then

select Insert Table.

2. To add the table, click a blank area inside the report body.
3. In the Properties pane, configure the following position properties:
o Position | Location | Left : 0
o Position | Location | Top : 0

The table will display five columns. By default, the table template includes only three
columns.
4. To add a column to the table, right-click inside any cell of the last column, and then
select Insert Column | Right.

5. Repeat the last step to add a second new column.

6. Hover the cursor over the cell in the second row of the first column to reveal the field
picker icon.

7. Click the field picker icon, and then select the Line field.

8. Notice that the table now includes a text value in the first row (header), and a field
reference in the detail row.
9. Add fields to the next four columns, in order, as follows:
o Product
o Quantity
o UnitPrice
o Amount
10. Verify that the table design looks like the following:

11. Save the report.

12. Preview the report.
 The table includes a header and 12 sales order line rows. There are many
improvements that can be made by formatting the table layout.

In the next task you will:

o Format the table header by using a background color and bold font style
o Modify column widths to remove redundant space and to prevent long text
values from wrapping
o Left-justify the first column values
o Right-justify the last three column values
o Format currency values using a currency symbol (for USD)
o Add and format a total row for the table

Task 6: Format the table data region

In this task you will format the table data region.

1. Return to design view.

2. Click any cell in the table to reveal the gray cell guides.

The cell guides are there to help you configure entire rows or columns.

3. To format the table header, click the header row guide.

Selecting a row or a column guide selects all cells in the row or column. Each cell is
in fact a textbox. Formatting single textbox—or a multi-selection of textboxes—can
then be achieved by using the  Properties pane, or the ribbon commands.

4. In the Properties pane (or the ribbon), configure the following properties:

o Fill | BackgroundColor : DarkGreen (tip: hover the cursor over each color to
reveal its name)
o Font | Color : White
o Font | Font | FontWeight : Bold
5. Select the first column guide.
6. In the Properties pane, set the Position | Size | Width property to 0.5.
7. Set the width of the second column to 2.5.
8. While pressing the Ctrl key, multi-select the last three column header textboxes
(Quantity, Unit Price and Amount).
9. In the Properties pane (or ribbon), set the Alignment | TextAlign property to Right.
10. Set the Line detail textbox to left align.

11. On the Home ribbon tab, from inside the Number group, set the last two detail (not
header) textboxes (UnitPrice and Amount) to format with a currency symbol.

12. To add a total row to the table, right-click the Quantity detail textbox, and then
select Add Total.
13. Notice that a new row, which represents the table footer, has been added, and that the
expression will evaluate the sum of Quantity values.
14. Repeat the last step to add a total for the Amount detail textbox.
15. In the first cell of the table footer row, enter the word Total.
16. Format all textboxes in the footer row to format as bold.
17. Verify that the table design looks like the following:

18. To remove any trailing space after the table, hover the cursor over the dashed line
between the report body and report footer region, and then drag upwards to touch the
bottom of the table.
19. Save the report
20. Preview the report.
21. Verify that the rendered report looks like the following:
 22. In the Sales Order Number parameter box, replace the value with 51721.

23. To re-run the report, at the right, click View Report.

This sales order has 72 sales order lines, and so the data will render over many
pages.

24. To navigate to the second page of the report, on the Run ribbon tab, from inside
the Navigation group, click Next.

25. On page 2, notice that the table header does not appear.

You will address this issue in the next task.

26. Scroll to the bottom of the page, and then notice that the report footer displays only
the execution time.

In the next task you will improve the footer text by appending the page number.

Task 7: Finalize the report design

In this task you will finalize the report design by ensuring multi-page reports render
appropriately.

1. Switch to the design view.

2. To ensure the table header repeats on all pages, first select any textbox of the table.
3. In the Grouping pane (located along the bottom of the report designer), at the far
right of the Column Groups, click the down-arrow, and then select Advanced Mode.
4. In the Row Groups section, select the first static group.

This selected the table header row.

5. In the Properties pane, set the Other | RepeatOnNewPage property to True.

This ensures that the first static group (representing the table header) will repeat on
all pages.

6. In the table footer region, right-click the ExecutionTime textbox, and then

select Expression.

7. In the Expression window, in the expression box, append a space, followed by & " |

Page " &, to produce the following:

VB Script

=Globals!ExecutionTime & " | Page " &

8. Ensure that a space follows the last ampersand (&).

9. In the Category list, select Built-in Fields.
10. To inject the page number value into the expression, in the Item list, double-
click PageNumber.
11. Verify that the complete expression reads as follows:

12. Click OK.
13. Drag the left side of the textbox to increase the width to the width of the report page.

The design of the report is now complete. Lastly, you will ensure that the page width
is set to exactly six inches, and also remove the report parameter default value.

14. To select the report body, right-click any table textbox, and then select Select | Body.

As the table fills the entire report body, this technique must be used to select the
report body.

15. In the Properties pane, ensure that the Position | Size | Width property is set to 6.

 It is important the width is not greater than six inches, as rendering to print format
would break the table up across multiple pages.

16. In the Report Data pane, open the SalesOrderNumber report parameter properties.

17. On the Default Values page, select the No Default Value option.

18. Click OK.
19. Save the report.

Task 8: Explore the final report

In this task you will view the report in print layout mode.

1. Preview the report.

2. In the Sales Order Number parameter box, enter the value with 51721
3. On the Run ribbon tab, from inside the Print group, click Print Layout.

Print layout mode provides a preview of what the report will look like when printed to
the strict page size.

4. Navigate to pages 2 and 3.

In this lab you won’t publish the report. Paginated reports can only be rendered in
the Power BI service when they are stored in a workspace on dedicated capacity, and
when that capacity has the paginated reports workload enabled. These requirements
do not exist for the class.

Congratulations!

You have successfully completed this Module, to mark the lab as complete click End.