This tutorial is intended to illustrate the capabilities of the latest NPrinting version when creating Excel reports. It is presumed that you are familiar with common NPrinting procedures.
- Three QlikView objects are going to be embed in an Excel report, two as images and one as a table.
- The Quarter level and Quarter tag will be added to cycle at the quarters level with headings.
- Pages and the page variable tag are then going to be added to the report so that the Excel report has three pages with titles.
Open an existing NPrinting document or a new one. We will use a sample QlikView document, SalesDemo.qvw, for this tutorial, which can be downloaded from here. Establish a connection to it (see Connect to a QlikView document).
Create a New Excel Report
- Select Reports in the lower left-hand pane
- Select the Excel reports type in the pane immediately above
- Select Excel Report in the New group of the tool bar
Set Basic Parameters and Open New Template in Template Editor
- Enter a report Name
- Enter a Description (optional)
- Click on New in the Template section of the tool bar
Create a New Excel Template: Import QlikView Objects as Images
Import an image of a QlikView object:
- Right click on the Images node icon or label in the left pane
- Select Add objects
- Select the QlikView objects CH186 and CH187 in the Select objects window to be embedded in your template as images
- Click on OK
QlikView objects added as images appear exactly the same as in QlikView. The data are not modifiable.
Embed Images in Template and Preview
- Expand the Images node in the left pane by clicking on the '+' to its left if necessary
- Drag the CH186 and CH187 node tokens into the template one at a time and drop each into its own cell
- Select the upper part of the Preview icon in the Actions group to see the result in the native output format of the template
Images can be embedded side by side as well.
Preview Resulting Excel Report in Various Output Formats
Click on the lower part of the Preview icon to select previewing in a particular output format
Add QlikView Objects as Tables: Selection
- Right click on the Tables node icon or label in the left pane
- Select Add objects
- Select CH184 in the Select Objects window
- Click on OK
QlikView objects added as tables can appear exactly the same as in QlikView or formatting can be modified partially or completely. The data are modifiable.
Make Room for Table CH184
Table CH184 is four columns wide, so move CH186 and CH187 tags five columns to the right to leave one column between the table and the images.
Embed a QlikView object as a table with QlikView formatting
- Expand Tables node in the left pane by clicking on the '+' to its left if necessary
- Drag and drop the CH184 node token into an unoccupied cell in the template
Pivot tables do not have component nodes, while straight table nodes can be expanded to show their component column nodes. These column nodes can be embedded individually or used as components of a different table.
Deactivate Table Expansion
- Right click on the CH184 node
- Select Properties
- Uncheck the Expand Range box and click on OK at the bottom right of the Properties window
Adjust Report Layout: Preview
- Select Preview in the Actions group in the Template Editor window
- Close Excel
Repeat the close Excel, Edit template and Preview cycle until you get the report you want. Close Excel and proceed.
Add a Level
- Right click on the Levels node
- Select Add levels
- Select Quarter from the Select Levels list
- Click on OK
Levels create tables and charts that are cycled through the values of the level field. The results for each level field value are displayed one after the other.
Embed Quarter_Level in Template
Drag the Quarter_Level node token into the template and drop it onto three, vertically-consecutive, empty cells.
Reposition Quarter_Level Tags to Define Range
- The Quarter_Level opening tag must be in a row above all rows containing tags to be cycled in the level. It must also be in a column to the left of or the same as any column containing tags to be cycled in the level
- The Quarter_Level closing tag must be in a row below all rows containing tags to be cycled in the level
- Any empty rows included in the level range will be present in the report except the rows containing the level tags. Those will be deleted
- You can verify the level range by clicking on the level node. The level range will be outlined and highlighted.
Add Quarter Tag as Cycled Heading
- Expand Quarter_Level node in the left pane by clicking on the '+' to its left if necessary
- Drag the Quarter node token into the template and drop it onto an unoccupied cell
The Quarter tag will display as the quarter relevant to the data displayed in the charts and tables.
Preview Results to This Point
- Click on the Preview icon to see the report with Quarter levels
- Close Excel and proceed
Multi-Page Publishing: Select a Page
Your report can be produced with a page for each Year, Month, Quarter, Customer, Country, etc. For example, to produce a page for each year:
- Right click on the Page node icon or label in the left pane
- Select Add page to current sheet
- Select the Year page from the list in the Select page window
- Click on OK
Embed the Year_Page Tags in Template
- Expand the Page node in the left pane by clicking on the '+' to its left if necessary
- Drag and drop the Year_Page node token into three, empty, vertical cells in the template, in the same column or to the left of the one containing the Quarter_Level tags
Arrange Year_Page Tags
- Reposition the Year_Page tags so that they define a rectangle containing all the elements of the template that should be on a page
- You can check this by clicking on the Year_Page node and the page range will be outlined and highlighted.
Embed the Year Node as Title
- Expand the Year_Page node in the left pane by clicking on the '+' to its left if necessary
- Drag and drop the Year node token into an empty cell in the template
Resulting report has a page for each year
Select Preview in the Actions group in the Report window
Your report will now be produced with a page for each year having the year as title.
Tip: To avoid having a cell or cells selected in your distributed reports, always select a cell away from your report elements just before Saving and Closing the Template Editor window at the end of what you think to be your last edit.