How to Add Totals and Subtotals to PixelPerfect Reports

Ruggero_Piccoli · Nov 11, 2019 9:28:11 AM

In this tutorial you'll learn how to add totals and subtotals to QlikView Nprinting PixelPerfect reports using nested Levels. A hierarchy of levels can be created as deep as you need. Please, pay close attention to how nested levels are created here because the method is quite different from that used for other kinds of QlikView NPrinting reports.

Preliminaries:

See the tutorial How to Create a Simple PixelPerfect Report to learn the basics.
Download our NPrinting Training Material and extract it to C:\NPrintingTraining\ so that all relative paths remain valid.

Begin Creating New PixelPerfect Report

Open the NPrinting Project NPrintingTraining.nsq found in the C:\NPrintingTraining\NPrintingFiles\ folder.

Select Reports in the lower left pane
Select PixelPerfect reports
Click on the PixelPerfect Report icon to create a new report

Name Report and Open New Template in Template Editor

Enter PixelPerfect Report with Totals and SubTotals on Levels as the report Name
Click on New in the Template group of the tool bar to create a new template

Add Levels to Template

Right click on the Levels node
Click on Add levels in the pop-up menu

Select Object for Level

Select the Connection to the QlikView document that contains the object you want
Select the CategoryName field
Click on the OK button

Begin Nesting Levels

Right click on the CategoryName_Level node
Click on Add levels in the pop-up menu

Select Object for Sublevel

Select the Connection to the QlikView document that contains the object you want
Select the Top 25 Products chart
Click on the OK button

Begin Creating Levels in Template

Right click on the Detail bar or within its range
Select Insert Detail Report
Select “CategoryName_Level”

Embed Field in Detail1 Band

Drag the CategoryName node token from the Levels hierarchy and drop it into the Detail1 open template area.

Format Category Heading

Drag the label left and right borders to cover the entire width of the range from margin to margin
Set the font to Arial, 16pt, Bold, centered or format it as is appropriate for a category heading

Create the CH184 Sublevel

Right click on the DetailReport - “CategoryName_Level” bar
Select Insert Detail Report
Select “CH184_Level”. The result is DetailReport1 - “CategoryName_Level.CH184_Level” bar.

Add Group Header

Right click on the DetailReport - “CategoryName_Level.CH184_Level” barSelect Insert BandSelect “GroupHeader”. The result is a “GroupHeader1” bar appears.

Add Group Footer

Right click on the DetailReport - “CategoryName_Level.CH184_Level” barSelect Insert BandSelect “GroupFooter”. The result is a “GroupFooter1” bar appears.

Embed Headings in Template

Expand the CH184_Level by clicking on the right pointing chevron to its leftHold down the CTRL or the SHIFT key on your keyboard and keep it so while you select the following fields in the order specified:

ProductName
Total Sales
Gross Profit
GP%

release the CTRL or the SHIFT key

Right click the mouse on these selected fields then drag and drop them into the GroupHeader1 range so that the upper border of the fields are aligned with the lower border of the GroupHeader1 bar. NPrinting automatically creates a table with the column names in the selected order!

Format Heading Text

Format Heading Borders

Select the Borders option in Property Grid, which is the lower left paneOpen the drop-down menu by clicking on the down-pointing triangle at the extreme right of the fieldClick on All to render all cell borders visibleDrag the Detail2 bar upper border till it coincides with the lower border of the GroupHeader1 border to eliminate the white space between the headings and the data in the final report

Embed Data in Template

Hold down the CTRLkey on your keyboard and keep it so while you select the following fields in the order specified:

ProductName
Total Sales
Gross Profit
GP%

release the CTRL key

Left click and drag the collection of highlighted data fields and drop them into the Detail2 range so that the upper border of the fields are aligned with the lower border of the Detail2 bar. NPrinting automatically creates a table with the tags for the columns of data in the order that they are selected.

Format Data in Template

Render Borders Visible and Eliminate White Space

