Parsing bookmarks as filters for rolling report subscriptions (exports)

[NTAJM]

Hi there,

I'm relatively new to Power BI so your wisdom and guidance is much appreciated!

I'm working on a feature where we want to allow email subscription to a given report that works on a rolling basis. For example, I want the "Student Results" report, but want to filter for only the 9th Grade, and then I want that sent to me on the first of each month with the last month's results.

Obviously, I can do this using 'Export to File in Group' by using a Filter, and then managing (from my end) that the filter updates with a given timeframe each month. However, what I'm struggling with is how I could potentially use existing bookmarks as filters on a report: since as I understand it, a bookmark is basically like an 'individual filter'. Doing this would avoid a whole other layer of complexity in trying to fetch individual fields by which to filter by, since my client has literally thousands of reports across many different topics and each one has different fields by which one could filter.

The problem right now:
- Trying to subscribe to a bookmark means the date range will never change.

In an ideal usecase of the above example:
1. The user looks at a report about all the student results
2. The user filters for the 9th grade
3. The user saves this as a bookmark

4. The user can then 'subscribe' to that filtered report for each month (whereupon I apply the date filter)

Without this ability, given you can only filter for specific columns/tables within a report, this is how my solution is looking:
1. The user opens the 'subscribe' window
2. The user selects a report
3. I write some fancy but annoying backend code to fetch all the possible filter values for that specific report and what type of filter they'd be (e.g. string, datetime, number, etc.)
4. The user then selects which values they want to filter
5. The user then manually enters the filters they want (presumably with no report preview in this step, though I guess that could be done with some sort of... iframe?)

6. The user can then 'subscribe' to that filtered report for each month (whereupon I apply the date filter)


Options to make the first scenario possible:
- The ability to parse bookmark values so I can write a function turning them into a URL filter
- The ability to have bookmarks apply across whole reports, with filters that override on date so that the date doesn't get 'stuck' (e.g. If the bookmark selects 1 Jan to 31 Jan, that in July I don't still receive the January report).

When I read the documentation about exporting, it does mention bookmarks, but only for filtering individual pages, and as far as I can see I couldn't then modify that bookmark for rolling dates, for example.

Alternatively, if you have other ideas for how the above could be achieved more elegantly, I'd love suggestions!

[v-yiruan-msft]

Hi @NTAJM ,

I'm not quite sure what you need, do you want to send a subscription email at the beginning of each month with reports showing only the previous month's data? It is worth noting that email subscriptions are sent with the report's default filter and slicer states. Any changes to the defaults that you make after subscribing don't show up in the email. Please find the details in the following official documentation.

Subscribe to a report or dashboard in the Power BI service

Here is one similar idea shared on Power BI Ideas , you can vote it up and add your comments there to improve Power BI on this feature. And you can raise one new idea ticket on Power BI Ideas. 

In addition, please check whether the following link can achieve your requirement.

Filter a report using query string parameters in the URL

https://reportserver/reports/powerbi/Store Sales?rs:Embed=true&filter= Store/Territory eq 'NC' and Store/Chain eq 'Fashions Direct'

Best Regards

[NTAJM]

It does also look like the "Ideas" post is in regards to what we need: I've been trying to find a workaround using file exports and then sending emails from our system, but certainly an integrated subscription system would be a lot cleaner. Is there any update as to when this is expected? If so, I can at least feed it back to my client as an estimate for when we can work on this without spending a lot of hours/effort on it.

[NTAJM]

Hi there,

Thank you for responding! I'm relatively new to Power BI so I suspect I'm using the wrong terminology. I found the first link you provided quite confusing (e.g. I don't know what "default filter and slice states" are), so I'll try to rephrase what we're trying to do.

So the basic idea is that our users look at reports each day, and sometimes they change the values for certain fields and make a bookmark. So far, so good.

But then, they decide they want to receive that same report, with those same bookmarked changes/values, sent to their email each month with (as you said) only the last month's values. If they try to subscribe to that bookmark (so to speak), then the date won't change.

For example:

- I want all the student grades, for ninth grade only, for the dates 1 January to 31 January. If I subscribe to this bookmark as things stand currently, I will continually be sent the ninth-grade reports for January in February, March, April, etc.

What I would want is that I get sent the ninth-grade reports, but with rolling dates of content. Is there any way to do this using bookmarks? Or alternatively, is there some way to 'parse' bookmarks into filters so I can use the "FIlter a Report by Parameter" link as you sent?

My last question is with regards to a one-liner low down on that same link: is it true that we could not, for example, subscribe to/export a PDF with filters on it? Are there any plans to change that?

All the best and thanks again!

[v-yiruan-msft]

Hi @NTAJM ,

You can create a relative date slicer like the one below to filter out the previous 1 month's data first.

Creating a relative date slicer and filter in Power BI

Then refer the following official documentation to subscribe to a report or dashboard for the users in the Power BI service.

Subscribe to a report or dashboard in the Power BI service

Best Regards