Publishing a complex report from a Qlik Sense app

This tutorial will show you how to create and publish a complex Qlik NPrinting Excel report using Qlik Sense data.

Qlik NPrinting has many features. This tutorial is meant to help experienced users create a report. It does not outline every report type, design element, or publishing option. At the end of this tutorial, you will have created, edited, and published an Excel report that is viewable by your selected users.

Other Qlik NPrinting tutorials:

• Publishing a simple report from a Qlik Sense app
• Publishing a simple report from a QlikView document

In this tutorial, you will be:

Accessing a Qlik Sense app

Qlik Sense apps contain the data and visualizations that Qlik NPrinting uses to build reports. There are a few different ways to access a Qlik Sense app:

• You can download a Qlik Sense app from the Qlik help site, and upload it to your Qlik Sense Apps folder. This tutorial references many values and fields found in this sample app. If this is your first time performing this tutorial, you may want to use the app provided, not a custom app.
• You can use your own Qlik Sense app, and skip to Connecting Qlik NPrinting to a Qlik Sense app . If you are using your own app to complete this tutorial, pick a simple one. Qlik NPrinting experiences longer load times with complex Qlik Sense apps.

Downloading a Qlik Sense app from the Qlik help site and importing it in Qlik Sense

Do the following:

1. On the Qlik NPrinting help site, go to Sample files, and download the Sample file - Beginning with the Basics .zip file to your preferred location on your computer.
2. In Windows, unzip the folder. Beginner's tutorial.qvf is your Qlik Sense file.
3. Send the app to your system administrator, and ask them import Beginner’s tutorial.qvf into the QMC.
4. Ask your system administrator to publish the app to a stream you can access. They can publish to the default stream, Everyone.

You will now be able to see the Beginner's tutorial app in your Qlik Sense hub.

Connecting Qlik NPrinting to a Qlik Sense app 

Now we will create a Qlik NPrinting app and connect it to your Qlik Sense app.

A Qlik Sense app and a Qlik NPrinting app are very different:

• Qlik Sense app: contains data, and uses visualizations to explore that data.
• Qlik NPrinting app: contains reports, filters, and the connection to a Qlik Sense app or QlikView document.

Qlik NPrinting apps let the Qlik NPrinting Designer access your Qlik Sense data so that information can be used in a report.

Do the following:

1. Log into the Qlik NPrinting web console.
2. On the main menu, click the Apps drop-down, and click Apps.
3. Click the Create app button.
   1. Enter a name for this new app. For example: Advanced Tutorial NP App.
   2. Ensure the Enabled check box is selected. Otherwise, your app will be ignored by the Qlik NPrinting Engine.
   3. Click Create.

      You will now be able to see Advanced Tutorial NP App under Apps.

      

4. From the main menu, click the Apps drop-down, and click Connections.
   1. Click the Create connection button.
   2. In the Name box, enter a name for the connection: Advanced Tutorial Connection.
   3. In the App drop-down list, select the Qlik NPrinting app that you created previously: Advanced Tutorial NP App.
   4. Select Qlik Sense as your Source.
   5. Enter the following information:
      • Proxy address: the full computer name of the Qlik Sense server. You can find this in the Qlik Sense hub, in your browser's address bar. The format is: https://sense_proxy_computer_name.domain_name.
      • Qlik Sense app id: This information is available in the Qlik Sense hub, in your browser's address bar, on your app's overview page.

        

      • Identity: the <Windows domain name>\<user name> of the Qlik Sense user who has access to the app.

   6. Click the Run verification button. This ensures your connection is valid.

      If the connection is valid, you will see a "Verification successful" message. If not, you will see the following message: "Errors detected. Check details below."

      

   7. Once your connection settings have been verified, click Create. You will be automatically moved back to the Connections menu.
   8. Click on your new connection. You will see the status: Cache status - Generating. This means that Qlik NPrinting is connecting to Qlik Sense and importing metadata. If you have a large Qlik Sense app, this may take several minutes.
      Information note

      Every time you add new objects to your Qlik Sense app, you must reload your connection metadata.

   9. Once the cache status shows Generated, you can create a new report template. If you get an Error status, check your connection information.

You will now be able to see your new connection on the Connections screen.

If you have trouble connecting to your Qlik Sense app, see:

• Creating connections
• Troubleshooting

Creating a new filter

Static filters let you refine your data according to fields from your Qlik Sense app. In this case, we are going to create a filter called Wine & Beer from the Product Group field. A table or image with this filter applied will only display results related to wine and beer, and would ignore all other product types.

Do the following:

1. From the main menu, click the Apps drop-down, and then select Filters.
2. Click Create filter.
3. Enter a Name for your filter. For example: Wine & Beer.
4. Under App, select Advanced Tutorial NP App.

5. Click Create.

   

6. Click Fields in the filters menu.
7. Click Add field.
8. Under Connection, select your connection.

