Skip to main content Skip to complementary content
Qlik Resources

Adding totals and subtotals to PixelPerfect reports

You can add totals to columns in a PixelPerfect report with at least one table. If the report has nested levels, you can add subtotals for some levels.

Create new PixelPerfect 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 Totals and SubTotals on levels.
  3. Select PixelPerfect 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.

Add levels to template

Do the following: Right-click the Levels node, and select Add levels.

Select object for level

Do the following:

  1. Select the Connection to the QlikView document that contains the object you want.
  2. Select CategoryName from the objects list.
  3. Click OK.

Begin nesting levels

Do the following:

  1. Right-click the CategoryName_Level node, and then select Add levels.

Select object for sublevel

Do the following:

  1. Select the Connection to the QlikView document that contains the object you want.
  2. Select the CH184 - Top 10 Products chart.
  3. Click OK.

Begin creating levels in templates

Do the following:

  1. Right-click in the Detail area.
  2. Select Insert Detail Report.
  3. Select “CategoryName_Level”.

Embed field in Detail1 band

Do the following:

  • Click and drag the CategoryName node token from the Levels hierarchy, and drop it into the Detail1 open template area.

Format category heading

Do the following:

  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

Do the following:

  1. Right-click on the DetailReport - "CategoryName_Level" bar.
  2. Select Insert Detail Report.

  3. Select “CH184_Level”.

    The DetailReport1 - CategoryName_Level.CH184_Level” bar appears.

Add group header

Do the following:

  1. Right-click on the DetailReport - “CategoryName_Level.CH184_Level” bar.
  2. Select Insert Band.

  3. Select GroupHeader.

    The GroupHeader1 bar appears.

Add group footer

Do the following:

  1. Right-click on the DetailReport - “CategoryName_Level.CH184_Level” bar.
  2. Select Insert Band.

  3. Select GroupFooter.

    The GroupFooter1 bar appears.

Embed headings in template

Do the following:

  1. Expand the CH184_Level.
  2. Press the Ctrl or Shift key and select the following fields in the following order specified: ProductName, Total Sales, Gross Profit and GP%.
  3. Release the Ctrl or the Shift key.
  4. Right-click the selected fields, then drag and drop them into the GroupHeader1 range so that the upper border of the fields is aligned with the lower border of the GroupHeader1 bar.

    5. Qlik NPrinting automatically creates a table with the column names in the selected order.

Format heading text and boarders

Do the following:

  1. Select the entire header table in GroupHeader1.
  2. Format the text Arial, 16px, Bold, Centered, or as appropriate.
  3. Select the Borders option in Property Grid in the lower left pane.
  4. Open the drop-down list by clicking on the down-pointing triangle at the right of the field.
  5. Click on All to render all cell borders visible.
  6. To eliminate the white space between the headings and the data in the final report, drag the Detail2 bar upper border until it reaches with the lower border of the GroupHeader1 border.

Embed data in template

Do the following:

  1. Press the Ctrl key and select the following fields from “CH184_Level” in the following order: ProductName, Total Sales, Gross Profit and GP%.
  2. Release the Ctrl key.
  3. 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.

    4. Qlik NPrinting automatically creates a table with the tags for the columns of data in the order that they are selected.

Format data in template

Do the following:

  1. Select the entire row of data field tags.
  2. Format them to Arial and 12pt.

Render borders visible and eliminate white space

Do the following:

  1. Select the Borders option in Property Grid in the lower left pane.
  2. Open the drop-down list by clicking on the down-pointing triangle at the right of the field.
  3. Click on All to render all cell borders visible.
  4. Deselect the top border.
  5. To eliminate the white space between the footer and the data in the final report, drag the GroupFooter1 bar upper border until it reaches the lower border of the Detail2 range border.

Open table cell tasks and Format string editor

Do the following:

  1. Select the CH184_Level.Total Sales cell by clicking on it.
  2. Click on the small arrow above the upper right corner of the cell.
  3. Click on the browse button at the far right of the Format String field.

Format CH184_Level.Total Sales and CH184_Level.Gross Profit cell

Do the following:

  1. Select Currency.
  2. Select Custom.

  3. Enter $ 0.

    You can use one of the preset formats or create a custom one.

  4. Click OK.
  5. Right-justify the text.
  6. Repeat steps 1-5 for the CH184_Level.Gross Profit cell.

Format CH184_Level.GP% cell

Do the following:

  1. Select the CH184_Level.GP% cell.
  2. Open the Format string editor.
  3. Select Percent.
  4. Click on 0.00%.
  5. Click OK.
  6. Right-justify the text.

Start creating subtotals

Do the following:

  1. Press the Ctrl key and select the following fields from “CH184_Level” in the following order: Total Sales and Gross Profit.

  2. Release the Ctrl key.

    Both fields in the level are selected.

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

Adapt cells

Do the following:

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

Open summary editor for CH184_Level.Total Sales cell

Do the following:

  1. Select the CH184_Level.Total Sales cell in GroupFooter1.
  2. Click on the small arrow above the upper right corner of the cell.
  3. Click on the browse button at the far right of the Summary field.

Set summary running and open Format string editor

Do the following:

If necessary, open the Summary function drop-down list, and then click on Sum.

With this menu, you can set the summary function from the available list.

  1. Set the Summary Running radio button to Group.

    This sets the level for which the summary function is calculated.

  2. Click on the browse button at the far right of the Format string field.

Format cell content and close

Do the following:

  1. Select Currency.
  2. Select the Custom tab.
  3. Choose the $0 format.
  4. ClickOK.

  5. Click OK again.

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

  6. Repeat steps from step 1 of Open summary editor for CH184_Level.Total Sales cell to the previous step for the CH184_Level.Gross Profit cell.

Format subtotal cell content

Do the following:

  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 spaces

Do the following:

  1. Select the Borders option in Property Grid in the lower left pane.
  2. Open the drop-down list 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. To eliminate the white space below the footer in the final report, drag the GroupFooter1 range lower border till it coincides with the lower border of the subtotal cell lower border.

Begin creating reports totals

Do the following:

  1. Right click on the DetailReport - "CategoryName_Level" bar.
  2. Select Insert Band.
  3. Select ReportFooter.

The ReportFooter1 bar appears.

Copy and paste subtotal cells

Do the following:

  1. Select both sum cells in GroupFooter1, and then copy the cells by pressing Ctrl+C.
  2. Paste the cells into ReportFooter by selecting the ReportFooter range, and then pressing Ctrl+V.

Open summary editor

Do the following:

  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 far right of the Summary field.

Set summary running and open Format string editor

Do the following:

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

Format cell data content

Do the following:

  1. Select Currency.
  2. Select the Custom tab.
  3. Choose the $0 format.
  4. Click OK.

Close summary editor

Do the following:

  1. Click OK.
  2. Repeat the process from Open summary editor for the Sum([CH184_Level.Total Sales]) cell in ReportFooter.

Format report total cell contents

Do the following:

  1. Format the text as Arial, 14px, Bold,Right justified.
  2. Select the Borders option in Property Grid in the lower left pane.
  3. Open the drop-down list by clicking on the down-pointing triangle at the extreme right of the field.
  4. Click All to render all cell borders visible.

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

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 page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here