How to Add Totals and Subtotals to PixelPerfect Reports

    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