7 min read
Newly Formatted Excel Reports
Last December, Qlik launched a new extension for the Qlik Cloud reporting service. In this blog, Senior Business Intelligence Consultant Lennaert van den Brink explains exactly what benefits this offers and how it works.
Why generate Excel reports from Qlik?
However beautiful dynamic and interactive dashboards can be, many companies still have a need to periodically create a static report based on the data from a Qlik dashboard. For example, to share it with people outside the organization, or as a report for management for annual reports or archives. With on-premise Qlik, we had the NPrinting extension for this. In Qlik Cloud, there has been the reporting service since December 2021. Although you can generate pretty nice reports with the reporting service, it does have one important drawback: you can only include complete sheets in the report on a 1-to-1 basis. That means you either have to create a separate sheet for this specific purpose, or accept that your dashboard design might not come across quite as nicely in your static report. In addition, this method only allowed you to export to PowerPoint or PDF format.
In December 2023, however, Qlik launched a new extension for 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 to put together the look of your static report. Also not unimportant for users who have already developed many reports in NPrinting: the technical preview version of NPrinting from February 2024 includes an export feature that lets you convert your existing NPrinting Excel reports to this new cloud option. This removes another important hurdle to migrating to Qlik Cloud.
Creating templates
When activating the add-in in Excel, you'll be asked to log in (if you've configured single sign-on, this happens automatically). After logging in, you can indicate in the add-in which app you want to retrieve the data from. Choose the desired Space and app, and click Save.
If you still haven't set a title for an object, you can view a preview of the object by clicking the arrow next to the object's name to expand it. You'll also see the option to open the visualization in the Qlik Sense app. Once you've identified an object, click the plus icon to add it to the Excel sheet. In the case of a table, you can then also choose whether you want to insert the entire table at once, or specify per column which ones to include. When you add a chart, it will be added to your worksheet as an image. You can then build your report the way you're 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 once it's filled with data.
Distributing the report
Once you're happy with your template, it's time to add it to the app in Qlik Cloud. Navigate to the app and then choose "Reporting" under "Prepare." You'll 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, where you can fill in the names, email addresses, filters, and groups for the recipients before uploading it back to Qlik. You define the filters in the interface under the "Filters" tab. Here you can specify a combination of selections that will be applied when generating the report. This way, you can reuse one report template for different groups of recipients.
Next, you also upload the report template you developed. Once you've 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 that will be generated and choose the date format that's added to it (each file gets a generation timestamp). Optionally, you can also apply a filter to the report at this level.
Under recipients, you 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. You can also choose to save the generated reports in 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 moments (daily, weekly, monthly) and times. You can also specify the period the report should cover when sent.
Tips and tricks
Excel reports are available to everyone with a Qlik Cloud tenant. If you'd like to get started with Excel reporting yourself, here are a few tips to help you on your way:
- Regularly preview your template. Even though we've all been working with Excel for a long time, it can still behave unpredictably at times. A preview quickly shows you whether everything looks right, or whether you need to make a small adjustment.
- When creating the 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 right objects in the Excel add-in.
- When you load an object as a table into your Excel, you can turn off the "Keep Source formats" setting in the add-in. Turning this off lets you then format the cells in Excel exactly as you want, for example with the correct number formatting.
- You can also use the Excel reporting functionality to just place the data from an object in a table on a hidden tab. Then use Excel's own functionality to create, for example, a pivot table or even entire visualizations. This is especially useful for quickly moving existing Excel reports over to your managed Qlik environment!
Written by Lennaert van den Brink
Senior Consultant