Adding content to your Excel report template

You can add various forms of content to your Microsoft Excel report template. Add content as chart image placeholders, tabular data tags, cells formatted with native Excel features, and other formats. Content added using the add-in interface takes the form of objects. These objects can be added, managed, and re-used in the add-in window.

For information about getting started with the add-in, such as activating, opening, and connecting to app content, see Getting started with using the Qlik add-in for Microsoft Excel.

What can you add to a report template?

Qlik Excel report templates work with the following types of content:

• Tabular data. With this form of content, text and numerical data values are populated directly into the cells of the report. Tabular data is added using tags. You can add the following tabular data:

  Tabular data. With this form of content, text and numerical data values are populated directly into the cells of the Excel report. Tabular data is added using tags. You can add the following tabular data:

  • An entire chart added with a single table tag. See Adding an entire chart as table.

  • A table consisting of one or more individual columns from a chart. See Adding a chart as table by column.

  • A calculated column defined within a native Excel table. See Adding calculated columns.

  • A calculated total defined within a native Excel table. See Adding calculated totals.

  • A native Excel PivotTable. See Creating native Excel pivot tables.

  • Variables. See Using variables and expressions in your Excel report template.

  • Expressions. See Using variables and expressions in your Excel report template.

  For more information about the available tags you can add, see The different types of tags.

• Chart images are added as placeholder images. The placeholders are replaced with current views of the Qlik Sense app data from the latest reload. For more information, see Adding charts as images.

• Certain text content not tied to the Qlik Sense app or the objects in the template. This can include manually entered text, such as titles and headers. Calculated cells can be placed outside the tag structure, allowing custom formulas to be applied to content contained within the tags. Keep in mind that unpredictable behavior can occur if you insert manual text incorrectly in the template.

Use levels and pages to automate the creation of separate sections and pages in your report, looping and repeating the generation of tabular and image data for a distinct number of dimension values. For information, see Using levels and pages to loop and cycle report elements.

Object types

Objects are the primary form of content added to a template. Objects can be created, edited, and deleted within the add-in. You can add more than one of the same object to a template. You can create the following objects:

• Chart images

• Chart tables

• Variables

• Expressions

• Levels

• Pages

The different types of tags

The following table lists the available types of tags you can insert into the template, and what each tag does. Use the Qlik add-in window in Excel to add the tags.

Adding charts as images

You can add a chart as an image to the Excel report template. When the report output is generated, an image of the chart is placed in the selected cell. The chart will use the data from the app's most recent reload.

A chart image is added as a placeholder image, representing a snapshot of what the chart looks like in the template creator's current session. This might include selections made in the app. It is important to remember that the selections in the chart snapshot are not included in the report output. Data reduction is instead controlled by report filters. For more information, see App selections and Working with report filters.

Do the following:

1. Click the icon on the top toolbar of the add-in.

2. Click Add chart. A list of available sheets appears. Expand the sheet containing the chart you want to add, and click the icon to add it.

   

3. Expand the sheet containing the chart you want to add. You can search the chart's name to refine the list of sheets if needed.

4. Click the icon to add the chart.

   You can preview the chart directly in the add-in before adding it by clicking the chart's name.

   

5. The chart is added, and the settings page for the image appears. It is recommended to provide a unique Name to the table so you can identify it in the add-in if you later need to change its settings.

   Change the size of the image as you normally do with Excel images. Use the Zoom setting in the add-in as needed.

   

Revisit any chart you have added at any point for modifications to its configuration, to locate it in the workbook, or to add another instance of it to the workbook. Click the icon in the add-in to open the Charts landing page, and click to navigate to the cell where it is inserted. Adjust any settings as needed in the add-in.

Adding charts as straight table tabular data

A chart can be added in tabular Excel form, either as an entire table or as one or more individual columns. Whether you add a chart as a single table tag or as single column tags, the underlying object you create is a table.

Charts with straight type hypercubes can be exported as tabular data.

When you add a chart as a table, the underlying fields representing the chart's data are added as columns to the report.

Click the icon on the top toolbar of the add-in. The Tables section appears.

When selecting a chart, refresh the list of available tables from the Qlik Sense app by clicking .

Adding an entire chart as table

