How to Add Levels in Excel Reports

    This tutorial is intended to illustrate the creation of NPrinting of Excel reports containing levels.

    We are going to embed a QlikView object as a table and one as an image in an Excel report. We are then going to select two fields as levels so that the Excel report presents three tiers of information with titles and headings.

    Open an existing NPrinting document or a new one. We will use a sample QlikView document, SalesDemo.qvw.

        
        
            
              

    Create New Excel Report

                        
                Create-New-Excel-Report.png                    
              
    1. Select Reports in the lower left-hand pane
    2. Select the Excel reports type in the pane immediately above
    3. Select Excel Report in the New group of the tool bar
            
            
            
              

    Begin Creating New Report

                        
                Begin-Creating-New-Report.png                    
              
    1. Enter a Name for your report
    2. Enter a Description (optional)
    3. Select New in the Template group of the tool bar to open the Template Editor
            
            
            
              

    Open Select Objects Window

                        
                Open-Select-Objects-Window.png                    
              
    1. Right click on the Images node icon or label in the left pane
    2. Click on the Add object icon
            
            
            
              

    Import QlikView Object as Image

                        
                Import-QlikView-Object-as-Image copy.png                    
              

    Import an image of the QlikView Top 10 Salesman bar graph:

    1. Select the Connection to the QlikView document that contains the object you want
    2. Select the QlikView object CH192 in the Select object window to be included in your template as an image
    3. Click on the OK button
            
            
            
              

    Add QlikView Object as Table

                        
                Add-QlikView-Object-as-Table.png                    
              
    1. Right click on the Tables node icon or label in the left pane
    2. Select Add object
            
            
            
              

    Select Object as Table

                        
                Select-Object-as-Table.png                    
              

    Import the QlikView Top 25 Products table as a table:

    1. Select the Connection to the QlikView document that contains the object you want
    2. Select the QlikView object CH184 in the Select object window to be included in your template as a table
    3. Click the OK button
            
            
            
              

    Open the Select Levels Window

                        
                Open-the-Select-Levels-Window.png                    
              
    1. Right click on the Levels node icon or label in the left pane
    2. Select Add level
            
            
            
              

    Add Level to Template

                        
                Add-Level-to-Template.png                    
              

    Add the Year field as a level:

    1. Select the Connection to the QlikView document that contains the object you want
    2. Select the QlikView Year field in the Select levels window
    3. Click on the OK button

    More than one field you want to cycle on can be selected by using Ctrl + click to select fields additional to the first selected one.

            
            
            
              

    Open the Select Levels Window

                        
                Open-the-Select-Levels-Window-1.png                    
              
    1. Right click on the Levels node icon or label in the left pane
    2. Click on the Add levels icon
            
            
            
              

    Add Second Level to Template

                        
                Add-Second-Level-to-Template.png                    
              

    Add the CategoryName field as a level:

    1. Select the QlikView CategoryName field in the Select level window
    2. Click on the OK button
            
            
            
              

    Embed Image and Table in Template

                        
                Embed-Image-and-Table-in-Template.png                    
              
    1. Expand the Images and Tables nodes in the left pane by clicking on the '+'s to their left
    2. Drag and drop the CH184 and CH192 tokens into empty cells Make sure to place the CH192 tag on the same line as the tags for CH184
    3. Open the Excel tool bar by clicking on Toolbar, if it is not already open
    4. Apply appropriate formatting to the column headings
            
            
            
              

    Apply First Level

                        
                Apply-First-Level.png                    
              
    1. Expand the Levels node in the left pane by clicking on the '+' to its left, if Year_Level and CatagoryName_Level nodes are not visible
    2. Drag and drop the Year_Level token into an empty cell at the top of a group of three vertically contiguous empty cells
            
            
            
              

    Add Field Name as Header

                        
                Add-Field-Name-as-Header.png                    
              

    If you desire to include a dynamic Year heading for the level cycle:

    1. Expand the Year_Level node in the left pane by clicking on the '+' to its left, if the Year node is not visible
    2. Drag and drop the Year node token into an empty cell
    3. Open the Excel tool bar by clicking on Toolbar, if it is not already open
    4. Apply appropriate formatting to the Year tag
            
            
            
              

    Position Level Tags

                        
                Position-Level-Tags.png                    
              

    Drag and drop or cut and paste the Year_Level opening and closing tags to position them so that:

    • The opening level tag is in a row above any and all rows to be repeated in the level cycle and in the same column or a column to the left of any and all columns to be repeated in the level cycle. Empty rows included in the level range will be reproduced in the level cycle.
    • The closing level tag must be in a row below any and all rows to be included in the level cycle.
            
            
            
              

    Verify Level Range

                        
                Verify-Level-Range.png                    
              

    Verify the elements to be cycled in the level by clicking on the Year_Level node in the left pane. The level range becomes delimited and highlighted

            
            
            
              

    Apply Second Level

                        
                Apply-Second-Level.png                    
              

    Drag and drop the CategoryName_Level token into an empty cell at the top of a group of three vertically contiguous empty cells. Its current three cell range is delimited and highlighted.

            
            
            
              

    Add Field Name as Header

                        
                Add-Field-Name-as-Header-1.png                    
              

    If you desire to include a dynamic CategoryName heading for the level cycle:

    1. Expand the CategoryName_Level node in the left pane by clicking on the '+' to its left, if the CategoryName node is not visible
    2. Drag and drop the CategoryName node token into an empty cell
    3. Open the Excel tool bar by clicking on Toolbar, if it is not already open
    4. Apply appropriate formatting to the CategoryName tag
            
            
            
              

    Determine Level Range by Positioning Level Tags

                        
                Determine-Level-Range-by-Positioning-Level-Tags.png                    
              

    Position the level tags as described in the Position the Level Tags section above and verify the extent of the level range

            
            
            
              

    Conclude Template Editing and Preview Resulting Excel Report

                        
                Conclude-Template-Editing-and-Preview-Resulting-Ex.png                    
              
    1. Click on the Save As icon with the default or some other appropriate name for your template
    2. Click on the Preview icon in the Actions group in the Template Editor window
            
            
            
              

    Result

                        
                Result.png                    
              

    The resulting Excel report should look like this.