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-step process to generate and distribute Power BI reports — 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.
- You can now view in this page the Power BI workspaces and reports/dashboards to which you have access. Select the report you wish to distribute.
- Set the ‘Filter Input Mode’ to Manual and select a reporting period from the dropdown.
- Define the report slicers — District Manager and Date. Enter the slicer values for which the reports are to be run in the table in the PDF tab.
BI Helper supports slicers set up on the first page of a 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 District Manager<>Store/DM.
The reports are to be run on the names of the District Managers — Allan Guinot, Andrew Ma, Chris Gray and so on. These are the slicer values.
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 report recipients in the To Email, CC Email, and BCC Email columns. Multiple email IDs in any column are to be separated by semicolons (;).
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 both 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. During setup, BI Helper will email you an authorization request to your sender email ID, which you 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!