Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP

How to Add Totals and Subtotals to PixelPerfect Reports

cancel
Showing results for 
Search instead for 
Did you mean: 
Frank_S
Support
Support

How to Add Totals and Subtotals to PixelPerfect Reports

Last Update:

Nov 19, 2015 6:19:32 PM

Updated By:

Frank_S

Created date:

Nov 19, 2015 6:19:32 PM

Begin Creating New PixelPerfect Report

Begin-Creating-New-PixelPerfect-Report.png

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

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

Name Report and Open New Template in Template Editor

Name-Report-and-Open-New-Template-in-Template-Edit.png
  1. Enter PixelPerfect Report with Totals and SubTotals on Levels as the report Name
  2. Click on New in the Template group of the tool bar to create a new template

Add Levels to Template

Add-Levels-to-Template.png
  1. Right click on the Levels node
  2. Click on Add levels in the pop-up menu

Select Object for Level

Select-Object-for-Level.png
  1. Select the Connection to the QlikView document that contains the object you want
  2. Select the CategoryName field
  3. Click on the OK button

Begin Nesting Levels

Begin-Nesting-Levels.png
  1. Right click on the CategoryName_Level node
  2. Click on Add levels in the pop-up menu

Select Object for Sub-level

Select-Object-for-Sublevel.png
  1. Select the Connection to the QlikView document that contains the object you want
  2. Select the Top 25 Products chart
  3. Click on the OK button

Begin Creating Levels in Template

Begin-Creating-Levels-in-Template.png
  1. Right click on the Detail bar or within its range
  2. Select Insert Detail Report
  3. Select “CategoryName_Level”

Embed Field in Detail1 Band

-Embed-Field-in-Detail1-Band.png

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

Format Category Heading

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

Create the CH184 Sublevel

Create-the-CH184-Sublevel.png
  1. Right click on the DetailReport - “CategoryName_Level” bar
  2. Select Insert Detail Report
  3. Select “CH184_Level”. The result is DetailReport1 - “CategoryName_Level.CH184_Level” bar.

Add Group Header

Add-Group-Header.png
  1. Right click on the DetailReport - “CategoryName_Level.CH184_Level” bar
  2. Select Insert Band
  3. Select “GroupHeader”. The result is a “GroupHeader1” bar appears.

Add Group Footer

Add-Group-Footer.png
  1. Right click on the DetailReport - “CategoryName_Level.CH184_Level” bar
  2. Select Insert Band
  3. Select “GroupFooter”. The result is a “GroupFooter1” bar appears.

Embed Headings in Template

Embed-Headings-in-Template.png
  1. Expand the CH184_Level by clicking on the right pointing chevron to its left
  2. Hold 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
  1. 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-Text.png
  1. Select the entire header table
  2. Format the text Arial, 16px, Bold, centered or as appropriate

Format Heading Borders

Format-Heading-Borders.png
  1. Select the Borders option in Property Grid, which is the lower left pane
  2. Open the drop-down menu by clicking on the down-pointing triangle at the extreme right of the field
  3. Click on All to render all cell borders visible
  4. Drag 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

Embed-Data-in-Template.png
  1. 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
  1. 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

Format-Data-in-Template.png
  1. Select the whole row of data field tags
  2. Format them Arial & 12pt

Render Borders Visible and Eliminate White Space

Render-Borders-Visible-and-Eliminate-White-Space.png
  1. Select the Borders option in Property Grid, which is the lower left pane
  2. Open the drop-down menu by clicking on the down-pointing triangle at the extreme right of the field
  3. Click on All to render all cell borders visible
  4. Deselect the top border
  5. Drag 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

Open-Table-Cell-Tasks-and-FormatString-Editor.png
  1. Select the CH184_Level.Total Sales cell
  2. Click on the small arrow above the upper right corner of the cell
  3. Click on the browse button at the extreme right of the Format String field

Format "CH184_Level.Total Sales" Cell

Format--CH184_LevelTotal-Sales--Cell.png
  1. Select Currency
  2. Select Custom
  3. Enter $ 0
  4. Click on the OK button
  5. Right justify the text

Format "CH184_Level.Gross Profit" Cell

Format CH184_Level.Gross Profit Cell.png


Repeat the above process for the CH184_Level.Gross Profit cell.

Format "CH184_Level.GP%" Cell

Format CH184_Level.GP% Cell.png
  1. Select the CH184_Level.GP% cell
  2. Open the FormatString Editor again
  3. Select Percent
  4. Click on 0.00%
  5. Click on the OK button
  6. Right justify the text

