How to Create Excel Reports with Nested Levels & 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