In this tutorial, we will walk through the steps to set up a Power BI report for bursting and distribution on BI Helper, using the Retail Sales Report as an example. This report has three pages — Title, Business Summary and Stores. On the Title page, it has two report level slicers — Date and District Manager.
BI Helper follows a three-stage process to configure Power BI reports for bursting and distribution. These stages are — Schedule, PDF, and Email. The detailed steps are as follows:
- Log in to BI Helper https://app.bihelper.tech and create a new job in the User Jobs tab by clicking on the large ‘+’ icon.
- Click on the Edit icon (orange pen) below the new job, to go to the Schedule tab. Enter the job name, frequency and start date and time.
- Select Save & Next to go to the PDF tab. Click on ‘Sign in with Microsoft’, and enter your Microsoft credentials to log in to Power BI Service.
- This will cause BI Helper to fetch the Power BI workspaces and reports/dashboards to which you have access. Select the report you wish to distribute.
- Now set the ‘Filter Input Mode’ to Manual and select a reporting period from the dropdown.
- The next step is to define the report slicers — District Manager and Date, and populate their values for which the reports are to run in BI Helper.
BI Helper supports slicers on the first page of Power BI report. Slicer names must follow the format: <Slicer Title><><Table Name>/<Column Name>. BI Helper identifies slicers using the Title field defined in the slicer formatting options.
Defining the District Manager slicer: In the image below, District Manager (slicer title) uses the DM column in the Store table. So the slicer is to be named as District Manager<>Store/DM.
The values for this slicer are the names of the District Managers — Allan Guinot, Andrew Ma, Chris Gray and so on.
Defining the Date slicer: The Date slicer uses the Date column in the Dates Master table. So it is to be named as Date<>Dates Master/Date.
The date values are to be defined in the ISO 8601 format (e.g., 2019–08–01T00:00:00) and the reporting period in the StartDate :: EndDate format. E.g., for the reporting period August 1 — 31 August 2019, the date slicer value is to be defined as 2019–08–01T00:00:00 :: 2019–08–31T00:00:00.
Multiple slicers are separated using the pipe operator ‘|’ in the PDF tab.
7. The slicer names and the values defined above have to be entered in the PDF tab. To apply both slicers together, enter the Slicer Name in the table (see below image) as Date<>Dates Master/Date | District Manager<>Store/DM.
8. Enter a corresponding slicer value as 2019–08–01T00:00:00 :: 2019–08–31T00:00:00 | Allan Guinot. Add a row for every slicer combination for which a PDF report is to be generated.
9. Finally, enter the email IDs of the desired report recipients in the To Email, CC Email, and BCC Email columns. If there are multiple email IDs in any column, please separate them using a semicolon (;).
If your report uses filters in place of slicers, the above steps remain unchanged except for the way the filter names are defined. Filter names follow the format <Table Name>/<Column Name>, and the filter names and values need to be entered in the same columns as the slicer names and values.
If your report uses slicers and filters, you can separate them using the pipe operator ( | ) in the table in the PDF tab.
10. Save the PDF tab and move to the Email tab. In the ‘From email’ box, enter the email ID from which your reports are to be sent. BI Helper will send you an authorization request to use this as your sender email ID, which you will need to accept.
11. Create the email subject and body text for the mail to your report recipients, to which the PDF reports will be attached. BI Helper supports full HTML email with images, logos, multiple fonts, and colors.
12. Save the Email tab to return to the User Jobs page. Click on the Run Now button (Play icon) to execute your job!