You can add an entire chart in tabular form with a single tag. The chart data is inserted in the report as a straight table.

Do the following:

1. With the Tables section of the add-in opened, click Add table.

   This shows the list of objects that can be added as a table.

2. Click the icon next to the chart you want to add.

   You can preview the chart directly in the add-in before adding it by clicking the chart's name.

3. In the drop down menu, select Table. This inserts the Qlik Sense entity as a single table tag into the selected cell.

   

4. The settings page for the table appears. It is recommended to provide a unique Name to the table so you can identify it in the add-in if you later need to change its settings.

5. Adjust any other parameters as needed.

Revisit any table you have added at any point for modifications to its configuration, to locate it in the workbook, or to add another instance of it to the workbook. Click the icon in the add-in to open the Tables landing page, and click to navigate to the cell where it is inserted. Adjust any settings as needed in the add-in.

Adding a chart as table by column

A chart can also be added in tabular form as a set of single columns. This allows increased control over formatting within the report template as well, with the native Excel text formatting options.

The chart data is inserted in the report as a straight table.

Do the following:

1. With the Tables section of the add-in opened, click Add table.

   This shows the list of objects that can be added as a table.

2. Click the icon next to the chart you want to add one or columns from.

   You can preview the chart directly in the add-in before adding it by clicking the chart's name.

3. In the drop down menu, select Individual columns. The list of available columns appears.

   

4. Select the columns you want to add, or click All to insert all columns from the chart.

5. Click Add. The columns are inserted into the template at the selected cell.

   

6. The settings page for the table appears. It is recommended to provide a unique Name to the table so you can identify it in the add-in if you later need to change its settings.

7. Adjust any other parameters as needed.

Each column selected is added to the template as a single column tag. The set of columns is still considered a single object.

Header cells with the field names are automatically inserted above each column tag. This allows you to customize the headers separately. These headers can be removed if you prefer to use the header formatting options within the add-in. See Header settings at the column level.

Revisit any table you have added at any point for modifications to its configuration, to locate it in the workbook, or to add another instance of it to the workbook. Click the icon in the add-in to open the Tables landing page, and click to navigate to the cell where it is inserted. Adjust any settings as needed in the add-in.

Adding more columns from a chart

After adding some columns of a table individually, you can add more at any time.

Do the following:

1. Select the column tag of the column directly to left of where you need to add another column.

2. In the add-in, the chart table settings menu appears.

3. Under Columns, click the column you want to add to expand it.

4. Click Add column tag.

   The tag of the selected column is added to the right of the selected cell.

Adjust any parameters as needed.

Adding more columns from a chart

Into a chart added by column

After adding some columns of a table individually, you can add more at any time.

Do the following:

1. Select the column tag of the column directly to the left of where you need to add another column.

   In the add-in, the chart table settings menu appears.

2. Under Columns, click the column you want to add to expand it.

3. Click Add column tag.

   The tag of the selected column is added to the right of the selected cell.

Adjust any parameters as needed.

From a chart added as entire table

You can also add more columns to the template from a chart that was added as an entire table. When you do this with a column, the new column you add becomes a new, separate table object with a single column. You can then add more columns that object.

Do the following:

1. Select the cell containing the table tag.

   In the add-in, the chart table settings menu appears.

2. Click Expand columns.

3. Select a cell outside of where the table tag's columns will end.

4. Click Add column tag.

Tabular data – Table formatting properties

Every table added as a single tag has three table settings enabled by default:

• Keep source formats: The Excel add-in tries to keep the original object formats. By turning this setting off, you can apply customized Excel formats to the table. For example, you can set a background color in the cell with the table tag and it will be propagated on all cells in the generated report.

• Expand range: The Excel add-in will add an adequate number of rows under the table to avoid overlap with table tags placed underneath. If the Expand range option is cleared, the add-in will not add rows under the table. You will have to manually manage the position of other table tags to stop related cells from being overwritten. Cell references will be kept.

• Wrap text: All text in table cells will be wrapped.

The Header configuration is also available to configure the table headers.

Each of these settings is explained in more detail below.

Keep source formats setting at the table level

