NPrinting Features for Creating Excel Reports

    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

                   
                Create-a-New-Excel-Report.png               
         
    1. Select Reports in the lower left-hand pane
    2. Select the Excel reports type in the pane immediately above
    3. Select Excel Report in the New group of the tool bar
       
       
       
         

    Set Basic Parameters and Open New Template in Template Editor

                   
                Set-Basic-Parameters-and-Open-New-Template-in-Temp.png               
         
    1. Enter a report Name
    2. Enter a Description (optional)
    3. Click on New in the Template section of the tool bar
       
       
       
         

    Create a New Excel Template: Import QlikView Objects as Images

                   
                Create-a-New-Excel-Template--Import-QlikView-Objec.png               
         

    Import an image of a QlikView object:

    1. Right click on the Images node icon or label in the left pane
    2. Select Add objects
    3. Select the QlikView objects CH186 and CH187 in the Select objects window to be embedded in your template as images
    4. 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

                   
                Embed-Images-in-Template-and-Preview.png               
         
    1. Expand the Images node in the left pane by clicking on the '+' to its left if necessary
    2. Drag the CH186 and CH187 node tokens into the template one at a time and drop each into its own cell
    3. 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

                   
                Preview-Resulting-Excel-Report-in-Various-Output-F.png               
         

    Click on the lower part of the Preview icon to select previewing in a particular output format

       
       
       
         

    The Result so Far

                   
                The-Result-so-Far.png               
         
    • The resulting Excel report looks like this.
    • Close Excel.
       
       
       
         

    Add QlikView Objects as Tables: Selection

                   
                Add-QlikView-Objects-as-Tables--Selection.png               
         
    1. Right click on the Tables node icon or label in the left pane
    2. Select Add objects
    3. Select CH184 in the Select Objects window
    4. 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

                   
                Make-Room-for-Table-CH184.png               
         

    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

                   
                Embed-a-QlikView-object-as-a-table-with-QlikView-f.png               
         
    1. Expand Tables node in the left pane by clicking on the '+' to its left if necessary
    2. 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

                   
                Deactivate-Table-Expansion.png               
         
    1. Right click on the CH184 node
    2. Select Properties
    3. Uncheck the Expand Range box and click on OK at the bottom right of the Properties window
       
       
       
         

    Adjust Report Layout: Preview

                   
                Adjust-Report-Layout--Preview.png               
         
    • 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

                   
                Add-a-Level.png               
         
    1. Right click on the Levels node
    2. Select Add levels
    3. Select Quarter from the Select Levels list
    4. 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

                   
                Embed-Quarter_Level-in-Template.png               
         

    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

                   
                Reposition-Quarter_Level-Tags-to-Define-Range.png               
         
    • 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

                   
                Add-Quarter-Tag-as-Cycled-Heading.png               
         
    1. Expand Quarter_Level node in the left pane by clicking on the '+' to its left if necessary
    2. 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

                   
                Preview-Results-to-This-Point.png               
         
    • Click on the Preview icon to see the report with Quarter levels
    • Close Excel and proceed
       
       
       
         

    Multi-Page Publishing: Select a Page

                   
                Multi-Page-Publishing--Select-a-Page.png               
         

    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:

    1. Right click on the Page node icon or label in the left pane
    2. Select Add page to current sheet
    3. Select the Year page from the list in the Select page window
    4. Click on OK
       
       
       
         

    Embed the Year_Page Tags in Template

                   
                Embed-the-Year_Page-Tags-in-Template.png               
         
    1. Expand the Page node in the left pane by clicking on the '+' to its left if necessary
    2. 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

                   
                Arrange-Year_Page-Tags.png               
         
    • 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

                   
                Embed-the-Year-Node-as-Title.png               
         
    1. Expand the Year_Page node in the left pane by clicking on the '+' to its left if necessary
    2. Drag and drop the Year node token into an empty cell in the template
       
       
       
         

    Resulting report has a page for each year

                   
                Resulting-report-has-a-page-for-each-year.png               
         

    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.