How to Create Excel Reports with Nested Levels and Subtotals

    Begin Creating New Excel Report

                        
                Begin-Creating-New-Excel-Report.png                    
              
    1. Select Reports in the lower left pane
    2. Select Excel reports in the upper left pane
    3. Click on the Excel Report icon in the New group of the tool bar
            
            
            
              

    Name and Describe New Report

                        
                Name-and-Describe-New-Report.png                    
              
    1. Enter a Name and an explanatory Description. The Description is optional.
    2. Click on the New icon in the Template group to open the Template Editor
            
            
            
              

    Add Levels to Template

                        
                Add-Levels-to-Template.png                    
              
    1. Right click on the Levels node
    2. Click on the Add levels button
            
            
            
              

    Select Levels to Add

                        
                Select-Levels-to-Add.png                    
              
    1. Select the Connection to the QlikView document that contains the object you want
    2. Click on the Field or Object to add, Ctrl+Click to select additional Fields or Objects
    3. Click on the OK button
            
            
            
              

    Add Object as Table to Template

                        
                Add-Object-as-Table-to-Template.png                    
              
    1. Right click on the Tables node
    2. Click on the Add objects button
            
            
            
              

    Select Object to Add to Template

                        
                Select-Object-to-Add-to-Template.png                    
              
    1. Select the Connection to the QlikView document that contains the object you want
    2. Click on the Object to add, Ctrl+Click to select additional Objects
    3. Click on the OK button
            
            
            
              

    Embed ProductName and Total Sales in Template

                        
                Embed-ProductName-and-Total-Sales-in-Template.png                    
              
    1. Expand the CH184 - Top 25 Products node by clicking on the '+' on its left
    2. Drag the ProductName and Total Sales node tokens into the template and drop them on D6 and E6 respectively
            
            
            
              

    Add Sum for Total Sales

                        
                Add-Sum-for-Total-Sales.png                    
              

    Enter =SUM(E7:E8) in cell E9. NPrinting will add rows as necessary to contain all values

            
            
            
              

    Format Sum

                        
                Format-Sum.png                    
              

    Format cell E9 to 11px, Bold, right justified, Custom = Accounting with no digits to the right of the decimal point.

            
            
            
              

    Embed Country_Level in Template

                        
                Embed-Country_Level-in-Template.png                    
              

    Drag the Country_Level node token into the template and drop it onto cell C4.

            
            
            
              

    Adjust Country_Level Range

                        
                Adjust-Country_Level-Range.png                    
              
    1. Drag the </Country_Level> tag down to C11 so that the Country Level includes the cell containing the sum plus an empty row
    2. Drag the Country node token into the template and drop it onto cell C6
            
            
            
              

    Format Country Tag

                        
                Format-Country-Tag.png                    
              

    Format cell C6 to 11px, Bold.

            
            
            
              

    Add Another Total to Template

                        
                Add-Another-Total-to-Template.png                    
              

    Enter the formula =SUM(E6:E11)/2 into cell E12. The sum is divided by 2 because the SUM function will add all the values including the sum of those values that is in cell E9. Format cell E9 to 11px, Bold, right justified, Custom = Accounting with no digits to the right of the decimal point.

            
            
            
              

    Embed CategoryName Level and CategoryName Tag in Template

                        
                Embed-CategoryName-Level-and-CategoryName-Tag-in-T.png                    
              
    1. Drag the CategoryName_Level node token into the template and drop it onto cell A2
    2. Drag the </CategoryName_Level> tag down to A14
    3. Drag the CategoryName node token into the template and drop it onto cell B3
            
            
            
              

    Format CategoryName Tag

                        
                Format-CategoryName-Tag.png                    
              

    Format cell B3 to 12px, Bold.

            
            
            
              

    Add Sum as Grand Total

                        
                Add-Sum-as-Grand-Total.png                    
              

    Enter the formula =SUM(E2:E14)/3 into cell E15. The sum is divided by 3 because the SUM function will add all the values including the subtotals in cells E9 and E12.

            
            
            
              

    Format Grand Total

                        
                Format-Grand-Total.png                    
              

    Format cell E15 to 14px, Bold, right justified, Custom = Accounting with no digits to the right of the decimal point.

            
            
            
              

    Eliminate Empty Rows

                        
                Eliminate-Empty-Rows.png                    
              

    Drag the deleterow node token to any cell in an empty row that you desire or need to eliminate.

            
            
            
              

    Inspect Report

                        
                Inspect-Report.png                    
            
            
            
              

    Save Template

                        
                Save-Template.png                    
            
            
            
              

    Set a Template File Name

                        
                Set-a-Template-File-Name.png                    
              
    1. Make sure the Save template as... window is open to C:\NPrintingTraining\Templates
    2. Enter a File name or accept the suggested name derived from the report name
    3. Click on the Save button