Newly formatted Excel reports

Last December, Qlik launched a new extension to the Qlik Cloud reporting service. In this blog, Senior Business Intelligence consultant Lennaert van den Brink explains exactly what benefits it offers you and how it works.

Why generate Excel reports from Qlik?

However attractive dynamic, interactive dashboards may be, many companies still need to produce a static report periodically based on the data from a Qlik dashboard. For example, to share it with people outside the organisation, or as a report to the board for annual reports or archives. With on-premise Qlik, we had the NPrinting extension for this. In Qlik Cloud, the reporting service has been available since December 2021. Although you can generate quite attractive reports with the reporting service, it does have one major drawback: you can only include complete sheets one-to-one in the report. This means you either have to create a separate sheet for this specific purpose, or accept that your dashboard design might not come across entirely well in your static report. In addition, this approach only let you export to PowerPoint or PDF format.

In December 2023, however, Qlik launched a new extension to the reporting service. You can now also generate Excel reports based on elements from your dashboard. The big advantage is that this gives you much more creative freedom in designing the look of your static report. Also important for users who have already developed many reports in NPrinting: the technical preview version of NPrinting February 2024 includes an export feature that lets you convert existing NPrinting Excel reports to this new cloud capability. That removes another major hurdle to migrating to Qlik Cloud.

Creating templates

To generate an Excel report from Qlik Cloud, you first need to create a template. You do this using the Qlik add-in for Excel. To make the add-in available in Excel, you first need to carry out a bit of configuration in the management console of your Cloud tenant (link), after which you can download and activate the add-in in your Excel application (available for both desktop and Office365).

When you activate the add-in in Excel, you will be asked to sign in (if you have configured single sign-on, this happens automatically). After signing in, you can specify in the add-in which app you want to pull the data from. Choose the Space and app you want and press Save.

If you have not set a title for an object after all, you can view a preview of the object by clicking the arrow next to its name. You will then also see the option to open the visualisation in the Qlik Sense app. Once you have identified an object, click the plus sign to add it to the Excel sheet. In the case of a table, you can then choose whether to insert the whole table in one go or specify per column which ones to include. When you add a chart, it will appear on your worksheet as an image. You can then build your report the way you are used to in Excel, by dragging the elements to the right cells. While developing, you can download a preview via the add-in to see what the result looks like when it is filled with data.

Distributing the report

Once you are happy with your template, it is time to add it to the app in Qlik Cloud. Navigate to the app and then, under “Prepare”, choose “Reporting”. You will see that there are a number of steps to go through. The first step is adding recipients for your report. When you click Recipients, you can choose whether to connect to a data source or upload an Excel file. You can also download a template here in which you can fill in the names, email addresses, filters and groups for the recipients, and then upload it back to Qlik. You define the filters in the interface under the “Filters” tab. Here you can specify a combination of selections that are applied when the report is generated. This lets you reuse one report template for different groups of recipients.

Next, you also upload the report template you have developed. Once you have done all this, 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.

Next, you configure the output. You specify the name of the file to be generated and choose the date format that is appended to it (each file gets a timestamp of when it was generated). If you wish, you can also apply an additional filter to the report at this level.

Under recipients, you specify which of the recipients or groups should be emailed by this task. You can also design the subject and body of your email here. You can also choose to save the generated reports to a network location, for example as an archive so you can see which reports have been generated.

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

Tips and tricks

Excel reports are available to everyone with a Qlik Cloud tenant. If you want to get started with Excel reporting yourself, here are some tips to help you on your way:

• Preview your template regularly. Even though we have all been working with Excel for a very long time, it can still behave unpredictably at times. A preview quickly shows you whether everything comes out right or whether you need to do something slightly differently.
• When creating the visualisations in your app, always add titles to your objects, even if you ultimately don't show them in the app. This makes finding the right objects in the Excel add-in a lot easier.
• When you load an object into Excel as a table, you can turn off the “Keep Source formats” setting in the add-in. Turning it off lets you format the cells in Excel exactly as you want, for example with the right number formatting.
• You can also use the Excel reporting functionality simply to put the data from an object into a table on a hidden tab. Then use Excel's features to create, for example, a pivot table or perhaps even complete visualisations. This is especially handy for quickly moving existing Excel reports into your managed Qlik environment!