How to Create an Excel PivotTable Report

    This tutorial demonstrates one method of creating an Excel pivot table. If you have a pivot table in QlikView that you want to reproduce in an Excel report you can do one of two things: convert your pivot table to a straight table in QlikView or clone your pivot table and convert the clone to a straight table in QlikView. Once this has been done, open NPrinting, connect to your QlikView document, and proceed as follows:


    Create New Excel Report

    1. Select Reports in the lower left pane
    2. Select Excel reports in the upper left pane
    3. Select Excel Report in the New group of the function ribbon

    Name Report and Create New Template

    1. Enter Excel Pivot Table Report as Name and optionally a Description
    2. Select New in the Template group

    Add Straight Table as Level

    1. Select and right click on the Levels node
    2. Select Add level
    3. Select the Connection
    4. Select Straight Country - Salesman - Year - CH205 in the Select level window
    5. Click on OK

    Embed Level in Template

    1. Expand the Levels node by clicking on the '+' on its left, if necessary
    2. Drag the CH205_Level - Straight Country-Salesman-Year node icon to an empty template cell

    Add Columns to Level

    1. Expand the CH205_Level - Straight Country-Salesman-Year node by clicking on the '+' on its left
    2. Drag the Country, Salesman, Year, and Sales node icons to empty cells on lines between the lines containing the <CH205_Level> and </CH205_Level> tags

    Creating Named Range

    1. Give the columns headings in the line above the line containing the <CH205_Level> tag
    2. Select a range of cells including the lines containing the headings,  the <CH205_Level> tag, the column tags, and  the </CH205_Level> tag
    3. Give the range a name in the left-most field just above the template pane
    4. Press Enter on your keyboard
    5. Save and then Close

    Begin Creating Pivot Table

    1. Select Sheet2
    2. Open the tool bar, if it is not already open, by selecting the Toolbar icon
    3. Select the Insert tab of the toolbar
    4. Select PivotTable in the Tables group

    Create Pivot Table

    1. Enter the name you gave to the range you created on Sheet1 into the Table/Range field
    2. Select OK

    Adding Fields to Report Template

    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

    Adjust Value Field Settings

    1. Select the Field Settings button in the Active Field group
    2. Change the value of the Summarize value field by by selecting Sum
    3. Click on OK

    Set Data to Be Refreshed

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

    Complete Editing

    1. Select Save and Close


    1. Select Preview in the Actions group

    The Resulting Excel Pivot Table Report


    This is the resulting pivot table report in Excel