Select the Borders option in Property Grid, which is the lower left paneOpen the drop-down menu by clicking on the down-pointing triangle at the extreme right of the fieldClick on All to render all cell borders visibleDeselect the top borderDrag the GroupFooter1 bar upper border till it coincides with the lower border of the Detail2 range border to eliminate the white space between the footer and the data in the final report

Open Table Cell Tasks and FormatString Editor

Select the CH184_Level.Total Sales cellClick on the small arrow above the upper right corner of the cellClick on the browse button at the extreme right of the Format String field

Format "CH184_Level.Total Sales" Cell

Select CurrencySelect CustomEnter $ 0Click on the OK buttonRight justify the text

Format "CH184_Level.Gross Profit" Cell

Format "CH184_Level.GP%" Cell

Select the CH184_Level.GP% cellOpen the FormatString Editor againSelect PercentClick on 0.00%Click on the OK buttonRight justify the text

Start Creating Subtotals

Hold down the CTRL key on your keyboard and keep it so while you select the following fields in the order specified:

Total Sales
Gross Profit

release the CTRL key

Drag the collection of highlighted data field node tokens and drop them into the GroupFooter1 range so that the upper border of the fields are aligned with the lower border of the GroupFooter1 bar.

Adapt Cells

CH184_Level.Total SalesCH184_Level.Gross ProfitGroupFooter1Detail2

Open Summary Editor for CH184_Level.Total Sales Cell

Select the CH184_Level.Total Sales cellClick on the small arrow above the upper right corner of the cellClick on the browse button at the extreme right of the Summary field

Set Summary Running and Open Format String Editor

Set the Summary Running radio button to GroupClick on the browse button at the extreme right of the Format string field

Format Cell Content and Close

Close Summary Editor

Repeat Process for CH184_Level.Gross Profit Cell

Format Subtotal Cell Content

Render Subtotal Cell Borders Visible and Eliminate White Space

Select the Borders option in Property Grid, which is the lower left paneOpen the drop-down menu by clicking on the down-pointing triangle at the extreme right of the fieldClick on All to render all cell borders visibleDeselect the top borderDrag the GroupFooter1 range lower border till it coincides with the lower border of the subtotal cell lower border to eliminate the white space below the footer in the final report

Begin Creating Report Totals

Right click on the DetailReport - “CategoryName_Level” barSelect Insert BandSelect “ReportFooter”. The result is a “ReportFooter1” bar appears.

Copy Subtotal Cells

Paste Subtotal Cells in ReportFooter

Open Summary Editor

Select the Sum([CH184_Level.Total Sales]) cell in ReportFooterClick on the small arrow above the upper right corner of the cellClick on the browse button at the extreme right of the Summary field

Set Summary Running and Open FormatString Editor

Set the Summary Running radio button to ReportClick on the browse button at the extreme right of the Format string field

Format Cell Data Content

Close Summary Editor

Format Report Total Cell Contents

Format the text as Arial, 14px, Bold, Right justified.

Select the Borders option in Property Grid, which is the lower left pane
Open the drop-down menu by clicking on the down-pointing triangle at the extreme right of the field
Click on All to render all cell borders visible
Drag the Report Total cell upper border till it coincides with the lower border of the ReportFooter bar lower border to eliminate the white space between the Category Subtotals and the Report Totals in the final report

Select the Borders option in Property Grid, which is the lower left paneOpen the drop-down menu by clicking on the down-pointing triangle at the extreme right of the fieldClick on All to render all cell borders visibleDrag the Report Total cell upper border till it coincides with the lower border of the ReportFooter bar lower border to eliminate the white space between the Category Subtotals and the Report Totals in the final report

Save Template

Name and Save to Specific Folder

Make sure the Save As window is opened in the C:\NPrintingTraining\Templates folder, browse to it if notEnter a File name or accept the suggested oneClick on the Save button

How to Add Totals and Subtotals to PixelPerfect Reports