Creating Excel reports

In this tutorial, you will create a new Excel report template with two tables and an image. You will use the Page feature to produce a new worksheet for each value of a field.

What you will do

You will:

1. Create a new Excel report template.
2. Add a table object.
3. Create a custom table column by column.
4. Add an object as an image.
5. Use the Page feature to generate a new worksheet for each different sales office.

This tutorial uses QlikView data that can be found in Sample files. You can also use your own QlikView or Qlik Sense data.

Creating a new Excel report template

Do the following:

1. Select Reports in the Qlik NPrinting main menu, and then click Create report.
2. Enter a Title for the report.
3. Select Excel from the Type drop-down list.
4. Select an app from the App drop-down list.

5. Select a Template from the options available:

   • Empty template - uses an empty template

   • Default template - use the default template (only available if a default template has been set in Report settings.

     See Load an Office, HTML or PixelPerfect file as a template

   • Custom - Choose a file to use as a template.
6. Leave the Enabled check box selected. If you clear it, the report will be saved, but ignored by the scheduler.
7. Click Create to create the report.
8. Click Edit template to open the Template Editor.

Adding a table

Do the following:

1. Right-click the Tables node, and select Add objects.
2. Select Sales by Country from the objects list. Click OK.

3. Drag and drop the CH304 - Sales by Country tag onto an empty cell.

   

Customize the formatting on an entire table

This causes the contents of all cells in all columns of the table to be exported from QlikView or Qlik Sense without formatting. You can apply new formatting using the Excel ribbon.

Do the following:

1. On the left-hand pane, select the table you want to customize.
2. Go to the Properties pane.

3. Clear the Keep Source Formats check box.

   

4. Use the Excel ribbon to customize the table.

   For example, change font size or color.

Adding a table column by column

You can create a custom table by adding columns individually. They do not have to be in the same order as the original Qlik Sense or QlikView table.

Do the following:

1. Expand the CH304 - Sales by Country node.

   Information noteYou can only expand the node to reveal column nodes if the object is a straight table or table box. If you do not see the +, you added a pivot table or a straight table with calculated columns.

2. Drag and drop the column tags into cells one at a time.

   This creates a tags for each selected column with its title as a text cell that you can format.

   You can move the tags in the Excel template to obtain the column order you prefer.

Customize formatting on specific columns in a table

If you want to keep the source formatting for the majority of columns, leave the Keep Source Formats box selected for the table as a whole. You can disable Keep Source Formats on individual columns. This causes the content of all cells in the selected column to be exported from QlikView or Qlik Sense without formatting. You can apply formatting using the Excel ribbon.

Do the following:

1. Expand the table node by clicking the + on the left.

2. Select the column that you want to customize.
3. In the Properties pane, clear the Keep Source Formats check box.

4. Select the column in the template, and then apply formatting as desired.

   

Adding an image

Do the following:

1. Right-click the Images node, and select Add objects.
2. Select Sales by Country from the objects list. Click OK.

3. Drag and drop the CH304_1 - Sales by Country tags onto any cell below the tables.

   

Previewing the report

Do the following:

1. Click Preview.

   Excel launches and displays your report.

2. You will see an Excel report with one sheet. That sheet will have the same Qlik object as an image, a table, and a table added column by column.

   

3. Close the preview window.

Applying Pages

The Pages node lets you produce a report with a separate worksheet for each value of a field. For example, a different worksheet for each sales office.

Do the following:

1. Right-click the Page node icon in the left pane.
2. Select Add page to current sheet.
3. Select SalesOffice from the list. Click OK.
4. Click the + on the left to expand the SalesOffice page node.

5. Drag the SalesOffice node tag onto a cell in the template.

   You can format the cell using Excel formatting features.

6. The worksheet name changes to <SalesOffice_Page> on the bottom tab. When the report is generated, this will be replaced with the related value of each worksheet.

   You can edit the worksheet name by adding text. For example: <SalesOffice_Page> office.

   

Your report will now be produced with a different worksheet for each sales office.

Preview and save

Do the following:

1. Click Preview.

2. You will see a report with sales office as a title, two tables, and an image. There are now several worksheet tabs, one for each office.

   

3. Click Save and Close to save the template and close the Template Editor.