Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT

How to send a bursted report using Qlik Application Automation

100% helpful (1/1)
cancel
Showing results for 
Search instead for 
Did you mean: 
Emile_Koslowski
Employee
Employee

How to send a bursted report using Qlik Application Automation

Last Update:

May 31, 2023 3:27:30 AM

Updated By:

Sonja_Bauernfeind

Created date:

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.

  1. 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.

  2. 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:

    Create Report.png

  3. 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.

  4. 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. 

    Variable VEmailList.png

  5. 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.

    Add Selection.png

  6. 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.

    Send Mail.png

  7. 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

  1. The Output block can be used to keep track of the automation's progress by showing the current scope.
  2. 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.
  3. 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. 

email list.png

  1. 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.

    run automation from start block.png

  2. 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. 

    List Rows with Headers.png

  3.  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.

    List Rows with Headers example 2.png

  4. 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. 

    add selections to report.png

 

 

 

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.

Labels (2)
Comments
martinamangiagli
Contributor II
Contributor II

Hi @Emile_Koslowski ,


I would like to clarify about 'Using an Excel File as a distribution list'.


Specifically, in step 1 of your explanation, how to retrieve the 'Drive ID' of the excel file to insert it into the QAA in the 'List Item in root Folder of Drive' block.


Our excel file has been uploaded to onedrive.


Thanks in advance.

Emile_Koslowski
Employee
Employee

Hi @martinamangiagli ,

If you click the Drive Id input field, the "do lookup" option will show in the dropdown, does that show your drive?

Emile_Koslowski_0-1649753196644.png

 

martinamangiagli
Contributor II
Contributor II

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.

Screenshot (98).png

Emile_Koslowski
Employee
Employee

Hi @martinamangiagli ,

The lookup does not search for files but drives. The result you get looks like a valid drive id, can you select that?

If you then run that block (hover it and click "Test Run") and go to its history, you should find a list of all items (files & folders) from that drive. In that list, you can then search for the file named "Manager Email List" and take that file's item id for the List Rows With Headers block.

In this automation, the role of the List Items In Root Folder Of Drive block is to help you find the right file. You can also ignore that first block (disable it by right-clicking) and directly map the drive id & item id in the List Rows With Headers block by using that block's lookups.

 

 

sophie_bd
Partner - Contributor III
Partner - Contributor III

Hello,

with this method each generate report counts 1 run out of the 100 basic available.
Is there a method to get the generate report block out of the loop?

regards,

Sophie

Sonja_Bauernfeind
Digital Support
Digital Support

Hello @sophie_bd 

Please post your query and the challenge you are facing in our Application Automation forum, where our support folks and active community can assist you. This sounds like something we cannot approach directly in an article.

All the best,
Sonja 

AlejandroJuarez
Contributor III
Contributor III

Hola , tengo un problema , tengo una tabla con multiples medicos que se les envía su reporte personal del mes terminado 

pero si el medico no atendió pacientes ( vacaciones x ejemplo en el mes terminado ) las selecciones no funcionan  le envía un reporte con toda la información abierta de todos los medicos 

Es posible colocar un filtro de cantidades por ejemplo dentro algún bloque ? consulta_medicas>1 por ejemplo 

 

Son geniales las automatizaciones  le dan un excelente valor agregado a Qlik 

sigan por mas !!!!!

 

shea
Contributor
Contributor

Hi, 

"Append <null> to VEmailList error on Variable - VEmailList" automation block. How do I solve it?Capture.PNG

Sonja_Bauernfeind
Digital Support
Digital Support

Hello @shea 

I recommend you post about your issue in as much detail as possible in the Qlik Application Automation forum, where our active support agents and users can assist you.

I'd also like to direct you at Qlik Cloud Tabular Reporting, in case this would also suit your purposes.

All the best,
Sonja 

Version history
Last update:
‎2023-05-31 03:27 AM
Updated by: