Using Qlik Application Automation to create and distribute Excel reports in Office 365

[MattGrayndler]

With Qlik Application Automation, you can get data out of Qlik Cloud and distributing it to different users in formatted Excel. The workflow can be automated by leveraging the connectors for Office 365, specifically Microsoft SharePoint and Microsoft Excel. 

Here I share two example Qlik Application Automation workspaces that you can use and modify to suit your requirements. 

Content:

• Considerations
• Example 1: Scheduled Reports
• Example 2: On-Demand Reports
• Environment

Video:

Considerations

• This example is built on distributing a SharePoint link. It is also possible to use attachments with the Mail block (see Creating a Qlik Reporting Service report).
• Qlik Application Automation has a limit of 100,000 rows when getting data out of a Qlik Sense straight table object.
• The On-Demand example uses an extension in QSE SaaS to send data to the Automation. An update to the Qlik Sense Button object is expected soon, which will provide a native way to pass selections to an Automation.

Example 1: Scheduled Reports

1. Download the 'Scheduled Report.json' file attached to this document.
2. Create a new Automation in QSE SaaS, give it a name, and then upload the workspace you just downloaded by right clicking in the editor canvas, and selecting 'Upload workspace'.
3. Select the 'Create Binary File (Personal One Drive)' block, select 'Connection' in the block configurator to the right, and then create your connection to Microsoft SharePoint.
4. Select the 'Get Straight Table Data' block. Under 'Inputs' in the block configurator, lookup your the App Id, Sheet Id, and Object Id for the relevant QSE SaaS table you wish to output.
5. Select the 'Create Excel Table With Headers' block, select 'Connection' in the block configurator, and then create your connection to Microsoft Excel.
6. Select the 'Send Mail' block. Under 'Inputs' in the block configurator update the 'To' to reflect the addresses you wish to deliver to.
7. With the 'Send Mail' block still selected, select 'Connection' in the block configurator and add your Sender details.
8. To test, Save and then Run the Automation
9. If you receive any warnings or errors, navigate to the relevant blocks and ensure your Connection is selected in the block configurator. 
10. Select the 'Start' block. Under 'Inputs' in the block configurator, change Run Mode to Scheduled and define your required schedule. 

Example 2: On-Demand Reports

Note - These instructions assume you have already created connections as required in Example 1.

1. Download the 'On-Demand Report v3.json' file attached to this document.
2. Download and install the 'qlik-blends' extension. See: 
   https://github.com/rileymd88/qlik-blends/files/6378232/qlik-blends.zip
3. Create a new Automation in QSE SaaS, give it a name, and then upload the workspace you just downloaded by right-clicking in the editor canvas, and selecting 'Upload workspace'.
4. Ensure your Connections are selected in the block configurator for each of the following blocks, 'Create Binary File (Personal One Drive)', 'Create Excel Table With Headers', 'Add Rows To Excel Worksheet Table (Batch)', 'Create Sharing Link', and 'Send Mail'.  Save the Automation.
5. Select the 'Start' block and ensure Run Mode is set to Triggered. Make note of the of URL and Execution Token shown in the POST example. 
6. Open your chosen QSE SaaS application, and Edit the Sheet where you wish to add a Button to trigger an On-Demand report. 
7. Under 'Custom Objects' look for 'qlik-blends' from the Extensions menu and drag this into your Sheet.
8. Under the 'Blend' properties to the right, add-in your POST webhook URL and Token as noted in Step 5.
9. We will now add three measures to the 'qlik-blends' object. It is important you add them in the order described.  Add the first measure, using the following function in the expression editor: GetCurrentSelections()
10. Add the second measure, using the following function DocumentName()
11. The final measure will be the Object ID of the table you wish to use. To find the Object ID, select 'Done Editing'.  Then right click on the table, select 'Share', select 'Embed', then look for the Object ID under the preview. Copy this value, go back into Editing mode and paste this as your third measure value. 
12. With the 'qlik-blends' object selected, under Form select 'Add items'. For 'Item type' select Text. Under default value you can choose to add a default email address. For 'Label' and 'Reference'  type 'Email'. It is critical that Reference is updated to 'Email'.  Turn 'Required input' on. 
13. You can change the Appearance properties to suit your preferences, such as updating the Button label and message, enabling Dialog, and changing the Color under Theme.
14. Back in the Automation, under the Start Block. Set  'Run asynchronously' = yes to allow multiple requests to run at the same time (This will also increase the max run time from 1min to 60min)
15. Once happy, test the On-Demand report by entering an email and clicking the button.

