Leveraging a QlikView Section Access table to dynamically create an Nprinting Report Distribution list with the same user based filters

    Leveraging a QlikView Section Access table to dynamically create an NPrinting Report Distribution list with the same user based filters


     

    This article uses NPrinting v16 and QlikView 11.2 to show a sample methodology to synchronize the user based filters in a QlikView Section Access table with the recipient based filters with an NPrinting report distribution. 

    ****I have not extended this document to cover Section Access with Active Directory Groups (in lieu of individual users) in combination with NPrinting user based filters but will endeavour to add that at a later date*****

    Step-by-step guide

     

    1. This step is optional but i opted to create my authorization table in a SQL Server database. The authorization table i created stores role, a user ID field ,  a filter (Region), and the user's email


    1.png

     

    2. The  following code in the load script provides the user based Section Access table for the QlikView application

    There is one SQL load to load the users from the SQL database and a 2nd table concatenation load to add an administrator. The result is the Section Access table that governs QVW access to the data. The administrator user is used for development of the QVW with QlikView Desktop. The administrator is also used by NPrinting to connect to the QVW and be able to view all the data.  When using a QVP QlikView server data connection from NPrinting, (note: it may be necessary to give the admin USER access as QlikView web connection do not support ADMIN access , only USER access)

    Also in the section access table is an OMIT on 3 fields. As you will see later, these 3 fields represent all the fields in a 'hidden' data island table in the QlikView QVW that will be hidden to all users of the dashboard.  The purpose of this table is to provide NPrinting with properly structured table for a 'recipient import' task so that Nprinting can connect to the QVW and import the users and their filters directly into Nprinting

     

     

    Section Access;

    LOAD ROLE as ACCESS,
    REPID as USERID,
    REGION,
    subfield('Recipient RecipientFilter RecipientEmail RecipientGroup',' ') as OMIT;
    SQL SELECT ROLE,
    REPID,
    REGION
    FROM Test.dbo.SecurityTable;

    LOAD * INLINE [
    ACCESS,USERID,REGION,OMIT
    ADMIN,0,
    ];

     

    3. The result of this simple QlikVIew application is as follows when loggin in with USER 1  for example.  (only Canada/United States REGIONs are shown)


    2.png

    4. Also in the load script i prepare a hidden' transformed version of the section access table in a format that NPrinting can import as a recipient import task.

    In this transformation i am adding a 'Group' to the table and also transforming the list of REGIONs into a delimitted list that NPrinting can read.

     

    SecurityTable:
    Load
    REPID as Recipient,
    'REGION={' & concat( distinct REGION,',') & '}' as RecipientFilter,
    EMAIL as RecipientEmail,
    if(REPID=1 or REPID=2,'Distribute','Do Not Distribute') as RecipientGroup
    group by REPID,EMAIL;
    SQL SELECT
    REPID,
    REGION,
    EMAIL
    FROM Test.dbo.SecurityTable;

    //add administrator
    Concatenate (SecurityTable)
    LOAD * INLINE [
    Recipient,RecipientFilter,RecipientEmail,RecipientGroup
    0,,admin@
    qlik.com,Do Not Distribute
    ];

     

    5. The resulting table in QlikView contains these records:


    3.png



    6. Next , in an NPrinting project , I have connected to the QVW with the administrator user ID '0' and created a PowerPoint Report that looks like this,

    4.png


    7. I have also pre-created a report distribution task to send reports to the 'Distribute' group in NPrinting. The distribute group was manually created in NPrinting. However no recipients have been added to Nprinting or to the group. The name of the group 'Distribute' however does match the 'group' value in some of the records in the hidden QlikVIew security table that was prepared for NPrinting.

    5.png


    8. Next i create a "Recipient Import Task" to import recipients and their filters from the TXT file exported from QlikVIew whenever the Qlik dashboard is refreshed:


    select 'QlikView entity' as the source

    8a.png




    Under 'Object ID', you must first create a chart in UI of the QlikVIew dashboard (hidden) that contains the fields from the hidden table. In the example above its called 'TB01'



    8b.png


    The table box does not need to be visible in the UI , so we can hide it using the 'show' property on the layout tab of the chart properties. Once hidden, you can still access the chart properties from the document properties menu item.

    8c.png


    back in NPrinting, select the table box and below is a preview of the data. Assign the appropriate field type (Email, Filter1, Group1 FullName) to map the field data from the QlikView table box to the correct NPrinting property type.


    8d.png

    9. Next you can create a job that does 1 or more of the tasks in the proper sequence:

    In this example, I first reload the source QVW , then once the data and security is updated to the QVW, I  run the import recipient/filter task next,  and finally with the recipients  and filters updated first I run the report distribution task

    9a.png

    10.  Here are the two emails that get sent


    10a.png