9. Under Name, select Product Group.

   Leave Override values unchecked.

10. Leave Value is in the Values area. This option forces Qlik NPrinting to select the list of values that you specify.
11. Start to type the value you want. A list of possible values will appear. In this case, choose Alcoholic Beverages.

12. Click Create.

    

Creating a new Qlik NPrinting report template

Report templates control the layout and output formats of reports. You can arrange objects and format pages using the standard options of the report type you choose. For example, an Excel report will have the same formatting options and interface as an Excel worksheet.

Do the following:

1. In the Qlik NPrinting web console, click Reports on the main menu.
2. Click the Create report button.
3. Enter a title in the Title field. For example: Advanced Tutorial Excel.
4. From the Type drop-down list, select Excel.
5. From the App drop-down list, select Advanced Tutorial NP App.
6. From the Template options, click Empty file.
7. Ensure the Enabled check box is selected. Otherwise, your report will be ignored by the Qlik NPrinting Engine.

8. Click the Enable dynamic naming check box. This will let you add variables to your report names.

   These field elements can be reordered using the sort-handles on the right.

9. You must have at least one variable. Choose Report name.
10. Click Add variable. Choose User department.
11. Click Add variable. Choose Custom. Add a hyphen or underscore in the text box that appears.
12. Click Add variable. Choose Date. Select a date format.
13. The Preview box shows your dynamic report name.

14. Click Create.

    

You will now be able to see Advanced Tutorial Excel under Reports.

Editing the report template

There are many ways to edit a report template. We will be adding:

• Titles: simple text used to organize your report. They can be altered using standard Excel formatting.
• Levels: sub-divide your data by a dimension. They can be applied to tables and images.
• Tables: take your Qlik Sense data and present it in tabular form. You can create Excel tables, or import Qlik Sense tables.
• Object filters: refine the data that is displayed in an object, such as a chart or table.

This section does not cover everything you can do in the Qlik NPrinting Designer. If you would like to read about other Excel report features, see: Excel reports.

Do the following:

1. In the Qlik NPrinting web console, click Reports on the main menu.
2. On the Reports screen, click on your report.

3. Click the Edit template button to open the Qlik NPrinting Designer template editor.

   

4. In cell A1, type in a title: For example, Quarterly trends and customer sales by region.

   Tip note You can use Excel formatting options to change the look of these titles. For example, you can change the font size and color. You can also merge several cells together so that the title is centered with the rest of the report.
5. Add a level:
   1. On the left pane, right-click on Levels, and select Add levels.
   2. A new window will open. Select Region. Click OK.

      

   3. Drag Region_Level to cell A2.

   4. Cut and paste the </Region_Level> tag to A17.

      Objects placed inside these tags will be organized by region.

   5. Expand Region_Level in the left pane.
   6. Drag and drop Region onto cell A4.

      This ensures that each level has a heading.

6. Add an Excel table:
   1. In cell A6, add a subtitle: Quarterly trends.
   2. On the left pane, right-click on Tables, and select Add objects.
   3. A new window will open. Select Quarterly Trend. Click OK.
   4. On the left-hand pane, select TFWPC - Quarterly Trend. This will open the Properties pane, below.

   5. Clear the Keep Source Formats check box.

      This removes Qlik Sense formatting from the table, so you can customize it.

   6. Set Show Headers to Show in tag cell.

   Column headers will be inserted in the tag cells and data will start in the row below. Formulas and variables in original Qlik Sense headers will not be replaced.

   7. Drag TFWPC - Quarterly Trend into cell A8. Select the cell containing the TFWPC tag, if it is not already selected.
   8. Click the Insert tab on the Excel toolbar.

   9. Click the Table icon in the Tables group. Click OK.

   10. Go to Table Tools > Design to apply table formatting.
   • Font and alignment formatting applied to the heading cell will be applied to all table cells in the heading row.
   • Font and alignment formatting applied to the content cell will be applied to all table cells in the content rows.
   • Number formatting will have no effect.

   11. Click Preview on the Qlik NPrinting Designer toolbar to preview the table.

       You now have an Excel table that can be sorted and filtered. However, you cannot format numbers.

7. Add a table with subtotals:
   1. In cell A11, add a subtitle: Customer sales.
   2. On the left pane, right-click on Tables, and select Add objects.
   3. A new window will open. Select Customer Sales and Quantity. Click OK.
   4. On the left-hand pane, select Customer Sales and Quantity. This will open the Properties pane, below.
   5. Clear the Keep Source Formats check box.

   6. Expand the new Customer Sales and Quantity node.

      Information noteYou can only expand the node to reveal column nodes if the object is a straight table or table box. If you do not see the +, you added a pivot table or a straight table with calculated columns.
   7. Drag Customer - Customer to A13 and JWaPFv - Sales to B13.

   8. In cell A15 , type: Region total. In cell B15, add a formula: =SUM(B14).

      Format B15 to display as currency.

      

