Creating Excel reports with nested levels and subtotals

Qlik NPrinting allows you to nest levels to create a hierarchy.

For example, you can create a year/category hierarchy to obtain a report with sales for each product category for each year. You can nest as many levels as you want, but note that performance will decrease with the number of nested levels.

You can add summary formulas and labels to each level of the report to show which values are displayed in that level. You can insert many QlikView objects of different types in a level, for example, tables, images, and so on.

Create new Excel report

Do the following:

  1. Select Reports in the Qlik NPrinting main menu, and then click Create report.
  2. Enter a Title for the report. Report with nested levels and subtotals.
  3. Select Excel from the Type drop-down list.
  4. Select an app from the App drop-down list.
  5. Click Create to create the report.
  6. Click Edit template to open the Template Editor.
  7. Right-click the Levels node, and then select Add levels.
  8. Select the Connection to the QlikView document that contains the object you want.
  9. Click the field or object that you want to add.

    You can press Ctrl+Click to select multiple items. For example, select the Country and CategoryName fields.

  10. Click OK.
  11. Right-click the Tables node, and then select Add objects.
  12. Click the object that you want to add. For example, select CH184.
  13. Click OK.
Note:

QlikView sheet objects that have calculated dimensions or null values can be used as levels. However, you cannot nest other objects inside them, except for fields from that sheet object. Qlik Sense visualizations with calculated dimensions cannot be used as levels.

Embed product name and total sales in template

Do the following:

  1. Expand the CH184 node.
  2. Drag the ProductName and Total Sales nodes into the template and drop them into empty cells. For example, cells D6 and E6, respectively.
  3. Enter the Excel formula =SUM(E7:E8) in cell E9.

    Note that this includes two rows, so Qlik NPrinting will add rows as necessary to contain all values.

  4. Format cell E9 to 14px, Bold, Right justifiedand Custom = Accounting with no digits to the right of the decimal point.

Create Country_Level range

Do the following:

  1. Drag the Country_Level nodes into the template and drop it into cell C4.
  2. Drag the </Country_Level> tag down to C11, so that Country Level includes the cell containing the sum plus an empty row.
  3. If necessary, expand the Country_Level node.
  4. Drag the Country node token into the template and drop it onto cell C6.
  5. Format cell C6 to 12px and Bold

Add another total to template

Do the following:

  1. Enter the formula =SUM(E6:E11)/2 into cell E12.

    The sum is divided by two because the SUM function will add all the values, including the sum of those values that are in cell E9.

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

Embed CategoryName_level and CategoryName tag in template

Do the following:

  1. Drag the CategoryName_Level node token into the template and drop it into cell A2.
  2. Drag the </CategoryName_Level> tag down to A14.
  3. Expand the CategoryName_Level node.
  4. Drag the CategoryName node token into the template and drop it into cell B3.
  5. Format cell B3 to 12px and Bold.
  6. Enter the formula =SUM(E2:E14)/3 into cell E15.

    The sum is divided by three because the SUM function will add all the values, including the subtotals in cells E9 and E12.

  7. Format cell E15 to 14px, Bold, Right justified, and Custom = Accounting with no digits to the right of the decimal point.
  8. Drag the deleterow node token to the first column of any empty row that you want to delete.

Preview and save

Do the following:

  1. Click Preview.
  2. Check the report, close it, and then edit the template again if necessary.
  3. Click Save and Close to save the template and close the Template Editor.

Did this information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

Can you tell us why it did not help you and how we can improve it?