By default, your generated reports are set to use the same formatting as the source Qlik Sense objects. If you manually apply custom formats to the template, they will be ignored. To add custom styling, you must turn off Keep source formats.

The image below is an example of the output generated from a single table tag highlighted in yellow, with Keep source formats turned off.

Expand range

If the number of rows and columns in a table is fixed and known during the development of the template, you can add Excel formulas to the table. However, if you keep the Expand range box checked, the generated report will be wrong. It will contain incorrect cell references and the formulas will not apply to the correct cells.

In the example below, we have added some calculated cells outside the cells to be populated with the Qlik Sense table columns. Additionally, calculated cells are placed at the bottom of three columns in the table. We leave the Expand range setting turned on.

However, in the report generated from this template, the cell references are not respected, and the calculated cells are instead added below the table. See the image below.

If we turn the Expand range setting off and generate the report, we can see that the cell references are now kept and function as intended. This is shown in the image below.

Wrap text

When Wrap text is turned on at the table level, every column in the table will have text wrapping automatically applied to it. Turn off this setting if this is not desired.

Header settings at the table level

You have three options for configuring headers at the table level:

• Show in tag cell (default): Table headers will appear in the same row as the table tag, and table data will start on the row directly below it.

• Hide: Headers are removed entirely. Table data will start on the same row as the table tag. If you use this option and keep header manually entered above the tags, you can customize both the header text and the formatting of the header cells.

• Show above tag cell: Headers are moved to the row above the table tag. Table data starts on the same row as the table tag. You can apply custom Excel formatting to the cell above the table tag, so that the header cells are styled to your preference. The text in the header cells themselves, however, is generated with the dimension and measure titles from the Qlik Sense app.

Tabular data – Column formatting properties

If you add a table as columns, or if you do not add all the columns of the source table, the table properties function the same as if you added an entire table tag.

The Expand range and Wrap text settings affect the whole table. Keep source formats and Header can be applied at the column level.

Keep source formats can be set at the column level to customize the formats of every single column. Click on the tag of a column and uncheck the related Keep source formats checkbox.

Keep source formats at the column level

Keep source formats can be set at the column level to customize the formats of every single column. Click on a column tag and uncheck the related Keep source formats checkbox. Alternatively, open the table in the Tables objects within the add-in, and expand the column. Then, uncheck the checkbox.

Checking, or unchecking, the Keep source formats checkbox at the table level will change the value of this setting for all individual columns in the table. For information about setting this at the table level, see Keep source formats setting at the table level.

Header settings at the column level

It is also possible to set the Header style at the column level. The same options for setting headers at the table level are available for setting them at the column level. See Header settings at the table level.

When you add a table by individual columns, a fixed string is used as the header text by default. This can be removed, or overwritten, using the available column-level Header options. You can also modify the fixed string to display custom header text for a column.

Customize the fixed string in a column and delete it from another. Using the Header drop-down menu, select Show above tag cell.

Changing the Header setting at the table level will change the value of this setting for all individual columns in the table.

Removing content

Removing an object

You can remove an object from the report template, and from the list of that object type in the add-in pane.

Do the following:

1. Select the cell where the object is added.

2. Click Delete in the bottom of the add-in window.

This removes the tags or the preview image and the object from the corresponding object list.

If you instead use Excel features to delete an object, it will not be inserted in the generated report. However, the object will still appear in the list of added objects. This means you can easily re-add the object later.

Removing table columns

For chart data added by column as a table, you can delete columns from the template. Navigate to the cells of the column you want to remove, highlight all its components, and delete or clear the cell contents.

Removing columns from the template does not remove the columns from the table object, so you can use the add-in to add back any columns as needed.

App selections

Selections you make in the Qlik Sense source app are reflected in the report template while authoring it. The selections are applied to the preview of the report you can generate from the add-in. However, the selections are not reflected in output from report tasks in Qlik Cloud. Instead, data reduction in the report output is controlled by filters you create in the Reporting section of a Qlik Sense app. See Working with report filters for more information.

If you do want to change the selections reflected in the template for the purposes of previews and image placeholders, you can change the selections in the original Qlik Sense app, and then refresh the report template. To see the new selections in Excel, click the icon in the top right corner of the properties pane for the object. This refreshes the selections only in the selected object.