Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER

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

100% helpful (2/2)
cancel
Showing results for 
Search instead for 
Did you mean: 
MattGrayndler
Former Employee
Former Employee

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

Last Update:

Jun 26, 2023 3:26:33 AM

Updated By:

Sonja_Bauernfeind

Created date:

Oct 15, 2021 12:34:34 AM

Attachments

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:

 

QAA_O365_Excel_Sharepoint_Report_EmailEG.pngQAA_O365_Excel_Sharepoint_Report_ReportEG.png

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

 

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 (1)
Comments
Lachlan
Contributor II
Contributor II

Loving this concept @MattGrayndler, especially as a potential SaaS replacement for some NPrinting components.

However, when creating a new Connection in Qlik Application Automation, for example to Sharepoint, is there a way to change the Prompt / Consent Level, similarly to the process within the normal Data Connections, so that we can use the select_account option?

Madolfsson
Employee
Employee

This is so great @MattGrayndler  , could you help me out, on the On demand automation, the "select field values by query" needs inputs.. can you help me to map the proper inputs there for that block? Thank you...

 

MattGrayndler
Former Employee
Former Employee

@Madolfsson When you import the Automation these fields should have values already, do they not?

Select-Fields-By-Query.png

For App ID, it's looking at the 2nd measure coming from qlik-blends.  And the other values come from an object that's created  for Selected Dimensions and Values. 

If you need to input these expressions - turn off formula parsing:
ToggleFormula.png

And input these values:

AppID: {$.InputData.data[0].qMatrix[0][1].qText}
Field Name: {$.TempObject.Dimension}
Field Value Search String: ("{replace: {$.TempObject.Value}, ', ', '"|"'}")

For the formula in the field value search string, we are replacing the values coming from Qlik like this Value1, Value 2
and turning into this format ("Value1","Value2")

MattGrayndler
Former Employee
Former Employee

@Lachlan  when I create a connection it uses the 'select_account' prompt type. 

select_Account2.png

 

If QAA needs admin approval, it will then prompt you to sign in with an Admin account. 

Please feel free to post more detail about the issue you are facing either within the Qlik Application Automation Community or through Qlik Support:

https://community.qlik.com/t5/Qlik-Application-Automation/bd-p/qlik-application-automation-discussio... 

Madolfsson
Employee
Employee

Hey @MattGrayndler  thanks for that, the following blocks did not have inputs when I imported the On demandv2-

 

 Get Straight Table Data

Select Field Values By Query

Create Excel Table With Headers

MattGrayndler
Former Employee
Former Employee

Hi @Madolfsson - Thanks for pointing this out, this certainly wasn't what I expected.

Upon testing it looks like the values for the Qlik Cloud Services blocks disappear when you move a Workspace between different Qlik Cloud regions. 

I've gone ahead and uploaded 3 versions (one for each Qlik Cloud region - AP, EU, US).  Please download the relevant one for your region and try again. 

Madolfsson
Employee
Employee

Thanks a lot @MattGrayndler for that extra work!

MattGrayndler
Former Employee
Former Employee

As an update, the issue with sharing Workspaces between tenants in different regions is now resolved. 

As a result, the On-Demand example is now again a single version attached to the document that can be used by any tenant/region. 

 

jmcy2408
Contributor III
Contributor III

HI - is there a limitation to this on no of columns? 

I've got to 27, and it fails, works at 26 columns.  

 

Error 400:

The argument is invalid or missing or has an incorrect format.

 

jmcy2408
Contributor III
Contributor III

jmcy2408_0-1643279517327.png

I found the solution - had to add extra columns to mapping table.

Version history
Last update:
‎2023-06-26 03:26 AM
Updated by: