Creating Excel pivot tables

You can create Excel pivot tables using Excel table columns or levels.

Qlik Sense pivot tables can only be added as images in Qlik NPrinting. QlikView pivot tables can be added as images and straight tables. Neither can be converted into an Excel pivot table in your reports. 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 pivot table using Excel table columns

We recommend using Excel table columns to create pivot tables in Excel reports. It is easier than using levels, and does not slow down report generation.

Requirements

To create a pivot table in the Qlik NPrinting Designer, you need:

• Excel 2007 or later
• A Qlik Sense or QlikView straight table

What you will do

You will:

1. Add a QlikView straight table as a table object to an Excel report.
2. Build a table column by column.
3. Convert that table to a pivot table.

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

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.
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.

Adding a chart object as a table column by column

Do the following:

1. Right-click the Tables node, and select Add objects.
2. Select the Connection that contains the straight table.

3. Select a table object. For example: Straight Country - Salesman - Year. Click OK.

4. In the Tables node, select Straight Country - Salesman - Year.
5. In the Properties pane, clear Keep Source Formats.

6. Click the + next to Straight Country - Salesman - Year.

   This expands the table node, and displays all table columns.

7. Hold Shift or CTRL and select the columns you want to add. In this example, select all four table fields.

8. Drag the columns onto the template.

   

9. Highlight the column headings, table tags, and one empty row below.
10. On the Excel ribbon, click the Insert tab.
11. In the Tables group, click Table .

12. Make sure the My table headers box is selected. Click OK.

    

13. On the left pane, click Extras. Drag a deleterow tag onto a cell in the empty row below the table.

    This will remove the empty row from the final report.

    

Converting to a pivot table

Do the following:

1. Highlight the heading and table field cells.
2. On the Excel ribbon, under Table Tools, click the Design tab.

3. In the Tools group, click Summarize with PivotTable.

4. In the Create PivotTable window, click OK.

   

   A pivot table is created in a new worksheet. You can also add the pivot table to the same worksheet and hide the column with the data.

5. Drag the Year field into the Columns box.
6. Drag the Salesman and the Country fields into the Row Labels box.

7. Drag the Sales field into the Values box.

   

8. Click Count of Total sales to open the drop down menu. Select Value Field Settings.

   

9. On the Summarize Values By tab, select Sum. Click OK.

10. Select the cells in the table that contain Sales values. Format them as currency.

    

Ensuring data is refreshed when report is opened

If you do not perform these steps, you will see an empty pivot table in the generated report.

Do the following:

1. Under the PivotTable Tools - Analyze tab. On the left, click the Options drop down menu .
2. Select the Data tab.
3. Click the Refresh data when opening the file check box.
4. Set Number of items to retain per field to None.

5. Click OK.

   

Previewing the report

Do the following:

1. Click Preview.

   Excel launches and displays your report.

2. You will have a report with a pivot table.

   

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

Creating a pivot table using levels

You can use levels to make pivot tables, but this will slow down report generation time. You should only use this method if you have a version of Excel (2003 and earlier) that does not support adding table columns.

What you will do

1. Add a QlikView straight table as a level object to an Excel report.
2. Build a table using levels.
3. Convert that table to a pivot table.

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

Adding levels to the template

Do the following:

1. Open an existing Excel report, or create a new one.

   See: Creating

2. Right-click the Levels node, and select Add levels.
3. Select Straight Country - Salesman - Year in the levels list. Click OK.

4. Drag the CH205_Level - Straight Country-Salesman-Year node onto empty template cells. This will create opening and closing tags for the level.

   

5. Click the + next to the CH205_Level - Straight Country-Salesman-Year node.
6. Drag the Country, Salesman, Year, and Sales nodes onto empty cells on rows between the <CH205_Level> and </CH205_Level> tags.

   In the Properties pane, ensure that Keep Sources Formats is unchecked for all four of these objects.

   

7. Type in headings for the columns in the row above the <CH205_Level> tag.

   

Creating a pivot table

Do the following:

1. Select the range of cells that includes:
   • the headings
   • the <CH205_Level> opening tag
   • the column tags
   • the </CH205_Level> closing tag
2. On the Excel ribbon, select the Insert tab. In the Tables group, select PivotTable.

3. In the Create PivotTable window, click OK.

   

   A pivot table is created in a new worksheet. You can also add the pivot table to the same worksheet and hide the column with the data.

4. Drag the Country and Salesman fields into the Row Labels box.
5. Drag the Year field into the Column Labels box.

6. Drag the Sales field into the Values box.

   

7. In the Active Field group, click the Field Settings button.

8. Change the value in the Summarize Values By tab to Sum. Click OK.

   

9. Select the cells in the table that contain Sales values. Format them as currency.

   

Ensuring data is refreshed when report is opened

If you do not perform these steps, you will see an empty pivot table in the generated report.

Do the following:

1. Under the PivotTable Tools - Analyze tab. On the left, click the Options drop down menu .
2. Select the Data tab.
3. Click the Refresh data when opening the file check box.
4. Set Number of items to retain per field to None.

5. Click OK.

   

Previewing the report

Do the following:

1. Click Preview.

   Excel launches and displays your report.

2. You will have a report with a pivot table.

   

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