8. Add table with subtotals and a filter:
   2. Add a new sheet to the Excel workbook.
   3. Go to the new sheet. In cell A1, add a subtitle: Wine and beer sales by region.
   4. On the left pane, right-click on Levels, and select Add levels.
   5. A new window will open. Select Region. Click OK.
   6. Drag Region_Level to cell A2.
   7. Cut and paste the </Region_Level> tag to A17.
   8. Expand Region_Level in the left pane.

   9. Drag and drop Region onto cell A4.

      This ensures that each level has a heading.

   10. On the left pane, right-click on Tables, and select Add objects.
   11. A new window will open. Select Product Treemap. Click OK.

   12. Click on the Product Treemap node. Under Properties > Filter, click the ¥. Select the Beer and Wine filter.

       This will ensure that this new table only shows data related to beer and wine products.

       Clear the Keep Source Formats check box.

   13. Expand the Product Treemap node.
   14. Drag Product Type to B5 , Item Desc to C5, and Sales to D5.
   15. In cell C7 , type: Region total. In cell D7, add a formula: =SUM(D6).
   16. In cell C10 , type: Total sales. In cell D10, add a formula: =SUM(D6:D8)/2.

   17. Select both D7 and D10, and format them to display as currency.

       

9. On the Qlik NPrinting template editor menu, click the File tab. Click Save.
10. Click the Template tab, and click the Preview button to preview your report.

    It may take several minutes for your preview to load, depending on the size of your report. You can stop preview generation by clicking the Abort button.

    

    

    Warning noteEdits done in preview mode will not be saved.
11. If you would like to continue editing, close the preview window, and format your report in the template editor.
12. When you are finished editing, click Save And Close.

The report template is now saved and ready to be shared with users.

Publishing to NewsStand and email

Reports need to be published in order for users to access them. We will be publishing to NewsStand and by email.

NewsStand is a web portal that allows users to preview and download reports, and subscribe to automatic updates. See: NewsStand overview

Emails contain your reports as attachments. Their contents can be customized by adding text and images. See: Distributing reports by email.

We will also create a trigger, which will automatically publish your reports according to a schedule.

Do the following:

1. In the Qlik NPrinting web console, click Tasks on the main menu, and select Publish task.
2. Click the Create task button.

   

   1. Enter a name. For example: Publish Advanced Tutorial.
   2. Select your app from the drop-down list: Advanced Tutorial NP App.

   3. Leave the default values for the other options. Click Create.

      

3. Click on the Reports tab.
   1. Click the Add report button.
   2. Select Advanced Tutorial Excel from the Choose report drop-down list.
   3. Select XLS from the Output format drop-down list.
   4. Ensure that the Attach to email and Enabled check boxes selected.

   5. Click Add report.

      

4. Click on the Users/groups tab.
   1. Click the Add user or group button.
   2. Select a user or group from the Choose recipient drop-down list.

      If you would like to learn how to create new users, see: Creating users.

   3. Select the Enabled, NewsStand destination, and Email destination check boxes.
   4. Click Add recipient.
5. Click on the Destinations tab.
   1. In the NewsStand column, click the Publish for all check box.
   2. In the Email column, click the Publish for all check box.
   3. Click Save.
6. Customize the email template:
   1. Click Email message in the main task menu.
   2. In From, select one of the following:
      • User: Select the sender email from the drop-down list. You can also use the search form to filter the list.

      • Custom: Enter a valid email address.

   3. In the To drop-down list, choose Selected users.
   4. Enter a Subject.

   5. Select the email Format by clicking on HTML, which supports formatted and dynamic text.

   6. Add text to the email body. You can customize it using the formatting buttons.
   7. Click Users, and then select tags from the list. They will be replaced with the appropriate value during email generation.

      For example, %%User_Company%% will be replaced with the name of the recipient's company. In this example, we have added %%User_FullName%%, %%User_Department%%, and %%User_Company%%.

      If your user profiles do not contain this information, the message will contain empty strings. See: Creating users.

   8. In the Attachments drop-down list, select All reports attached.

   9. Click Save.

      

7. Create a trigger:
   1. Click Triggers in the main task menu.
   2. Click Create trigger.
   3. Enter a Name: Tutorial Trigger.

   4. In the Schedule section, select the Schedule type. For this tutorial, choose: Weekly.

      The task will be executed automatically on the specified days of the week.

   5. Under Daily frequency, click Once. Leave the default time.
   6. In the Start section, you can set the Start date and Time zone of the hours specified in the trigger. The default start date is the day the trigger is created.

   7. Click Create to save the new trigger in the repository.

      

8. Go back to the Overview page.
9. Click the Run now button.

   A confirmation message will appear at the top of the page.

Your recipients can now go to the NewsStand in their browsers and view your report. They will also receive a copy in their email. Since you created a trigger, users will also receive an updated report once a week, according to the schedule you set.