Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
May 31, 2023 3:27:30 AM
Dec 14, 2021 4:59:13 AM
This article explains how the Qlik Reporting connector in Qlik Application Automation can be used to generate a bursted report that delivers recipient-specific data.
For more information on the Qlik Reporting connector, see this Reporting tutorial.
This article offers two examples where the recipient list and field for reduction are captured in an XLS file or a straight table in an app. Qlik Application Automation allows you to connect to a variety of data sources, including databases, cloud storage locations, and more. This allows you to store your recipient lists in the appropriate location and apply the concepts found in the examples below to create your reporting automation. By configuring the Start block's run mode, the reporting automations can be scheduled or driven from other business processes.
Contents
Using a straight table as a distribution list
In this example, the email addresses of the recipients are stored in a straight table. Add a private sheet to your app and add a straight table to it. This table should contain the recipients' email address, name, and a value to reduce the app on. We won't go over the step-by-step creation of this automation since it's available as a template in the template picker under the name "Send a burst report to email recipients from a straight table".
Instead, a few key blocks of this template are discussed below.
- List Values Of Field. This block will return a list of all values of the specified field. The selected field should directly map to the recipient scope of data to be delivered. For each value, a report will be generated and distributed to the recipients who have that value assigned in the distribution list. We'll call this value the scope of the report, an example field could be Region to build a report for each different region and delivery to regional employees.
- The Create Report block and attached blocks will then be executed for each unique value of the field. Tip: you can dynamically name your reports by mapping the current field's value to the Report name parameter of the Create Report block:
- Add any sheets to the report by using the Add Sheet to Report block. Use the block Add Selection To Sheet to add selections. Add at least a selection for the current scope of the report. Also, apply a selection to the app using the Select Field Value block. This will reduce the straight table that contains the distribution list to the current scope.
- Now, use the block Get Straight Table Data and configure it to retrieve the distribution list. The important step here is to append each email address from the distribution list to a string variable. Also, add a semicolon ';' to the field to separate the email addresses.
- The scope from the distribution list should be used to add a sheet to the report and apply a selection for the current recipient. For example, if the recipients are sales reps, this could be used to provide an overview of each recipient's individual sales.
- Use the Generate Report block to finalize the report. Map this block's output to the attachment parameter of the Send Mail block. Map the string of email addresses to the To input field of the Send Mail block.
- Then make sure to execute the block Unlock Selections to allow the selections to be overwritten for the next report scope. Also, empty the email addresses from the string variable to make sure they aren't persisted for the next scope.
Helpful tips
- The Output block can be used to keep track of the automation's progress by showing the current scope.
- When building the report automation, you can test the email output by sending the report to yourself before using the distribution variable for operational execution.
- It's also possible to use the Copy File block from the Cloud Storage connector to store a copy of the report on any file storage system like Dropbox or SFTP.
Using an Excel File as a distribution list
In this example, the email addresses of the recipients are stored in an Excel file. This can be a simple file that contains one worksheet with headers on the first row (name, email & a value for reduction) and one record on each subsequent row.
- The first step is to gather the Excel file's item id. This can be retrieved from the URL when the file is open in a browser window or by executing the List Items In Root Folder Of Drive block. Execute this block in a test run to fetch these records.
- The results from the test run can now be used in other blocks, click the Item Id input field of the List Rows WIth Headers block and select the id key from the right item in the output returned by the List Items In Root Folder Of Drive block.
- Further configure the List Rows With Header block by specifying the worksheet name and the dimensions of the data (header row included). Below is an example of the excel file shown at the beginning of this section.
- The other steps of the automation are similar to the first part of this article so they won't be repeated here. Optionally, values from the Excel file can be used for reducing the report with the block Add Selection To Report.
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
Hi @martinamangiagli ,
If you click the Drive Id input field, the "do lookup" option will show in the dropdown, does that show your drive?
Hi @Emile_Koslowski ,
Our excel file called 'Manager Email List' has been saved in onedrive but, when we run the search on 'do lookup' among the options it does not appear.
Instead a 'business' file appears which is not present in onedrive and is not the file we need.
Hi,
"Append <null> to VEmailList error on Variable - VEmailList" automation block. How do I solve it?
