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

                        
                Create-New-Excel-Report.png                    
              
    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

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

    Add Straight Table as Level

                        
                Add-Straight-Table-as-Level.jpg                    
              
    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

                        
                Embed-Level-in-Template.png                    
              
    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

                        
                Add-Columns-to-Level.png                    
              
    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

                        
                Creating-Named-Range.png                    
              
    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

                        
                Begin-Creating-Pivot-Table.png                    
              
    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

                        
                Create-Pivot-Table.png                    
              
    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

                        
                Adding-Fields-to-Report-Template.png                    
              
    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

                        
                Adjust-Value-Field-Settings.png                    
              
    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

                        
                Set-Data-to-Be-Refreshed.png                    
              
    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

                        
                Complete-Editing.png                    
              
    1. Select Save and Close
            
            
            
              

    Preview

                        
                Preview.png                    
              
    1. Select Preview in the Actions group
            
            
            
              

    The Resulting Excel Pivot Table Report

                        
                The-Resulting-Excel-Pivot-Table-Report.png                    
              

    This is the resulting pivot table report in Excel