How to Create Excel Reports with Nested Levels & Subtotals

[Frank_S]

Begin Creating New Excel Report

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

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

1. Right click on the Levels node
2. Click on the Add levels button

Select Levels to Add

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

1. Right click on the Tables node
2. Click on the Add objects button

Select Object to Add to Template

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

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

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

Format Sum

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

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

Adjust Country_Level Range

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 cell C6 to 11px, Bold.

Add Another Total to Template

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

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 cell B3 to 12px, Bold.

Add Sum as Grand Total

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 cell E15 to 14px, Bold, right justified, Custom = Accounting with no digits to the right of the decimal point.

Eliminate Empty Rows

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

Inspect Report

Save Template

Set a Template File Name

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