Using levels and pages to loop and cycle report elements

Automatically generate new sections or worksheets into your report using level and page tags. Levels and pages are added as objects to report templates developed in the Qlik add-in for Microsoft Excel. With levels and pages, a new filtered section or worksheet is generated for each unique value in a field (or row-level combination in a chart).

Levels and pages provide an additional layer of data filtering to reports, on top of the report filters you can apply in your Qlik Sense report tasks. You can use a combination of levels, pages, and report filters to generate highly customized and individualized report output. For information on report filters, see Working with report filters.

Click the icon on the top toolbar of the add-in to open the Levels and pages tab. This is where you add, modify, and delete level and page objects.

Levels

Levels cycle report elements through all the distinct values of a field or the rows of a straight table. The results are displayed in the order of the field elements or of the rows of the table used to create the levels.

Use levels to create sections in your report, filtering your data by dimension values or by row-level chart aggregations. Within a level, you add the tabular data and chart images that want to view separate filtering for.

The following are examples of how you can use levels in the template:

• Create a level for a Year, YearMonth, or YearQuarter dimension. Within the level, nest tabular data with dimensions and measures like Product Group, Store Number, and Sales.

• For a report detailing support ticket progress, create a level for an Incident Status dimension. Within the level, nest tabular data with dimensions and measures like Ticket ID, Assignee, Ticket Opening Date, Ticket Closing Date, and Time Spent to Close.

Adding a level

Do the following:

1. Click the icon on the top toolbar of the add-in. A list of available level and page objects appears.

2. Click Add level.

   

After you complete the steps above, you can choose to create level from a field or a chart. See below for more information.

Revisit any level object 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 Levels and pages landing page, and click to navigate to the cell where it is inserted. Adjust any settings as needed in the add-in.

Using a field as a level

Do the following:

1. After creating a level using the above instructions, a list of areas from which you can create the level appears. Expand the Fields section.

2. Click the icon to the right of the field you want to use as a level.

   In this example, the level tag will create a new section for each unique City value in the app.

   

3. After you select a field, a list of field tags appears under Select fields to add (optional). If you choose to include the field tag, it will be inserted between the opening and closing level tags in the template. In the generated report, the field tag will display the field value that each level section applies to. For more information about field tags, see Field tags.

4. Click Add. The level tags are added to the currently selected cell in the workbook. Any field tags are inserted between the level tags.

   

5. After the level and field tags are inserted, move them around as needed and begin adding tabular data and chart images inside the level.

   

The report created from the template above might look like the following:

The rows with the opening and closing level tags are deleted from the generated report. For example, if you place text inside other cells of a row containing a level opening tag, that text will not appear in the generated report.

Using a chart as a level - practical example

Follow the same steps to use a chart as a level as you do to use a field as a level. When the list of available sections appears, you can expand the section for the sheet in which the chart appears, and click the icon to begin configuring the level tags.

When you add a chart as a level, the data is separated into a section for each row-level combination of values. For example, if your level is a chart with one dimension and one calculated measure, a section will be created for each dimension value, with the measure value treated as the single possible unique value for that particular dimension. The images below illustrate this.

Let's say you create the report template below. In this template, we have a level for a Sales Per Region chart. This chart has two fields (Region and Sales), which are also inserted as field tags. Inside this level, we added three columns of tabular data.

This template will result in the truncated report shown in the image below. Note that a separate section is created for each dimension value in the source chart (for example, USA and UK), each of which also has a single unique corresponding measure value.

Nesting levels

You can also create complex hierarchies by nesting levels within levels.

For example, let's say you nest a level by Country within a level by Year. Within the Country level, let's say you insert one chart image. In this case, the number of images generated in the report would be the distinct number of years, multiplied by the distinct number of countries. This example illustrates that without filters, you could easily exceed the limits of the cloud when nesting report elements inside one another.

Refer to Limitations for tabular reporting for a list of the limits that apply to using levels and pages.

Pages

You can create different filtered Excel worksheets using pages.

Adding a page tag to a worksheet name will generate a separate, filtered page for each unique entity value linked to it. Data in each worksheet are filtered by the related value of the field or the table row. You can create a page using the following items:

• Field: If you use a field as a page, you will have a worksheet for each distinct value in the field.

• Straight table: You can also use a straight table as a page. The straight table must contain only a single dimension, with no calculated conditions or measures. If you have a chart in your app which meets these requirements, it will be available under Charts when you are adding the page.

Data in each worksheet are filtered by the related value of the field or the table row.

Adding a page

You can use fields as pages.

Do the following:

1. Click the icon on the top toolbar of the add-in. A list of available level and page objects appears.

2. Click Add page.

   

3. Expand Fields, and click the icon to the right of the field you want to use as a page. Alternatively, if your app has a compatible, expand the section for the sheet on which it appears.

   In this example, the level tag will create a new section for each unique Manager value in the app.

   

4. After you select a field, a list of field tags appears under Select fields to add (optional). If you choose to include the field tag, it will be inserted in the currently selected cell in the worksheet. In the generated report, the field tag will appear on every page generated by the page tag. It will display the field value that the page applies to. For more information about field tags, see Field tags.

5. Click Add. The page tag is added to the name of the worksheet. Any field tags are added as well, if you have included them.

   

With some adjustments and the addition of some tabular data columns, the report output might look like the following image. A new worksheet is generated for each manager.

Revisit any page object 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 Levels and pages landing page, and click to navigate to the worksheet where it is inserted. Adjust any settings as needed in the add-in.

Field tags

A field tag is a unique tag that can be added inside a level tag, or in a cell on a worksheet defined with a page tag. Field tags are not objects. Instead, they are elements within level and page objects, and they are optional.

For each field you are adding as the level or page, a field tag can optionally be added. The field tag displays the field value for each discrete entity on which the level or page s looping the data generation. Within a level or page, a field tag often functions as a section header, but it still contains tabular data.

Access the fields and field tags for a level or page by selecting the cell containing the level or page, and looking under Fields in the object's settings.

Field tags can be removed by deleting or clearing the cell in which they have been added. Add a field tag back by clicking Add field tag under Fields in the object's settings.

A field tag can be set to Keep source formats, or this setting can be turned off to apply custom formatting and styling to the cell using the native Excel features.

Removing a level or page

Like with any other object, you can remove a level or page 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 and the object from the corresponding object list.

If you instead use Excel features to delete a level or page, 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.