Improving filtering in Nprinting

    As of the September 2017 release of Qlik Nprinting we can now use the cycling feature, a gap feature from Nprinting 16, that has now been added to the product.

    You can read more about the feature here: Cycling your reports ‒ Qlik NPrinting .

     

    However, in this feature all the reports ends up in a .zip file, even though we apply a filter for a single user. Filters needs to be dynamic based on what the users can see elsewhere. Faced with this not-so-user-friendly we ended up with two questions

     

    How can we filter easily and friendly for a user? No users want a zip file.

    How can each user receive a report dynamically?

     

    So we came up with a solution for both of these.

    The solution includes a single Qlik Sense app which builds a logic on filters. The Qlik Sense app has 3 tables which is identical to how an import job would look like. Dynamically based on the data we used in other apps. So we use Nprinting to extract them out of QS to a xlsx file through a regular "Publishing task", and import them again through an "Importing task".

    As more and more filters add, we simply add them within the QS script - to have a single truth for all our reports.

     

    Step 1. Import Active Directory automatically to Nprinting

    The solution builds on the import of an .xls file, which can be handled manually, as explained here: Importing users with filters and groups ‒ Qlik NPrinting , and can be automated, as with my own solution, mentioned here: Import Nprinting Users from Active Directory automatically . Either way, the way columns are handled within the Excel file would likely be a similar requirement if there is an AD connection in a future release of the product.

     

    The trick here is to include every userfilter per user, all of them with a number. In the PowerShell script in the automated version, this is a very simple script suggesting 10 filters to start with, but allows us to connect a number (userfilter_001) to a specific filter.

     

    Step 2a. Structure user access hierarchy with Section Access (or similar)

    So in order to filter a particular region, product or country on a specific user, we need something to filter on. In our case we used Active Directory groups to slice data, with Section Access, e.g. SA_Region_Austria to filter out people who should look at Austria. All members only allowed to see Austria would then be a member of this group. In our case we actually use a more complex script, as we have several fields to limit data on. You can read more about this here:

    A Primer on Section Access

    Basics for complex authorization

    Data Reduction Using Multiple Fields

    We include the Section Access in our apps through a must_include statement, but also allows it to be disabled through a script. This allows us to read the SA tables but not apply them, using these very filters to filter out e.g. regions, products or countries. By using the very same script, we ensure we do not have different versions of the SA script. In the attached sample .qvf file userfilter_001 and userfilter_002 is using such SA groups.

     

    set vL_ImportOnly_SA = 1;

    $(must_include=[lib://Shared\SectionAccess.qvs] );

     

    //0 = production mode, SA in place

    //1 = no SA

    //2 = SA on, but in this specific document we do not drop the authentication bridge

     

    if vL_ImportOnly_SA = '1' then

         set vL_ImportOnly_SA = 1;

    elseif vL_ImportOnly_SA = '2' then

         set vL_ImportOnly_SA = 2;

    else

         set vL_ImportOnly_SA = 0;

    end if;

     

    Step 2b. Row-level access instead of SA

    Assuming that e.g. Salespeople do not have access to the Qlik Sense environment, but should receive a report, we can connect their username to filtering out data instead. This assumes that on each row in the data model there is a connection to their username, and thus we can connect them. In the attached sample .qvf file userfilter_003 and userfilter_004 shows how this can be done.

     

    (Step 3. - Add a distribution list for a group of people to receive reports - Optional)

    As we control access largely through AD Groups, we decided to add all users that should receive a report in a specific group, called NP_Austria. By using this group we control in the AD, not in the script, who should receive a report. The members of the group will be added to a "virtual group" within the below QS script, as we apply filters, send it into Nprinting and similar. Of course, the group can be added directly in Nprinting as well. We can also manually hardcode within the QS script who should receive reports, by logic.

     

    Step 4. Produce filter tables for Nprinting within Qlik Sense.

    We use a Qlik Sense script to generate tables, practically identical to how an Excel import file would look. In the attached sample file "Generic Nprinting filters" you'll see 4 samples.

    • userfilter_001 - a group receiving reports about Austrian products, filtered on different categories. Within QS we use Section Access to filter out specific product categories, which we apply here too.
    • userfilter_002 - same as 001, but management - which usually includes a summary and no data constraints. Thus we apply no filter. No filter = all values.
    • userfilter_003 - Sales people in France, which has not access within QS, e.g. a license constraint. The users have AD accounts, and are members of a receiving AD group.
    • userfilter_004 - same as 003, but management - which usually includes a summary and no data constraints. Thus we apply no filter. No filter = all values.


    When we export users, filters and groups - we bundle together 001+002, 003+004.

    001 and 002 are receiving different reports (standard vs summary), but same standard Nprinting filters apply (e.g. Region Europe) and they use the same Nprinting app and Nprinting connection. As you may notice, when selecting in the QS app whichfilter_filter = 001 and whichfilter_user = 001, we still see userfilter_002 under the sheet Users, and on the sheet Groups. It is missing from "Filters" as no filter = all values.


    The attached sample file can help you get started, which includes the above 4 examples. Modify the script accordingly to suit your needs. Suggestions is - when you are done - to publish it to a stream and reload it daily. Reload it daily _after_ you import the users from the xlsx file, to ensure that you have the newest information. You can also for instance import .qvd's from other Qlik Sense apps that filters out data, builds tables etc.


    Regarding Nprinting filters:

    Use one connection per report, even though they go to the same app. The reason for this is that otherwise the filters, applied on users can cause conflicts. The Products for report A will be applied to both report A and B, for user01. Yes, this means that on a general static filter, e.g. Country: Austria, it needs to be added several times, one for each app.


    Step 5. Filter report within Nprinting

    Go into the admin interface of your Nprinting server.


    First, you need to create a new Nprinting app.

    Apps > Create app.

    Name, e.g. "Nprinting filters"


    Then we need a connection

    Connections > Create connection

    Name, e.g. "Nprinting filter connection"

    Source: Qlik Sense

    Fill out the proxy address and the Sense app ID accordingly.


    Step 5a. Userfilter 001 and 002


    For filters and reports, you may use the attached sample file - "Nprinting filters - create userfilter_001 report.zip".


    We also need filters.

    These filters will filter on which report we want to use from the QS app.

    Filters > Create filter

    Name: Nprinting filters - userfilter_001

    Use the following filters:

    whichfilter_filters: userfilter_001

    whichfilter_users: userfilter_001

    VirtualGroup: 1

     

    We also need to create a report:

    Reports: Create report

    Name: Nprinting filters - create userfilter_001

    You may use the attached xlsx file as a template - filter_output_template.xlsx. It needs to look exactly like Nprintings import format, meaning one sheet for Filters, one for Users and one for Groups.

    Add the filter: Nprinting filters - userfilter_001




    Before we can create the output job, we need to add a folder as a destination

    Destination > Folders > Create folder.

    Let's call the connection "Output Nprinting filters".

    Select "Custom path" and e.g. "D:\Shared\Import-filters_auto"



    We then need to create a "Publish task"

    Publish tasks > Create task

    Name: Nprinting filters - create userfilter_001

    App: Nprinting filters

    Reports: Nprinting filters - create userfilter_001

    Friendly name: userfilter_001

    Output format: XLSX

    Add yourself (rootadmin) as User/Groups, but under Destinations only add the folder connection "Output Nprinting filters".

    Suggestion is to schedule this daily, _after_ the QS app.


    That's it! When the task is run, it will output a file on disk, which we will use in a later step.



    Step 5b. Userfilter 003 and 004

    Repeat the above steps, you may use the same App but as it is a different report, using probably different filters, you may want to use a new connection. This means that you need to set up new filters, reports and tasks. Destination folder can be the same.

     

    Step 6. Import the filter tables though an Import job in Nprinting.

    Now that we have a file ready on disk with a bunch of users, we can import it. Most of these users already exist through an earlier import of the full AD, so this import job will:

    Create new filters on each user, e.g.

    • userfilter_001_USER, userfilter_002_USER etc
    • Create virtual groups, that we will use for distribution. These are called "USERFILTERGROUP_001", "USERFILTERGROUP_002" and so on.
    • Add these virtual groups on each user.

    You will notice this that under each user he/she will be part of the filters, and the virtual groups. Attached is a sample file showing what it could look like.


    Go to Tasks > Import filters and recipients task > Create import task

    Name: userfilter_001

    Import file path: D:\Shared\Import-filters_auto\userfilter_001.xlsx

    You may check all the checkboxes in the configuration. We want this job to import accordingly. Please note that if a user is removed from a distribution group, he will disappear from Nprinting temporarily. As you might have schedule a full AD import every day, a day later he will return (or upon the next import).

    Schedule this daily, _after_ the output job is done.

     

    Step 7. Add the virtual user group to your reports.

    Now that we have set up filters on each user, and a virtual distribution group, we can add these to our regular Nprinting reports.

     

    Filters on each user, will be applied first. Each user will receive a sliced version of the report, with his or her products/regions. As they are a member of the virtual distribution group called USERFILTERGROUP_001, they will all get the report on their Newsstand/email inbox. Add this virtual group under the Publishing task > Task name > Users/groups.

    Filters added directly on a report, will be applied afterwards.


    If you are receiving warnings in the publishing task, it is due to a user-filter have filtered out some data, and the report-filters are filtering out "the rest" on a specific user. Thus there is nothing left to send to the user. You might want to reconsider how the report structure is set up.

    You may read more about filter order here: Static and dynamic filters ‒ Qlik NPrinting .

     

    Good luck!

     

     

    Changelog:

    v 1.0 - 2017-12-22

    Initial version. Tested with Qlik Sense September 2017 and Nprinting September 2017.