[MUSIC] >> Good morning, good
afternoon, and good evening. Welcome to the Power BI
Dev Camp Session 16, Using the Power BI Export API to generate PDF and image files. We're here today with Ted
Pattison the Principal Program Manager from the Power
BI Customer Advisory Team. Good morning Ted, how are you doing? >> I am doing great
thanks. Well today we're going to look at the Power BI Export API and
we're going to talk about generating PDF and image files, so thank you for everyone
who's here at the live event, thank you for everyone
who's watching the recording after
the live event. Note that we do these sessions once a month
with Power BI Dev Camp. What I'm going to do is
I'm going to quickly move over to the portal. Your going to notice that at the portal we have
different sessions. This is Session 16
and if you want the PowerPoint slides for
this just simply click right here and the
slides will download for you likewise there's
two different samples that I'm going to
be showing today. There's the PowerPoint
slides if you want them. The two different things
that we're going to be showing today is one, a simple C# Console
application called ExportReportToFile
and because I'm showing code I want
you to have the code. Then at the very end
we're going to look at a another sample and
what I wanted to do here on the
slides is just make sure that you have
links to both of those because after the fact you want to go back
and look at the code, you're going to be
able to do that. Let's go ahead and start up. What we want to look at is how do we export the reports that we built and start
building PDFs, TIF files, Excel workbook
files things like that. Let's start by looking at the
different types of output. You have Power BI reports and you have Paginated reports, which one supports more outputs? Obviously the Paginated report. One of the things
that I want to start everyone to think about
it is that if you're building content
out for Power BI several years back it
was just all about Power BI Desktop and building the reports in Power BI Desktop but what we're
finding is that there are more and more scenarios where a Paginated report
is a better choice. I'll make a blanket statement
is if you're trying to claim that you are a Power BI content
builder professional, you can't say you don't know how to build Paginated reports. I think we're going to see
more and more people that will use Power BI Desktop
when we need to use Power BI Report Builder because there's lots of scenarios
where a Paginated report is a better choice and what we don't want is
we don't want people picking Power BI reports over Paginated reports
because they don't know or they're afraid
to go down that road. Now we're not going to have a session in
building Power BI Reports, but to build a Power BI Report obviously we have this
tool Power BI Desktop, we can build reports that
have multiple pages, I have a very simple
report that we're going to look at today and the idea is after you
have built the report, we can go to Power
BI Desktop and generally you can get a pretty good idea
what the report looks like in Power BI Desktop but you really need to publish
to the service and see what the report looks
like there and since this is all about
exporting if we didn't need to write
a code you can simply export this report to a PDF file or a
PowerPoint file. Now what I want to
do is to cover some of the things that might make the Power BI Desktop
approach not be so great. What's great about Power BI reports especially when you compare them to
Paginated reports, is they give us much better
interactive behavior. Now when we export a
Power BI report we have something in the Cloud
that just is able to capture images as a screenshot. What's important
to note is that it can only capture
what you can see. Let's go with a retro acronym how many people know
what WYSIWYG means? When I was building
applications back in the '90s and obviously
I mean the 1890s, we use the term what you
see is what you get. The idea here is let's go back and let's look at this
particular report and if you have a case where just for quick measure
I'll go down here, if you can't see everything in front of you you're
not going to be able to see what you can't see now when you export the report. Once again in order to
really be able to export Power BI reports you
have to make them self-contained on a
page by page basis. The export process just can't handle a table or matrices or any other things with
scroll bars where the data elements or the
data's not seen on the screen. Enter Paginated reports
and if you haven't gone down the road of
starting to build these especially when we get to
the topic of exporting, it's going to be important. This might be a
strange environment if you've never used it
before but the idea is that we're going
to be able to run a reports generator here and
as we build the report it's going to give us the ability to have abandoned
report writer so we have totals and subtotals and sub-subtotals inside there. The other thing that we
really get as we create these type of reports is
something that looks good, what we think of as
the printed page. Maybe we don't think so much of the printed page as
much as we used to do, but we think of PDF files which basically need printed pages. The idea is that as you have these pages down here and
then we go back and say, what does this look
like when I print it or when I export to PDF and being able to get to the
ability to have here at the bottom of the
report having whether it's published dates or
page numbers inside there, that's the obvious
big benefit that Paginated reports are going to give you over Power BI reports. I've told you to go and learn Paginated reports if
you don't know them. How mean can I be? But now what I want
to do is point out that our team within
Microsoft has published a really great
video series and so I wanted to put the
link right there but there's 25 individual lessons. It's got one of our highly articulate
technical professionals Peter Myers and Paginated
Bear go through this. Once again I don't
want to dwell on this topic but to
really get exporting down you're going to
often have to build Paginated reports to meet the
requirements that you want. We're just about to
get in the code. One more thing I want to point
out is that we're going to start exporting to
different file formats. There are controls at the tenant level for the Power
BI administrator to say, no I don't want you
to be able to export to CBS, CSP or PPTX. You have to make sure that at the tenant level these
settings are enabled. Now there are going to be three different API
calls that were going to make and
the first one is Export To File In Group. Now one more thing I
want to point out here, let's go back and we have great documentation
for our rest API. As Export File To Group, so I'll point you to
this documentation. Now let's go back here. But it turns out that
export to file and group is one of three methods
you have to call. When you call ExportToFileInGroup
it basically kicks off a export job and it
returns back to you an export object that shows you the status
of not started. But the idea is that
you have an export ID. Now you can call
GetExportToFileStatusInGroup, you download these API names, and when you call that
you pass the Export API to basically get an update on the status of the
current export job. Now the status property
is going to have not started or running and both of those being
it's not done yet and then what
we generally want is to see the succeeded status and there's also
a failed status. Now the export object is also going to give me some other interesting
information, it gives me percent complete so I can tell are we
halfway done yet, it gives me the report name because as you pass
in a Report ID, that's not a friendly name, but it also tracks what was the name of the
thing that we're printing, you have the resource location. The resource location is an interesting aspect
because when a job ends they make that export image file available at a URL endpoint and you're able to pull it down. It stays there for 24 hours, it's absolutely not made
available to anonymous access, you need the exact
same access token to download the file as
the start of the job. Then the last thing I
want to point out is you have the resource file
extension which will come back as .PDF or .XLSX or .CSV. Now the idea is that you call the first method to
start the job and then you call GetExportToFileStatus
one or more times until you get back a status successful and then you call
GetFileOfExportToFileInGroup. Sounds good. Let's look at that in a little bit
more visual way. Your code is on the
left and my code is in a simple C# application
that I'm going to show. But your code could
be an Azure function, it could be in some
Cloud-based application. But the idea is when you
call ExportToFileInGroup, the workspace ID and
the report ID which identify a specific
report are in the URL. Then in the body, we have a piece of JSON that
says what's the format and any other things about
starting this job. Now when you do that, it starts an async job, but the job has started. Now this reminds us of
if we're parents and we're driving our children to the grandfather's house
and it's four hours away, when do they start saying,
''Are we there yet?" Typically three
minutes into the talk. The idea is once you start
a job, you can keep saying, ''Are we there yet,'' calling this GetExportToFileStatus
and what we're going to see is that we can look at the status and the status might say
we're still running, which means come back later. But the idea is at some point, this export job will end. Then as it's done the next time that you
call inside there, what we're going to
see is a succeeded. Other things I'll point out
here is the report name, here is the file
extension inside there, there's some good
information inside, and then they also give
it the resource location. In the final call is when we call
GeFileOfExportToFileInGroup. Now let's do this. Let's look at C# that is going to give
us the basic pattern for exporting a report. Remember the idea is that when you call ExportToFileInGroup, it starts the job,
but now you've got to pull to figure out
when it's done. Step number 1 is we'll create an
ExportResourceRequest object. In this case, we'll just
include the format. Next we'll basically call
ExportToFileInGroup, we'll pass the workspace
ID and the report ID and that ExportReportRequest
and we're going to capture the export ID. The idea is that
now where I'm in the "are we there yet phase", where to call the
GetExportToFileStatusInGroup, and if you look below here, one of the things that
we're going to look for here hopefully as we
have a succeeded flag. If we get a failed flag, there's no use in hanging out
inside of the loop either. Once we see one of those, we're going to drop
out of the loop. As long as we're succeeded, now we're going to call it
GetFileOfExportToFileInGroup, and that actually
gives us a stream, allows us to take and return. Now there's the
sample application that we're now going to look at. With this sample application, what I wanted to do
is basically give you some simple starter code and try to show you
the capabilities. Here's an example. I'm going
to have some code that's going to call Export
Power BI report. We're going to pass things
such as the workspace ID, the report ID, what's the name of the file
that I want to generate. Then I'm going to go back
here and what you're going to see is that I have a
whole bunch of code, and if you run this, it will generate about
20 different files. But right now let's just say
we want to do it one time. Here, let's go back and
we're going to use Fiddler. We're going to go
ahead and we're going to start this job, and as we get Fiddler here, what you can see
is step number 1, what reports do we have. Now I find the
report that I want. Our next step is to
call this Export 2, and if we go back
here and we look at the JSON that we pass inside, you can see that this
request is for a PDF. Now every couple seconds, we're going to call this, ''Are we there yet method.'' Once again if I go back
here and we take a look, right now or in this loop
and I'm waiting 10 seconds. If you're more or less patient, you can change that number. But the idea is that we're just going to sit and wait here and at some point what we're going to see
status is still running. Now we come back here,
status is successful. This is the PDF file
inside and here's the call to bring down that
particular file. Also what I wanted to do to
demonstrate this is to go back into this place
where we're building all these files just so you can have a look at
what we just built. Under Exports, and I'll take the one that
was most recently built, and we'll open that up, and you can see
here's my PDF file. It looks fine inside of edge. Maybe if I say open web and
we started up in Chrome, it gives me a little
bit better sense because of the PDF
add-in I have, of what were given to users. Now once again we've
got to take it from a Power BI thing to a very universal file type
that I can give to just about any business user
on the planet right here. Now let's keep going. The next thing that we're
going to look at is drilling down on Power BI reports and then we'll have a section drilling down on
paginated reports. Now if all you include in the ExportReportRequest
object is the format, it doesn't really matter
whether you point it towards a Power BI report or
a paginated report. But there are some configuration
details that differ. We have two different
objects that we can put inside the
ExportReportRequest and the first one we're
going to look at is the PowerBIReportExportConfiguration
object. Now when you look
at this code here, here's the if you are writing C# with a Power BI.NET SDK, you can write the code that
you see in front of you. If you're not using.NET, you can't use the
Power BI.NET SDK. You're going to have
to figure out what the request body JSON
is going to look like. I just wanted to
show that as well. Now what you saw is that
we can create PDF files. We can also create
PowerPoint slide decks. Now let's say we go back
here and now we go back and what does this slide deck look like? That's pretty neat. Once again I had
a slide deck or I had basically a couple things here and it just quickly
turns into a presentation. If I come back here, this is not live once again, this is just an image, a screenshot. No interactivity. For PowerPoint specifically, I know that we have
communicated that we will have some other support for
PowerPoint in betting that might be live several
months down the road. But when we're using
the Export API, once again it's just a image that got added
inside of this slide deck. Now one other thing
that we can do is we can say when I
have a paginated report, I want to build it as a PNG. Well, unlike a PDF file, a PNG file can't
have multiple pages, it has to be one image. What happens when you have
a report and you say I want to basically
export it as PNG. If it comes back
as a single page, it will be returned
as a PNG file. If you have two or more, they return a ZIP archive
with the pages inside. Just be prepared for that
that if you don't know how many pages are in the
report that you're exporting, you really don't know if
you're going to get a.zip or a.JPEG file back. Once again one of the
neat things about calling the API is you get the status back and once the file is ready, there is a property that
tells you whether it's a.zip or a.png file. Now let's keep going. Another thing that
we can do is we can add filter information as I
explore a Power BI report. The idea here is
let's go back and take a look at the files
we have inside here. Let's say that we decided to build a couple of these
reports customer sales, so what's this going to be? This has got to be
something where we want to add what particular
states we have, what calendar year
that we're in. Now if I run something
that looks like this, what you can see. Sorry. That's a
paginated report, you should have
jumped there already. But what I want to do here
is let's open up this right here and what you could see is that if you
have one report, you can export it with the
filters for the West Coast, you can explore it a second
time with the filters from the central region, then for the East coast. You could just use a report as a template and just put
different filtering and basically generate
as many images as you have filter
combinations inside there. One little thing that
was confusing to me here and I just want to point it out is that when you set these, the report level filters and
you have a list of filters, so you would think you would put multiple filters in multiple
export filter objects, but we only support one filter. Basically you're going to
have one filter string and you're fine to use and
and/or inside there. But you'll find that
if you generate two export filter
objects inside here, the API will not work correctly. Now what else can we do? Let's say I wanted to export a report and I
have name bookmarks. Well, that's easy to do. I'm just able to add that
into the request itself. Now how do you get the
name of the bookmark? Now this is what I was
saying is where it's not the friendly name
of the bookmark. For instance, if I go back to this report right here and let's go ahead and open the report and now I come down here and
say show bookmarks, it's not going to be this
friendly name Western Region. But the idea is that
if you click here and then we go up into the browser, what you're going to see is that there's this query string property with a very strange
name of bookmark width. That is the thing if you need to find out what the
name of a bookmark is. Now you can export
and pass a bookmark, you can also decide to
export by one page. As I look at this, note that there is this
final section inside here, get rid of this bookmark width. But you'll see here
that at the very end, there is this part of the
URL that is the page name. Once again you can
take page names and you can come down here. While the page names
are strange to look at, if you want to export
a single page, you can hard code the
page names inside. Other thing to note
is that you can also export a single visual. Now visual names are
a lot harder to get. There's no Power BI rest API to get the names of the visuals. You can get them either by
dissecting the PBX file, which isn't really supported,
but you can do it, or once you use embedding, you can use JavaScript to basically query and
get visual names then. We're going to revisit
visual names a little bit later when we get to
App-Owns-Data embedding. But generally if
your application is introducing
commands in a visual, you can then have the
user execute a command, and because you're running in the context of that visual, you can pick up the visual name. We'll see some examples of
that a little bit later. Now just to close out
on Power BI reports. Well, there are limits. Now I also want to call out
that we have been using Gen1 and everything
I'm talking about with this Export API has
been in preview. In the next week or two, we're going to go
from preview to GA. But it's been running in Gen 1, so we've had a lot of
published constraints that are no longer applicable. What I'm going to
cover is now Gen 2. By the time we get to 2022, everyone should be using Gen 2, we won't have to really
differentiate between the two. But what we're able
to do is export 50 report pages per
minute per capacity. The idea is if you
have 10 reports and each one has five pages, basically you've got
50 report pages there. Now in Gen1, we called out that
there was a maximum of concurrent requests
that you can put up. There's no more maximum
concurrent requests, but we do have some queuing
and some throttling in place. Now let's call out some
visuals are not supported. R Visuals, PowerApps, Python, Power Automate and I just have to say well a
paginated report visual, we're going to
show a trick where this is going to
become important, but if you just export a report that has a
paginated report visual, that's not going to
give you what you want. One thing I want to call out is that a lot of companies like
to create custom visuals. Today for your
custom visual to be able to output into
an exported file, it has to be certified. We know there's an issue, we're working to remove
this limitation. Unfortunately, we
haven't done that yet. I will just tell you that the idea that you have a custom visual that's not certified, the ability to have that output into an export file
will be available, I'm going to be a little
bit vague, sometime in 2022 because I'm not the one who's pushing this thing out. Hopefully Q1 of next year. But once again, I just want to call out
if you do it today, you're going to see
just a strange box where the custom visual is. Now let's move to
paginated reports. When we do that, what we're going to see is that there is this other object, the PaginatedReportExportConfiguration
object. Now the first thing
to see is that there's a whole bunch
of format settings. It comes down to
report settings. If you're familiar with
Power BI Report Builder, you'll probably look at
these and these will seem familiar But if you want to set page height, page width, you want to set margins, if you don't want to
print the whole page, you just want a page range, you're able to add those inside there and make your request. Now we also have PDF. Paginated reports unlike
Power BI reports have a second option where
you can generate your Power BI report
as an accessible PDF. You can compare the two, you'll see there are some differences, but if you have
higher requirements for meeting accessibility, that is definitely
a good option. Now you might need
to pass parameters. Once again just like
a Power BI report where your report can
be a template and you can export 10
different times with 10 different filtering
options well, we can do the same thing
with paginated reports. With a paginated report, let me go back to my Power BI Report Builder and we'll go back to Design and we'll go back to
State right here. The idea is that when
you're building this, you're able to create a
particular parameter. Does it support multiple values? Yes. We want to filter by state. Then as you go and
you start building out your dataset properties, now I can look down what are the filters and then we have
a state filter implied. Before we get into
the coding aspects, let's say that we go back here into Power BI
and then back here, we will open this up. If we aren't using
Power BI embedding, but we're just basically loading the report inside the
Power BI service, it's going to give the user the option to pick the
parameter values they want. We are trying to
address some issues to make paginate reports
load a little faster. But while that is moving ahead, here what you can see is that if you've defined your paginated
report with parameters, it's simple to pass them as you run an export
job inside there. Here you can see that if a user is just
using the report, they're able to choose this, they're able to view the report, which now is going to rerun
the query again inside here. Now another neat thing
about paginated reports is what I think of as more of a data format that you're exporting as opposed
to a visual format. For instance, we
had that report. Let's go back and let's open the paginated report
which has been exported to an Excel file. How handy with some of your
users feel that they get export a report and
then all of a sudden, they're in their comfort zone. I'm in Excel, I've
got my numbers, I can start doing what
I want with them. Once again to be
able to export to either Excel format
or to an XML format, and let's say come back here. Here's the XML file that just
got generated inside here. Once again the data is available if someone
wants to use it. Or likewise if I go back here and I look at the CSV file, we're going to see the
same thing where it's just a very simple data file
that someone can consume. Once again obviously if you like this and you don't
know paginated reports, time to start learning
paginated reports. Now one thing that I found
confusing and I want to spend a little time on is that when you export
a paginated report, we support several different
image file formats, but you don't set the format
property to that format, instead you set the format to image as you can see
in the slide right here. Let's say I wanted to create
a TIF file of this report. I come back up here. Here is my TIF file,
what you can see. Actually, no. The TIF
file is inside there. Now you'll see that
some things like PDF files are really good things if you have a multi-page report. How about if I export as a PNG? The important thing to see here, and let me go ahead
and export it one more time just with paint, is that you have these
image file formats. Now back on the
slide, what are they? EMF, BMP, JPEG, they only print one page. If you have a PDF output and
you have a 10-page report, you're going to get
a PDF with 10 pages. If you export as
a JPEG or a PNG, you got basically a
file that has page 1. If you want to get
down to that level, you'd have to say
start at page 1, end at page 1, export as PNG. Then a second export job
to do page 2 and so on. The image files are useful, but once again they don't give
you all the pages at once. Now we're going to end
the section just by going through some of the limitations. Paginated reports actually have better throughput than
Power BI reports. Power BI reports are limited to 50 report pages per
minute per capacity. Paginated reports
are just limited to 50 reports regardless
of page count. Now that's the throttling. If you had a 700-page report, you're not going to get 50
of those printed per minute, but that's where the
throttling is going to say that you can't have
more than that. You can have really
big reports and realize that if your report takes a long time to load the data than a long
time to generate pages, there is a one-hour
limitation just due to the access token only
being alive for one hour. There is an issue with paginated reports that
are bound to Power BI datasets both in Power
BI embedding and export, we don't yet provide support
for service principles. We're working hard
to do that and we're hoping to have that by the
end of this month November. Also you can export
a paginated report with effective identity, but you have to use a user who's you user principal name matches a user and the current
Azure AD tenant. Now that we've done that, let me go over and see
if there is a question. I don't see any questions here. I'll do one more
section, but if you have questions here, you want
to put them in window. >> Got a quick one for
you from anonymous. Do you run this as
a standalone EXE from the command window? >> I think that question came a bit ago and it was probably about my C# application. Back here, this is the way to the I'd run it
to create some sample code. The idea is, you
just can run it. But would you be able to run it as a console
application in your environment? Maybe yes, maybe no. Maybe you'd like to take
this and turn it into PowerShell so administrators
could run these. You could put this code in a.Net server-side application, you can put this code
into an Azure function. Certainly C# is not the only
language you could run in. I'm mainly running it as a
simple C# console application just to give everyone the simplest starting point to get this thing
up and running. Hopefully that makes sense. >> Thank you so much.
I'll keep you posted in the next section if
there are any new questions that pop up. >> Super. Now two more sections. The next one is going to be
some deep developer stuff. Might be a little brain damage, I'm sorry in advance
if that happens. Then the last section is
where we're going to look into exploiting with
Power Apps and Flow. Let's go ahead and start
with the developer sample. Here what I wanted to do is
I wanted to show an example, let's go back and now
we're going to go to the second application that
I wanted to demo today. It's a.NET core application that runs on the
server and then we have code on the client that is written
inside of TypeScript. What I wanted to show is some of these I called a playbook, but the idea is I want to
embed a Power BI report, I wanted to step
through and show some of the different
options inside there. Without further ado, I'm going
to go ahead and run this. Now what you're
going to see here is that when this
application starts up, I want to demonstrate three
different types of reports. The first one is going to
be the Power BI report. Go ahead and click on that
and we'll add that in. When the Power BI
report comes down here, we're also going to
add some commands to be able to export
this report as PDF, as PowerPoint, or as PNG. Or if someone goes to
a particular page, we also have the export
page options as well. Now the way that they're
just going to work. Let's go ahead and
click on this. Right now it's
called to a Web API. The way that I put
this architecture together is with a
custom web entry point. Now the other thing
before I go into how the calls work
is on the client, you have this issue where
this user has gone to a particular page and
let's say the user now picks a particular
year, picks a filter. Now if I go ahead and click
and I say please print this report and I pass the workspace ID
and the report ID, they have no idea what filtering the user is currently
looking at. One, we're going to take this user on-demand
request to export a file. We also need to include
the filtering information, and one very easy way to do that is by using
custom bookmarks. Now so down here, notice that if I'm running JavaScript code behind
embedded report, I can say bookmarks
manager capture. That gives me this bookmark
that has a state property. That bookmark remembers
all the current settings inside of the browser. Here notice that I
can get the bookmark. If I want to print
a particular page, I can go, what is the active page
so I get the page name? Now if we look back here, there's the first thing
that I printed out, and I didn't have any filtering. I didn't mean to close
the application. Let's try this one more time. Now what we want
to do is let's go to a particular page and
in this particular page, let's got to sales by city. Let's go ahead and pick Florida and Georgia, Louisiana, Texas. Now I want to go ahead and I want to output this in
one of these formats. Now again what I'd
like to do for this demo is to look behind the scenes at what's happening. What's going to happen here
is that someone is going to click on the Export as PDF. We want to just print this one page and we want to print it with a current
filtering inside there. What you can see here is
that we have this call, we have another call. Let's go back to the
slides and let me walk through what's happening
at a high level. You've embedded the report and let's start with JavaScript that's
running in the browser. Someone clicks on
a button and so your JavaScript code then can capture a bookmark and say hey, this user wants PDF. What's important here is that you cannot
call directly from the browser to the
Power BI REST API because we're using
service principal. That's why app owns data
makes things harder. The idea is we need to introduce
this custom Web API and then what happens is my client side code
posted my Web API, my Web API then blocks while they do the
work to get the report. Now the web API is able to call into the Power REST
API service principal, it passes that bookmark, and ultimately it will
create the export file. While the client
is still blocking, then we find out and we're able to take the export file and return it here and then
return that back over here. By the time that process
is over, you look at this, the user has got this
one particular file, let's go ahead and
open that and you can see exactly what I
had inside there. Now going one step further. I seem to say that a lot. Let's say that what
we'd like to do now, we just did export a single
page and we saw that work. But now what we'd like to do is allow users to export
a single visual. This is something you can't
do in the Power BI service. Just to demonstrate that, let's say we go back
here and we open the Power BI service and I go to one of these visuals
and there's this export data, is that going to
give me an image? No, absolutely not. That's just going to
give me basically a text file in NCVS format
with numbers in it. What we'd like to do is to
give a better experience. The way that we're
going to do that, let's go ahead and start
this up right here. Let's load in the
Power BI report. When the Power BI
report gets loaded in, notice that down here
I go to this and there is three custom menus items
that I put inside here. Now if we go back over here. Here is the report configuration
object and the idea is that I don't like some of
the built-in commands like Spotlight export and the data,
I'm going to hide those. I'm going to add my
own commands inside. This is the way that
we get this effect. Then what's going to
happen when someone clicks on one of these particular
files right here? What you're going to see is that what would
happen if someone set the year inside here and what is the region
that we've set. We're going to want to make
sure that this visual when it gets exported has
those same settings, and you have to do extra work
to be able to get there. Now a quick
walkthrough is I have this command triggered
right here and the idea is that when the
command get triggered, this is going to run right here. So some of the things
that we are able to do, and let's say that I
collapsed the definitions. Well, let me dig back inside here and find
out the thing that I need. There's Export report, there's Export report page. This is Export
report page visual. The idea is that when
a user comes down here and says I want to export
this to a PNG file, what my code is then
going to do is first of all let's get the
active page name and also what we're going to see is that the code that
handled the event. Down here you have details such as the name of the page and the
name of the visual. Ultimately we're going to have this request and what's going to happen is I want to
just print out the visual. What we send across
the network to our Web API is going to be what's the type,
what's the filtering. This code actually looks at the slicers on the
current page and will take any slicer filtering and simply pass that along. The end result is that I want to be able to export that
particular visual to my user, have the user open it up and
see that the image they've created is exactly like the
image that they exported on. That was the painful part. What you see in
App-Owns-Data embedding is that there's no toolbar
to do a simple export, so you have to create a fairly elaborate architecture with a middle tier Web API entry point so that your
JavaScript can call back to your entry point and the
service principle can get the filtering
information and do things. Now let's look at an
easier way to do that. The idea is let's
say that instead of embedding these
Power BI reports, what I would like to do is let's embed a paginated report. I embed a paginated report, and once again this
is App-Owns-Data, I'm doing everything
with service principal, and one of the really
neat things that we have here is once this report
finally loads in, we're going to have
a Export menu. The idea is that in
the last example, I had to work hard to give a user an experience where they can export
a report on-demand, but now when you embed
a paginated report, there's the export menu, users can just help
themselves to be able to export as PowerPoint, Word, whatever they
need inside there. I find that working with paginated reports is going to give you a better
output to begin with, but can also make
things a lot easier in the sense that the Export
menu you know it's just built in to the
embedded experience for a paginated report. Now that we have seen that, we're onto one more topic. Now Thanksgiving is coming up
here in the United States. We always cook turkeys
on Thanksgiving. But a couple of decades
ago someone came up with this crazy idea called a
turducken. What is a turducken? It is something where you have a bird inside of a
bird inside of a bird. On the outside you
have a turkey, the next level is the duck, and the third level
is the chicken. When I first heard about this, I thought it was
preposterous and then I went to a Thanksgiving
with the editor Duncan and I fell in love. Just amazed. What does this
have to do with Power BI? Let's go ahead and
create Archer Duncan. The idea is that you can
use paginated reports, and we have this thing which is the paginated report visual. On my Power BI report, I can put paginated
report visual 1, I can put paginated
report visual 2. What's neat about
that is that if your paginated reports
are parameterized, we can add slicers on
the Power BI report that pass filtering information via parameters to the
paginated report. Maybe it's not quite three birds nested inside one another, but just another
element down here Power BI reports and paginated reports can be built on the same
Power BI datasets. That will make your
performance much better. Once again I need to
call out that today, Power BI datasets behind a paginated report are problematic for
service principals. Service principal cannot embed a paginated report based on a Power BI dataset
or export one, but we're hopeful that that limitation is going to end in the next week or two
by the end of November. Then the idea that you're
building reports on top, now how is this going to work? Now let's say that we go back
to our application here. Inside my application, I have a turducken. The idea is that here is your paginated report
that's building in. Let's say that I
decide I want to go ahead and put some particular
values inside there. It can take a second to load in. But what's nice with respect to the paginated report
that now is embedded inside the Power BI
report is that you can just export the paginated
report by itself, and we're waiting for
this guy to show. Come on. We're waiting on you. Keeps going back to 168, 200. Hopefully it will come out. But the idea is when this
thing finally shows here, I'm not sure why
it's not showing. Let me go ahead and try one more time just
with something simple. But the idea is that
there's no code, there's the Export menu. If we pass filters for
the Power BI report into the paginated report and then we export the
paginated report, it's going to hold the filter
settings inside there. Now if I quickly open
this report right here, what you're going to see as
you build paginated reports and then you take them
and you embed them inside of a Power BI report. As this loads in, maybe we'll go ahead and
just remove this at first. If I come back over here, here is the paginated report. The idea is that
you have to have the paginated report already
in the Power BI Service. Now we will connect to
that report inside there. Now if you have parameters, so that has a name parameter so then you're going
to have to go and find a column in one of your tables we're going to add it to the parameters last year. Now that you've done that, you're going to
say set parameters and what we should now be able to do this create a
connection between the two. As we do that now we should
be able to make changes. The final piece right here is that if I look at
this Power BI report, notice that if you
change the filtering, it's not going to automatically
apply those filters. In some cases, as long as it's not going to give you
performance issues, having something
where the second someone changes what
the settings are, it basically reruns the
query behind there. Now I think we've gotten down now where we just have
one more section left, but the moral of the story is that in
App-Owns-Data embedding, it's a lot of work to give the users the ability to export reports and report pages and
visuals, but it's possible. If you move to
paginated reports, users can just on-demand export things because of
the built-in export menu. Now we're going to finish off with one more
gratuitous topic. That's exporting reports
using PowerApps. For this, let's go back
here to the browser. What I'm going to do, let's say we go to
Power Automate. Inside of Power Automate, what I want to do is I
want to create a new flow. I'm going to say,
give me an answer in Clouds Flow so I can
just click on a button. Export this report. Here we're going to add a new step and I'm going
to type in Power BI. What we see is that in Flow, we have a lot of actions. The two I want to point out are the export to file for Power BI reports and export to file
for paginated reports. Now so let's say I add
one of these inside here. In a simple case it's going to show you the workspaces and you can just hard code what we want printed
out inside there. Also note that in a
demo I'm going to show, you can also enter custom
value and set these. But now that you have that, let's say we go down here
and now we'll have something like SharePoint, Create, File. If Ted could only spell. With SharePoint, what
do we want here? We just want a Crate file. What is the place that
I want to push out to? That SharePoint site
is going to be fine. I've created a document
library for my Export report. We'll put that in there. What is the name of the file? MyExport.pdf. What is the file contents? We can simply go back to that. What's neat about this? Let's go ahead and say this, is that when they
created this action, they totally took
away the complexity of an Async API and
you having the poll. That's all done behind the
scenes when you use this. If I go back here and we say let's manually
test the flow, let's go ahead and continue, I have to prove those different
connections inside there. Now our flow starts running. What we can see here is that
the flow starts running, I can see what things that we were getting
past inside there. In some amount of time, whether it's 10
seconds or 60 seconds, we should see this will
get to completion. When that is the case, let's go back up
in the SharePoint. Let's go to ExportedReports. As soon as this thing
ends inside here, sometimes it takes a little
bit at a time to warm up, we're going to
have a new report. I'm running out of time. I'm not going to wait
for this to close, but I want to get through
just a couple more slides. What I want to show is a sample
Canvas app that I built. The idea is that it's going
to use a custom connector. There are PowerApps
custom connectors and Power BI custom connectors. This is a PowerApps
custom connector. But the idea here is let's go back and I'm going to run this application to show
you what it looks like. We go ahead and run
this application. What it's going to do, it's
going to show the user all the workspaces that that
user has permissions to see. Well, let's say that
there is this workspace, Power BI Dev Camp
Demos, I go there. All of a sudden you know
there are the reports. What we have here is the
ability to get the workspaces, get the reports and each time we drill down on a
particular workspace, we get reports in groups
so we can fill those up. Also note that my code
can differentiate paginated reports from
Power BI reports. If I click on one of these note, I can also drill down and
see individual pages. Now that gives me this
stability to create this infrastructure right here. if I go back here, I can see my export
just finished, but now what I want to show. I know I'm about two minutes
away from finishing, so I'm going to go quickly here. But what I wanted to do is demonstrate a
PowerApps Solution. There's the Canvas app
you're looking at. It had accustomed connector
and then it also has a flow. If I look at this
flow right here, what you'll see is that
this is a flow that can be called from a Canvas app. When this finally appears here, you can say that the Canvas app is going to pass workspace ID, report ID, export
format, and page name. Now here you could also see we're doing things like export file for Power BI report. Now the cool thing about this is let's go and look at the
jobs that we have here. Now let's go back to our user that's using
the Canvas app. This user decides, I really need that
report as a PDF file. I need this as a PPT file. Notice that we're not waiting
and maybe individual pages. Let's say for these, we want PNG files, for
these inside here. Now if I go back
here and we refresh, what you can see is I've
just basically triggered a whole bunch of
flows that are now all running and I
start them all. A trick here is that
when you create a flow to call this
from PowerApps, what we're able to do is basically make
sure it's non-blocking. We start this, and
because we don't have to wait for the exported file to get back to the user
and download the browser, we're just storing these
things inside a SharePoint. For my proof of concept, every time we export, my flow is
automatically going to figure out what the
workspace name is, it's going to figure
out what the date is, and then it's going to add
the files inside here. Just think about now that you've added these files inside, we've made it so users in
SharePoint can get to that data pretty easily whether
it's Excel files, Word documents, PDF
files, what have you. Once again, two things
I wanted to show here. One is creating this
solution where flow has a export to file for Power BI report action and
for paginated report action. That can save a
lot of complexity. If all you want to do is have a flow that runs once a day and exports
a report to SharePoint, that would be
something that would take five minutes to create. The Canvas app here
that is able to show users workspaces
reports and pages and then it knows how to pass the right configuration
data when we're exploiting reports is
also an example of that. I just wanted to give
a quick demo and then I'll finish by doing
a plug for next month. We're going to concentrate
on working with Power BI APIs and
Power Automate. This sample had a custom
connector, but next month, we'll drill a lot more into
using Power Automate to call Power BI APIs and we'll get
into the Admin API space. For people who are managing Power BI environments and
you want to quickly see what are your unused
artifacts or what users have the ability to see
this report or this user, what reports can this user see? All that's available through
APIs and all can be put into simple Canvas apps with development and
that's going to be the focus on our
session next week. With that I'm now done with
the content and I think we should go through
and see if there are any other questions. >> Nothing in the chat.
Nothing in the Q&A. >> Everyone who has attended the live session day, thank you. Anyone who is listening
to the recording and got all the way through,
once again thank you. But looking forward
to seeing you next month to come back and more Power BI Dev Camp drilling into Power BI working
together with Power Automate. >> I just wanted to take a quick opportunity
to thank you Ted for your always insightful
sessions and your demos and sharing the
knowledge with everyone. Make sure to check out
the Power BI community at community.powerbi.com as well as our Power BI YouTube channel where you can find past
Dev Camps sessions as well as other things like Ted had mentioned the Power BI Paginated Reports in a day and other
things like that. Thanks so much, Ted, and I
will see you next month. >> Have a great day, everybody. [MUSIC]
