Generating formatted Excel exports with Qlik Cloud

Last December, Qlik launched a new extension for the reporting service of Qlik Cloud. In this blog, I explain the benefits it offers and how everything works. Keep reading for some practical tips at the end of the blog!

Why Generate Excel Reports from Qlik?

Despite the attractiveness of dynamic and interactive dashboards, many companies still have a need to periodically create a static report based on data from a Qlik dashboard. This could be for sharing with individuals outside the organization, monthtly / annual reports aimed at upper management management or to archive. In the case of on-premise Qlik, we used the NPrinting extension for this purpose. In Qlik Cloud, since December 2021, there has been the reporting service. However, the reporting service has a significant limitation: it allows you to include only complete sheets in the report, meaning you either have to create a separate sheet for this specific purpose or accept that your dashboard design may not look perfect in the static report. Additionally, with the reporting service, you could only export to PowerPoint or PDF formats.

However, in December 2023, Qlik introduced a new extension for the reporting service. Now, you can generate Excel reports based on elements from your dashboard. The significant advantage is that you have much more creative freedom to design the appearance of your static report. Importantly for users who have developed many reports in NPrinting, the technical preview version of NPrinting in February 2024 includes an export functionality to convert existing Excel reports from NPrinting to this new cloud capability. This eliminates a significant hurdle for migrating to the Qlik Cloud.

Creating Templates

To generate an Excel report from Qlik Cloud, you first need to create a template using the Qlik add-in for Excel. To make the add-in available for Excel, you must perform some configuration in the management console of your Cloud tenant (link). Afterward, you can download and activate the add-in in your Excel application (available for both desktop and Office365).

When activating the add-in in Excel, you will be prompted to log in (if you have configured single sign-on, this process will be automatic). After logging in, you can specify from which app you want to retrieve the data in the add-in. Choose the desired Space and app, and click Save.

Now, you can start developing your report. For instance, if you want to populate an Excel table with data, choose "Tables" and then "Add table." Next, you will see the names of the sheets in the app. By clicking the arrow next to the sheet's name, you can expand it and see all available visualizations. The name of the visualization corresponds to the title you gave to the object. If you haven't set a title, you will see a code consisting of seemingly random uppercase and lowercase letters. It's advisable always to fill in the titles when creating a dashboard, even if you hide them in the visualization in the app.

Suppose you haven't set a title for an object. In that case, you can preview the object by clicking the arrow next to the object's name. You will also see the option to open the visualization in the Qlik Sense app. Once you've identified an object, click the plus sign to add it to the Excel sheet. In the case of a table, you can choose whether to insert the entire table at once or specify which columns to include. When you add a Chart, it will appear as an image on your worksheet. You can then build your report in Excel by dragging the elements to the correct cells. While developing, you can download a preview via the add-in to see how the result will look when filled with data.

Distributing the Report

Once you are satisfied with your template, it's time to add it to the app in Qlik Cloud. Navigate to the app and choose "Prepare," then select "Reporting." You will see that several steps need to be completed. The first step is to add recipients for your report. Clicking on Recipients allows you to choose whether to connect with a data source or upload an Excel. Here, you can also download a template where you can fill in the names, email addresses, filters, and groups for the recipients and then upload it back to Qlik. Define the filters in the interface under the "Filters" tab. You can specify a combination of selections to be applied when generating the report. This way, you can reuse one report template for different groups of recipients.

Next, upload the report template you developed. After completing these steps, you can create the reporting task. Give the reporting task a suitable name and description, and choose the template you want to use for sending.

Then, configure the output. Specify the name of the generated file and choose the date format to be added to it (each file receives a generation timestamp). At this level, you can also apply a filter to the report if necessary.

For recipients, indicate which of the recipients or groups should be emailed by this task. You can also design the subject and body of your email here. Additionally, you can choose to save the generated reports in a network location, such as an archive, to track which reports have been generated.

Finally, specify when the report should be sent. It can be sent every time the report is refreshed or at specific intervals (daily, weekly, monthly) and times. You can also specify the period for which the report should be sent.

Tips and Tricks

Excel reports are available to anyone with a Qlik Cloud tenant. If you want to start using Excel reporting yourself, here are some tips to get you started:

• Regularly preview your template. Despite our extensive experience with Excel, it can still behave unpredictably. With a preview, you can quickly see if everything looks good or if you need to do something slightly different.
• When creating visualizations in your app, always add titles to your objects, even if you ultimately don't display them in the app. This makes it much easier to find the correct objects in the Excel add-in.
• When loading an object as a table into your Excel, you can turn off the "Keep Source formats" setting in the add-in. By turning this off, you can then format the cells exactly as you want in Excel, for example, with the correct number formatting.
• You can use the Excel reporting functionality to put only the data from an object into a table on a hidden tab. Then use Excel functionalities to create, for example, a pivot table or even entire visualizations. This is especially useful for quickly transferring existing Excel reports to your managed Qlik environment!