cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver IV
Resolver IV

Export to file - Power BI Report with Filters / Parameters

HI All,

I am looking at the new (currently in preview) API for exporting Power BI reports as images/pdf files.
https://docs.microsoft.com/en-us/rest/api/power-bi/reports/exporttofileingroup 

There currently isn't a way to specify Filter/Slicer values for Power BI Reports!
This feels like a big omission to me, especially considering the inclusion of Bookmark capabilities.

Is there a published roadmap for this API endpoint anywhere AND/OR does anyone know if we can expect to see filter options anytime soon?

The API is unusable for many scenarios without this.
I am keeping my fingers crossed that it arrives soon.

Cheers,
Matt

11 REPLIES 11
Highlighted
Resolver IV
Resolver IV

Re: Export to file - Power BI Report with Filters / Parameters

I am struggling to find any details of 'future changes' or roadmap details for this API, so I created an idea for the inclusion of filter parameters.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/40060261-add-filter-options-to-th...

If you think that this functionality is missing - please go and upvote!
Thanks.

Highlighted
Helper I
Helper I

Re: Export to file - Power BI Report with Filters / Parameters

I feel your pain Matt!  I just spent 3 days writing code to use the API to export a report to PNG. I just got to the filtering bit - we need to create a separate image for each filter value - and have hit a brick wall. Did you find a workaround?

Highlighted
Resolver IV
Resolver IV

Re: Export to file - Power BI Report with Filters / Parameters

Hi @latearrival 

No workaround that I know of.
Depending on how many filters you have and what your model looks like, you might be able to use the Bookmarks feature?

I attempted to work out if we could generate bookmark state at runtime, then apply it to a PDF/PNG export, but this was a non-starter.

Looking at the idea site though... looks as though this has progressed to 'Under Review'.
So I guess we will have to wait.

Highlighted
Helper I
Helper I

Re: Export to file - Power BI Report with Filters / Parameters

Hi @MattCalderwood 

 

Yeah, I see that bookmarks is a workaround, albeit a poor one. The whole point of using the Export endpoint is to automate. And creating bookmarks is a manual process.

 

I'm very very new to Power BI so I can't get my head around this yet, but this article hints at being able to fudge Roles (handing in a fake user ID as a query string parameter when the access token is requested?) and using a permissions table to drive Row Level Security. So, to use the Export endpoint to produce a sales report for, let's say, a given product, I'm wondering if I could hand in a product code as the fake user ID and then... (I'm not sure what happens then 🙂 ).

 

 

Highlighted
Helper I
Helper I

Re: Export to file - Power BI Report with Filters / Parameters

Hey @MattCalderwood, I'm going to see if I can use Bookmarks until filters are supported.  I see in the Export to File doco that you can pass in a PageBookmark specifying "The bookmark to apply on a single page. Provide name or state, but not both", but that the bookmark name is currently not supported. Do you know how I can find out the "state" value for a given bookmark?

Highlighted
Resolver IV
Resolver IV

Re: Export to file - Power BI Report with Filters / Parameters

I went looking at options for this... The only oneI can find (the one I was previously experimenting with) was via the JavaScript embedding API. https://github.com/Microsoft/PowerBI-JavaScript/wiki/Bookmarks

The section at the end shows how to capture a bookmark programatically. The resulting object then holds the bookmark state.

// Capture the current view
let capturedBookmark = report.bookmarksManager.capture();

// Applied previously captured state
report.bookmarksManager.applyState(capturedBookmark.state);

 

I tried to work out how this state object was structured, in the hope I could dynamically create it in C#... but I didn't get very far.

As I mentioned before... If you only have a small number of filters and bookmark states that you need to capture, then you could use this method to fetch those states and reuse them as many times as you like. But as soon as you want to do anything really dynamic... you hit a bit of a blocker.

I am keeping my fingers crossed for quick updates to the API to add the parameters. 😀

Highlighted
Helper I
Helper I

Re: Export to file - Power BI Report with Filters / Parameters

Yeah, thanks Matt. I abandoned the bookmark path - too manual.

EDIT - I've now got it working with Identities and Roles. I can now export a report for every value of a filter.

In case this helps anyone else, here's my simple use case....

I want to create a report for every value of a filter called Country. Because filters aren't yet supported by the ExportTo API, I've used Roles instead.

I created a Role in Power BI Desktop called Country. The filter for this Role is on a table called countries, with a filter expression, [country_code] = username().  The countries table is already setup in my Model with the relevant relationships, and with cross filter direction = single. I then republished my report.

To get the ExportTo working for a specific country, I pass a country code through as the username in the body of the ExportTo API call:

 

{ "format":"PNG",
"powerBIReportConfiguration":
{ "identities": [
{ "username": "AUS",
"roles": [ "Country"],
"datasets": [ "XXXXXXXXXXXXXXX etc" ]
} ] } }

 

In my server-side code, all I need to do is call the ExportTo API in a loop, replacing the username in the body of the call with each country code.

Highlighted
Resolver IV
Resolver IV

Re: Export to file - Power BI Report with Filters / Parameters



Looks like the Idea has moved to 'Under Review'.
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/40060261-add-filter-options-to-th...


I will update this thread as things progress.
Feel free to go and up-vote! 😀

Highlighted
Skilled Sharer
Skilled Sharer

Re: Export to file - Power BI Report with Filters / Parameters

@MattCalderwood 

@latearrival 

I am using the REST APIs for ExportToFile in my Power Automate flow.

I can export the PDF with no issues in a normal use case, but next I want to try out exporting a PDF with a particular bookmark.

Use case is - I have 5 user groups and I will create 5 bookmarks (one for each).

Will send the PDF in an email to each of them as per their individual bookmarks.

 

However, when I try to put the bookmark name in my flow, it doesn't work.

Here is how my flow looks -

vmakhija_0-1596722370342.png

 

The error I get is -

{
  "error": {
    "code""BadRequest",
    "message""Bad Request",
    "details": [
      {
        "message""Error converting value \"Group1_bookmark\" to type 'Microsoft.PowerBI.ServiceContracts.Api.V1.PageBookmark'. Path 'powerBIReportConfiguration.defaultBookmark', line 1, position 74.",
        "target""request.powerBIReportConfiguration.defaultBookmark"
      }
    ]
  }
}
 
Appreciate if you can provide any inputs in case you have any ideas around this use case?
 
Regards

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.