Expand/Collapse all

Example - Creating a tabular report

In this example, you will build an Excel-based report to analyze sales by product, year, and region.

Before you start

Requirements

Before you begin, ensure:

• Your Qlik Cloud subscription has capacity for the Qlik Reporting Service.

• You or your admin installs the Qlik add-in for Microsoft Excel.

  Deploying and installing Qlik add-ins for Microsoft Office

• You have activated the Qlik add-in for Microsoft Excel after it is installed.

  Activating add-ins

• You have the required permissions for working with in-app reporting.

  Qlik Reporting Service permissions

  If you cannot create or manage reports, contact your administrator and consult Setting permissions for metered reporting features.

Upload example sources

Refer to the example application sources here: Examples - In-application reporting. Upload the application and data files into a space, and then reload the application. You are ready to complete the example.

Create a new template

Get started

Do the following:

1. Open Microsoft Excel and create a new XLSX file. This will be your template file.

2. Click the Qlik add-in icon in the ribbon bar under the Home tab.

   

3. Log in to the add-in using your Qlik Cloud credentials.

   Connecting to the tenant and logging in

Connect to the application

Do the following:

1. Select the Space where you have uploaded the application.

2. Under App, select the application.

3. Click Save.

   

The template is now connected to the application.

Add a level and a page

With levels and pages, you can automate the creation of filtered worksheets, cycling report data for each dimension or chart value:

• A level creates a new section for each dimension or chart value.

• A page create a new worksheet for each dimension or chart value.

Add a level

Add a level object to generate a separate section on the same worksheet for each unique value of a dimension or chart. In this case, create a new section for each region.

Do the following:

1. In the worksheet, select cell A3 to put it in focus.

2. In the add-in, click the icon on the top toolbar.

   The Levels and pages section opens.

3. Click Add level.

4. Expand Fields.

5. Click next to Region.

   

6. Under Select fields to add (optional), check the box for Region.

   This will insert a field tag with the level tags, creating section headers for each value.

7. Click Add.

After creating the level, add 3 rows under the <Region> field tag. This is where the report data will be added.

Add a page

Add a page object to generate separate worksheets for each unique value of a dimension or chart. In this case, create a new worksheet for each year.

Do the following:

1. In the add-in, click the icon on the top toolbar.

   The Levels and pages section opens.

2. Click Add page.

3. Expand Fields.

4. Click next to Date.autoCalendar.Year.

   

5. Under Select fields to add (optional), leave Date.autoCalendar.Year unchecked.

6. Click Add.

The worksheet title is updated with the name of the field used for the page object.

Add a chart image

Now that you have defined a level and page, add a chart image.

Do the following:

1. In the worksheet, select cell A5 to put it in focus.

2. In the add-in, click the icon on the top toolbar.

   The Charts section opens.

3. Click Add chart.

   A list of available sheets appears.

4. Expand the Dashboard sheet.

5. Click next to the Top 5 Customers chart.

   

6. Resize the chart image to your preferences, and then resize the height of the row so it matches the size of the chart image.

Add tabular data

Next, add a table based on a chart in the source application.

Part 1: Add the source table

Do the following:

1. In the worksheet, select cell A6 to put it in focus.

2. In the add-in, click the icon on the top toolbar.

   The Tables section opens.

3. Click Add table.

   A list of available sheets appears.

4. Expand the Sales Tables sheet.

5. Click next to the Sales By Product Group chart, and select Individual columns.

   

6. Under Select columns to add, check All.

7. Click Add.

   All columns from the source chart are added as individual columns in the worksheet.

Part 2: Adjust the formatting

You can customize the table by applying native Excel formatting, such as:

• Resizing column widths

• Changing fonts, text size, and adding cell-specific styling

• Customizing number formatting

If you want to customize formatting in the table, you need to turn off Keep source formats for the table object you just created.

Do the following:

1. With the table object selected in the add-in window, uncheck Keep source formats under Table settings.

   Now you can change the formatting by applying changes to the cell contents of the table.

2. By default, the table object has fixed-text column headers inserted above it. These are not part of the table object.

   Under Table settings for the object, set Display headers to Show above tag cell. This ensures table headers are not duplicated.

3. Style the fixed-text headers by making the font size slightly larger than the tag cells.

