Automated filter and slicer input is a powerful feature in BI Helper to apply updated filter and slicer values and email IDs directly from Power BI. It eliminates the need to manually update these values each time you run a job in BI Helper. You can set up your BI Helper jobs to run on a schedule, and your PDF/ PPTX reports will be created and emailed to the updated set of recipients on time, every time!
This tutorial uses the Retail Sales Report in Power BI to explain how automated filter and slicer input works in BI Helper.
IMPORTANT: The Retail Sales Report in this example uses a slicer with the title District Manager whose values are read from the DM column in the Store table. Alternatively, it can be set up as a report level filter (Filters pane > filters on all pages).
Steps in Power BI (see image above)
1. On the first page of your Power BI report (PBIX/ Desktop), create a table with four columns: Filter Value (filter and slicer values for which PDFs are to be generated), To, Cc and Bcc. This table provides the automated filter and slicer input to BI Helper.
2. In the example, the table is titled Filters and Contacts, but you can give it any other title in Power BI.
3. Multiple email IDs in the To, Cc and Bcc columns need to be separated by a semi-colon (;).
4. The values in these columns are to be read from your Power BI report.
5. Publish your PBIX to Power BI Service and select it as your BI Helper job. Hide the Power BI page with the Filters and Contacts table so that it is not part of the output PDF/ PPTX reports.
Steps in BI Helper
1. Follow the steps in this tutorial to create a new job in BI Helper.
2. Go to Generate PDF > Apply Filters, Slicers and Email IDs and select the Automated Input mode.
3. Specify the Filter and Slicer Input in the box. Follow this article for filter/ slicer syntax. For example, the District Manager Slicer in the Retail Sales Report is to be entered as District Manager<>Store/DM.
If you use a report level filter instead of a slicer, enter it as Store/DM.
4. Enter Filters and Contacts (the title of the table in the first page of your Power BI report) in the Source Visual box.
5. Name the output PDF/ PPTX, select the visuals for Excel export and Save this tab. Go to the Send Email tab and enter the email settings and body. Click on Finish to return to the Job Summary tab.
6. When you run this job, BI Helper downloads the filter/ slicer values from the Filters and Contacts table. It then generates a PDF/ PPTX for each value and sends it to the To, Cc, and Bcc email IDs.
Note: You can define multiple slicer and filter combinations in the first column of the Filters and Contacts table in BI Helper.