This On-Demand Report Automation can be used across multiple apps and tables.  Simply copy the extension object between apps & sheets, and update the Object ID (Measure 3) for each instance. 

Environment

• Qlik Application Automation 
• Qlik Cloud 
• Microsoft Office 365

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.

[didierodayo]

@MattGrayndler 

I have palyed with the users island table to be able to retrieve the User generation the report but it is still replacing it with the user who created the automation.

Question:

Is it possible to save the Excel file with the name of the user that initiated the Automation? which bloacks need to come into place to get this information?

[MattGrayndler]

Hi @didierodayo 

When an Automation is set to triggered, anybody with access to the GET or POST URL with the Execution Token is able to execute the Automation.  There's no concept of 'who' triggered the Automation within Application Automation. 

The only way to get this detail is to pass this information to the Automation, which is possible via either an Extension or Selections in an App

[jesus_gomez]

Hi @MattGrayndler it's a great job!!

Trying to make other reports I wondered how can we add a new excel sheet to the report.

For example, if we want to set a cycled report per region, where each sheet is a region. I tried with Add Worksheet block, but the error given when it creates a new table (create excel table with headers 2) in the new sheet is : "

error:

Error calling endpoint "Microsoft Excel - Update Table Name".

I tried with different names and the same name, but the error remains.

Could you show us how we can do this?

Thnaks in advance

[MattGrayndler]

Hi @jesus_gomez ,

This is certainly possible but is a more complex use-case. 

The error is because each table in Excel needs to have a unique name, so you need to update 'Create Excel Table With Headers' to ensure that a unique table name is being created each time. I think i just wrote 'Report' so you should append with your dimension value. 

You also need to make sure you clear the rowdata variable at the end of each loop. 

I have created an example see below:
Scheduled Excel Report - Dimension Cycle.zip

Because we don't have the ability to duplicate a worksheet, there is no formatting capability with this method (header etc).  If you want something more formatted and your dimension values are fixed then you should create the Worksheets in advance in the template.

[jesus_gomez]

Hi @MattGrayndler 

this definitively works!! Now we are able to attach the report to the mail also

Thank you very very very much!

[JCAMAIN]

Weird, I get the following issue when clicking on the link in the email:  

[DenisSinai]

Hi,

Under Create Share link check what is your expiration date.

It should be in the future.

[Alejandro-juarez22]

HI @MattGrayndler

This solution is great, where I can find documentation on the use of blocks in detail, I need to make a filter and not send the raw table

😃

[MattGrayndler]

Hi @Alejandro-juarez22 

It sounds like you just need to add a 'Select Field Value' block before the 'Get Straight Table  Data' one. 

Information about how to use the Connectors within Qlik Application Automation are found within this knowledgebase, specifically for Qlik Cloud:
https://community.qlik.com/t5/Knowledge/Qlik-Cloud-getting-started-with-the-Qlik-Cloud-connector-in/...

I also recommend the video playlist:
https://www.youtube.com/playlist?list=PLkRFvjyuHFvIcy-KzdA4y6CIF8n34P4OO

[Kayleigh_Milewski]

I am still having trouble with the automation when there are quotation marks in my data. I saw mentioned in the comments that this was resolved. Where can I find the solution? Is it an updated json file?