Skip to main content

Creating an Excel pivot table report using levels

You can create native Excel pivot tables in your reports.

The data source for a pivot table must be a QlikView or Qlik Sense straight table to be able to manage single columns. If you have a pivot table that you want to reproduce in an Excel report, you can do one of two things:

  • Convert the original pivot table to a straight table.
  • Clone your pivot table and convert the clone to a straight table.

Creating a new Excel report

Do the following:

  1. Select Reports in the Qlik NPrinting main menu, and then click Create report.
  2. Enter a Title for the report. Pivot table report with levels.
  3. Select Excel from the Type drop-down list.
  4. Select an app from the App drop-down list.
  5. Click Create to create the report.
  6. Click Edit template to open the Template Editor.
  7. Right-click the Levels node, and then select Add levels.
  8. Select the Connection to the QlikView document that contains the object you want.
  9. Select Straight Country - Salesman - Year - CH205 in the levels list.
  10. Click OK.

Creating a table based on level

Do the following:

  1. Drag the CH205_Level - Straight Country-Salesman-Year node onto empty template cells. This will create opening and closing tags for the level.
  2. Expand the CH205_Level - Straight Country-Salesman-Year node by clicking on the + on its left.
  3. Drag the Country, Salesman, Year, and Sales nodes onto empty cells on lines between the lines containing the <CH205_Level> and </CH205_Level> tags.
    Note: In the Properties pane, ensure that Keep Sources Formats is unchecked for all four of these objects.
  4. Provide headings for the columns in the line above the line containing the <CH205_Level> tag.
  5. Select a range of cells that includes the lines containing the headings, the <CH205_Level> tag, the column tags, and the </CH205_Level> tag.
  6. Give the range a name in the left-most field just above the template pane.
  7. Press Enter on your keyboard.

QlikView sheet objects that have calculated dimensions or null values can be used as levels. However, you cannot nest other objects inside them, except for fields from that sheet object. Qlik Sense visualizations with calculated dimensions cannot be used as levels.

Creating a pivot table

Do the following:

  1. Select Sheet2, or add it if necessary.
  2. Open the Excel Ribbon by selecting the Toolbar icon.
  3. Select the Insert tab of the Ribbon.
  4. Select PivotTable in the Tables group.
  5. Enter the name that you gave to the range that you created on Sheet1 into the Table/Range field.
  6. Click OK.

Adding fields to report template

Do the following:

  1. Drag the Country and Salesman fields into the Row Labels box.
  2. Drag the Year field into the Column Labels box.
  3. Drag the Sales field into the Values box.
  4. Select the Field Settings button in the Active Field group.
  5. Change the value in the Summarize Values By tab to Sum.
  6. Click OK.

Setting data to be refreshed

Do the following:

  1. Select the Options button in the PivotTable group.
  2. Select the Data tab in the PivotTable Options window.
  3. Make sure the Refresh data when opening the file box is checked.
  4. Make sure Number of items to retain per field is set to None.
  5. Click OK.

Preview and save

Do the following:

  1. Click Preview.
  2. Check the report, close it, and then edit the template again if necessary.
  3. Click Save and Close to save the template and close the Template Editor.