PETER MYERS: Welcome to this March, 2023
update for Power BI Paginated Reports.
Since the last recording, there have been a number of
exciting announcements and new features.
I'm happy to present them to you here in this video.
There are four topics to present,
starting with support for all Power BI licenses,
a new migration tool,
web authoring, and report drillthrough.
Formerly, paginated reports were a Premium feature.
Either the workspace needed to
be on a dedicated capacity with
a premium license, or you needed
a Premium Per User license.
Well, the news is
that paginated reports are now
available with all licenses.
Note that users with a free license can only
publish paginated reports to their personal workspace.
Note also that the use of the exportToFile API,
this is in programmatic work,
to export paginated reports
remains a Premium-only feature.
Next, the migration tool.
Built into SQL Server Reporting Services in 2017, 2019,
and 2022, there is a migration tool that
can help you to automate reports directly to Power BI.
Consider offloading Reporting
Services workloads to Power BI.
That way, you can allow SQL Server to
focus on core business operations.
Let's take a look at how the migration tool works.
Here, I am in Power BI Report Server.
It's simply a matter of going to the Publish command
and publishing all reports, or in this case,
publishing a selection of reports.
Having selected several reports,
I then click on Publish to Power BI.
The next step then is to determine the target workspace.
If you're working with information protection,
you might also apply sensitivity labels.
I think I'll go ahead and click
"Publish" and the migration happens automatically.
Here we can see that three reports
were successfully published.
I then have the option of
opening up the Power BI service,
allowing me to verify that
the migration has indeed succeeded.
Web authoring. You can now easily create
paginated reports based on
Power BI datasets by working in the Power BI service.
Specifically, you can add filters,
and a table, and styling, and totals.
At this stage, the experience is pretty simple,
but expect to see incremental paginated
report web authoring features coming soon.
Now, consider this, you can download
the RDL file and open it in Report Builder.
Then in Report Builder, you can
enhance the report design.
For some web authors,
this could be the perfect starting point:
Use the service to create
a basic report and then
refine the design in Report Builder.
Let's see how it works in demonstration.
Here in the Power BI service,
I have the Sales and Marketing workspace.
Inside this workspace, I have
a dataset and a Power BI report.
Let me go ahead and select the Sales Analysis dataset.
Here on the dataset landing page,
I can use the Create
a Report option to create a paginated report.
This is the web authoring experience.
Let me start the design of this report by
dragging the Fiscal Year field into the filters.
I'll then expand Fiscal Year and I'll
filter by fiscal year 2021.
To create a formatted table,
I will then drag the Month field
and drop it onto the report canvas.
Here, I can see all of the months of fiscal year 2021.
Next from the Sales table,
I will drag Sales field and drop it into the table.
Then from the Target table,
I will drop the Target field into the report.
I have some basic formatting abilities.
For example, I can change the widths of columns.
Now let me introduce Variance and Variance Percent.
I have the option also with table format
to turn off the totals or put them back again.
Lastly, I can move the table around on the page.
Switching to the View menu,
I have the ability to look at the report as a web layout
in web format or switch back to a print layout.
Then on the File menu,
I have the ability to save,
print, or download.
Let me go ahead and save.
I'll save this as the Sales Performance report.
Now, let me download the report as well.
Now that I've downloaded the RDL document,
I'll go ahead and open it in Report Builder.
This is just a standard RDL document
allowing me to work beyond
the web authoring capabilities.
For example, formatting the header.
I can then save, publish back to the workspace.
Report drillthrough.
Drillthrough allows navigation
between paginated reports.
It's set up by configuring an action on
a text box or other elements in the report design.
While this capability has been supported in Reporting
Services reports since
the very beginning of the product,
it's now available in paginated reports for Power BI.
However, there are some limitations to be aware of.
Firstly, you must enter the report directly,
there's no ability to browse for the report.
Secondly, you must add and set up
the target report parameters yourself.
Report Builder doesn't give you
assistance in setting that up.
Let's see how it works in demonstration.
In this workspace, there were two paginated reports:
the Sales Orders report and
the Sales Performance report.
Let's start by taking a look at the Sales Orders report.
It has two parameters requiring the entry of
a fiscal year and the selection
of one or more employees.
I'll go ahead and select the first.
Here we can see a list of all sales orders
for Pamela, the salesperson.
Let's now take a closer look at the report design.
I'll go ahead and download
the report and then open it in Report Builder.
Notice then in the report Data pane
that there are two parameters,
Date Fiscal Year and Salesperson Employee ID.
I then close this report.
Let's take a look at the second paginated report,
Sales Performance.
Here we can see a table visual
and when I drill into the United States group,
I can see the individual salespeople.
But what I'd really like to see
is the ability to click on
a salesperson and then
drillthrough to their order details.
Now that's possible.
I go ahead and I download
and open up the report in Report Builder.
I then select the text box for
the salesperson and I go ahead and open its properties.
Then on the Action page,
I select the option to go to report.
Now, to specify the report,
I must type in the report name.
Because I know that the report has two parameters,
I click the Add button twice,
and then I must enter the names of those parameters.
Next, I must set the value.
For Date Fiscal Year,
I use the parameter of this report,
that is the value of the Date Fiscal Year.
For the Salesperson Employee ID,
in the dataset,
I have an Employee ID field and
so its value will be passed to that parameter.
Now before I click OK and complete that setup,
I want to provide an obvious clue to report
consumers that there's a link
enabling this drillthrough.
I select the Font page, and I
modify the Color from black to blue,
and I set the Effects to Underline,
so it appears to be like a hyperlink.
Now I click OK on the Home ribbon,
I publish, and
I overwrite the current definition
of that Sales Performance report.
I then close Report Builder.
Back here in the Power BI service,
I go ahead and press F5 to reload the page.
Now I drill through from
a country and select Michael Blythe.
Here is the drillthrough showing
me the order details for Michael Blythe.
Finally, some resources that
you might find helpful related to
the topics presented in this video. Thanks for watching.