Start Creating Subtotals

Start-Creating-Subtotals.png
  1. 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
  1. 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

Adapt-Cells.png


Drag the left border of the CH184_Level.Total Sales cell and the right border of the CH184_Level.Gross Profit cell inGroupFooter1 until they line up with those of the corresponding cells in Detail2.

Open Summary Editor for CH184_Level.Total Sales Cell

Open-Summary-Editor-for-CH184_LevelTotal-Sales-Cel.png
  1. Select the CH184_Level.Total Sales cell
  2. Click on the small arrow above the upper right corner of the cell
  3. Click on the browse button at the extreme right of the Summary field

Set Summary Running and Open Format String Editor

Set-Summary-Running-and-Open-Format-String-Editor.png

  1. Set the Summary Running radio button to Group
  2. Click on the browse button at the extreme right of the Format string field

Format Cell Content and Close

Format-Cell-Content-and-Close.png
  1. Select Currency
  2. Select the Custom tab
  3. Choose the $ 0 format
  4. Click on the OK button

Close Summary Editor

Close-Summary-Editor.png


Click on the OK button.Note that the cell content has become Sum([CH184_Level.Total Sales]).

Repeat Process for CH184_Level.Gross Profit Cell

Repeat-Process-for-CH184_LevelGross-Profit-Cell.png


Repeat the above process for the CH184_Level.Gross Profit cell.

Format Subtotal Cell Content

Format-Subtotal-Cell-Content.png
  1. Select the two subtotal cells by drawing a rectangle that includes both while holding the left mouse button down
  2. Format the text as Arial, 12px, Bold, Right justified

Render Subtotal Cell Borders Visible and Eliminate White Space

Render-Subtoatal-Cell-Borders-Visible-and-Eliminat.png
  1. Select the Borders option in Property Grid, which is the lower left pane
  2. Open the drop-down menu by clicking on the down-pointing triangle at the extreme right of the field
  3. Click on All to render all cell borders visible
  4. Deselect the top border
  5. Drag 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

Begin-Creating-Report-Totals.png
  1. Right click on the DetailReport - “CategoryName_Level” bar
  2. Select Insert Band
  3. Select “ReportFooter”. The result is a “ReportFooter1” bar appears.

Copy Subtotal Cells

Copy-Subtotal-Cells.png


Select both sum cells in GroupFooter1 and copy them by pressing Ctrl + C.

Paste Subtotal Cells in ReportFooter

Paste-Subtotal-Cells-in-ReportFooter.png


Paste them into ReportFooter by selecting the ReportFooter range and pressing Ctrl + V.

Open Summary Editor

Open-Summary-Editor.png
  1. Select the Sum([CH184_Level.Total Sales]) cell in ReportFooter
  2. Click on the small arrow above the upper right corner of the cell
  3. Click on the browse button at the extreme right of the Summary field

Set Summary Running and Open FormatString Editor

Set-Summary-Running-and-Open-FormatString-Editor.png

  1. Set the Summary Running radio button to Report
  2. Click on the browse button at the extreme right of the Format string field

Format Cell Data Content

Format-Cell-Data-Content.png
  1. Select Currency
  2. Select the Custom tab
  3. Choose the $ 0 format
  4. Click on the OK button

Close Summary Editor

Close-Summary-Editor-1.png

Click on the OK button. Repeat the above process for the Sum([CH184_Level.Total Sales]) cell in ReportFooter.

Format Report Total Cell Contents

Format-Report-Total-Cell-Contents.png

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


Format Borders.png
  1. Select the Borders option in Property Grid, which is the lower left pane
  2. Open the drop-down menu by clicking on the down-pointing triangle at the extreme right of the field
  3. Click on All to render all cell borders visible
  4. 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

Drag report footer.png


Drag the ReportFooter range lower border up to coincide with the Report Total cell lower border to eliminate the white space at the bottom of the report.

Save Template

Save-Template.png


Click on the Save As icon in the Report group.

Name and Save to Specific Folder

Name-and-Save-to-Specific-Folder.png

  1. Make sure the Save As window is opened in the C:\NPrintingTraining\Templates folder, browse to it if not
  2. Enter a File name or accept the suggested one
  3. Click on the Save button

Preview Report

Preview-Report.png

Click on the upper part of the Preview icon in the Report group.

Inspect Report Detail

Inspect-Report-Detail.png

Version history
Last update:
‎2015-11-19 06:19 PM
Updated by: