How to Create One or More Levels in Excel Reports

[Frank_S]

Description: This tutorial is intended to illustrate the creation of NPrinting of Excel reports containing one or more levels. It is presumed that you are familiar with common NPrinting procedures.

We are going to embed a QlikView object as a table and one as an image in an Excel report. We are then going to select two fields as levels so that the Excel report presents three tiers of information with titles and headings.

Open an existing NPrinting document or a new one. We will use a sample QlikView document, SalesDemo.qvw, which is found in the NPrinting Training Environment available on the NPrinting Training Material page. Once NP_Training.zip has been downloaded, extract it into C:\NPrintingTraining\ so that the relative path structure remains valid. Establish a connection to it  (see: How do I connect to my QlikView documents?).

Create New Excel Report

1. Select Reports in the lower left-hand pane
2. Select the Excel reports type in the pane immediately above
3. Select Excel Report in the New group of the tool bar

Begin Creating New Report

1. Enter a Name for your report
2. Enter a Description (optional)
3. Select New in the Template group of the tool bar to open the Template Editor

Open Select Objects Window

1. Right click on the Images node icon or label in the left pane
2. Click on the Add object icon

Import QlikView Object as Image

Import an image of the QlikView Top 10 Salesman bar graph:

1. Select the Connection to the QlikView document that contains the object you want
2. Select the QlikView object CH192 in the Select object window to be included in your template as an image
3. Click on the OK button

Add QlikView Object as Table

1. Right click on the Tables node icon or label in the left pane
2. Select Add object

Select Object as Table

Import the QlikView Top 25 Products table as a table:

1. Select the Connection to the QlikView document that contains the object you want
2. Select the QlikView object CH184 in the Select object window to be included in your template as a table
3. Click the OK button

Open the Select Levels Window

1. Right click on the Levels node icon or label in the left pane
2. Select Add level

Add Level to Template

Add the Year field as a level:

1. Select the Connection to the QlikView document that contains the object you want
2. Select the QlikView Year field in the Select levels window
3. Click on the OK button

More than one field you want to cycle on can be selected by using Ctrl + click to select fields additional to the first selected one.

Open the Select Levels Window

1. Right click on the Levels node icon or label in the left pane
2. Click on the Add levels icon

Add Second Level to Template

Add the CategoryName field as a level:

1. Select the QlikView CategoryName field in the Select level window
2. Click on the OK button

Embed Image and Table in Template

1. Expand the Images and Tables nodes in the left pane by clicking on the '+'s to their left
2. Drag and drop the CH184 and CH192 tokens into empty cells Make sure to place the CH192 tag on the same line as the tags forCH184
3. Open the Excel tool bar by clicking on Toolbar, if it is not already open
4. Apply appropriate formatting to the column headings

Apply First Level

1. Expand the Levels node in the left pane by clicking on the '+' to its left, if Year_Level and CatagoryName_Level nodes are not visible
2. Drag and drop the Year_Level token into an empty cell at the top of a group of three vertically contiguous empty cells

Add Field Name as Header

If you desire to include a dynamic Year heading for the level cycle:

1. Expand the Year_Level node in the left pane by clicking on the '+' to its left, if the Year node is not visible
2. Drag and drop the Year node token into an empty cell
3. Open the Excel tool bar by clicking on Toolbar, if it is not already open
4. Apply appropriate formatting to the Year tag

Position Level Tags

Drag and drop or cut and paste the Year_Level opening and closing tags to position them so that:

• The opening level tag is in a row above any and all rows to be repeated in the level cycle and in the same column or a column to the left of any and all columns to be repeated in the level cycle. Empty rows included in the level range will be reproduced in the level cycle.
• The closing level tag must be in a row below any and all rows to be included in the level cycle.

Verify Level Range

Verify the elements to be cycled in the level by clicking on the Year_Level node in the left pane. The level range becomes delimited and highlighted

Apply Second Level

Drag and drop the CategoryName_Level token into an empty cell at the top of a group of three vertically contiguous empty cells. Its current three cell range is delimited and highlighted.

Add Field Name as Header

If you desire to include a dynamic CategoryName heading for the level cycle:

1. Expand the CategoryName_Level node in the left pane by clicking on the '+' to its left, if the CategoryName node is not visible
2. Drag and drop the CategoryName node token into an empty cell
3. Open the Excel tool bar by clicking on Toolbar, if it is not already open
4. Apply appropriate formatting to the CategoryName tag

Determine Level Range by Positioning Level Tags

Position the level tags as described in the Position the Level Tags section above and verify the extent of the level range

Conclude Template Editing and Preview Resulting Excel Report

1. Click on the Save As icon with the default or some other appropriate name for your template
2. Click on the Preview icon in the Actions group in the Template Editor window

Result

The resulting Excel report should look like this.