Add a native Excel pivot table

Next, add an Excel PivotTable to summarize the application's data at a high level.

Part 1: Insert the source tabular data

Do the following:

1. Create a new worksheet and title it PivotTable source.

2. In the add-in, click the icon on the top toolbar.

   The Tables section opens.

3. Click Add table.

   A list of available sheets appears.

4. Expand the Sales Tables sheet.

5. Click next to the Sales Details 2021-2023 chart, and select Individual columns.

6. Under Select columns to add, check All.

7. Click Add.

   All columns from the source chart are added as individual columns in the worksheet.

Part 2: Convert the tabular data to an Excel table

Do the following:

1. Select the following in the worksheet:

   • Header row

   • The row with tags

   • One additional blank row

   • All desired columns

   

2. Convert the selected area to an Excel table. The exact procedure depends on your Excel version. For example, select a cell in the table, and click Insert > Table in the ribbon.

   For exact steps, refer to the official Microsoft documentation.

3. Select cell A3 in the Excel table. In the add-in window, under Tags, click Add tag: <deleterow>.

A <deleterow> tag is inserted in cell A3.

Part 3: Convert the Excel table to an Excel pivot table

Do the following:

1. Transform the Excel table you just created into a native Excel PivotTable. The exact steps may depend on your Excel version. For example, click Insert > PivotTable in the ribbon.

   For exact steps, refer to the official Microsoft documentation.

2. When prompted, select the option to add the new PivotTable on a new worksheet.

3. In the PivotTable field list, configure the pivot table as follows:

   • Rows:

     • Region

     • Product Group

     • Sales Rep Name

   • Columns:

     • Year

   • Values:

     • Sum of Sales

4. Change the labels in the pivot table according to preference.

Add a variable

You can add variables from the analytics application to the report. Variables store values that can change dynamically. You can add a variable that has been added to the application, storing today's date.

Part 1: Add the variable

Do the following:

1. In the worksheet where you added the PivotTable, rename the worksheet Summary.

2. In the worksheet, select a blank cell in the middle of row 1, about halfway across the entire content width.

3. In the add-in, click the icon on the top toolbar.

   The Variables and expressions section opens.

4. Click Add variable.

   A list of available variables appears.

5. Click next to vTodayDate .

   

Part 2: Format the variable as a banner

Do the following:

1. In the worksheet, merge and center two groups of cells in row 1, with the right group being the one that contains the variable.

2. In the cell for the left group, type the following:

    Report generated on:

3. By default, in tabular reports, dates are formatted as serial numbers. You can format the date according to your preferences. The exact procedure depends on your Excel version. An example:

   1. Select the <vTodayDate> tag in the template.

   2. Right-click the cell, and select Format cells.

   3. Select Date, and then choose the date format you would like to display.

   4. Click OK.

After adding the banner, you can customize it with native Excel formatting. For example, you can adjust text styling, add cell background colors, and add borders.

Add an expression

You can add expressions to the report with Qlik-native calculations based on the application data. For example, you can add a cell showing high-level metrics, such as total sales, without creating a table.

Part 1: Add the expression

Do the following:

1. In the worksheet, select a cell in the middle of row 2, about halfway across the entire content width.

2. Click to return to the Variables and expressions section menu.

3. Click Add expression.

4. Type a Name for the expression, for example: TotalSales.

5. Under Expression, type sum(Sales).

   

6. Click Create.

Part 2: Format the expression with a title cell

1. In the worksheet, merge and center two groups of cells in row 2, with the right group being the one that contains the expression.

2. In the cell for the left group, type the following:

    Total Sales:

3. Customize the number formatting for the TotalSales expression. The exact procedure depends on your Excel version. An example:

   1. Select the <TotalSales> expression tag in the template.

   2. Right-click the cell, and select Format cells.

   3. Select Currency, and click OK.

After adding the title and expression, you can customize it with native Excel formatting. For example, you can center their text, adjust text styling, add cell background colors, and add borders.

Preview the report

Before finishing the report, you can set formatting and styling as desired.

When ready, preview the report as an Excel file to ensure it looks as expected.

Do the following:

1. In the add-in, click the icon on the top toolbar.

   The Preview section opens.

2. Click Preview as Excel (.xlsx).