Qlik Community

Qlik NPrinting Documents

Documents related to Qlik NPrinting.

How to Create Excel Reports with Nested Levels & Subtotals

Employee
Employee

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
Version history
Revision #:
1 of 1
Last update:
‎12-09-2015 04:48 PM
Updated by:
Employee