Qlik Community

QlikView Documents

QlikView documentation and resources.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.

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

JonnyPoole
Employee
Employee

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

Comments
Asier_Fernandez
Employee
Employee

Very nice post jpe , I've been testing it and works perfect. The only thing I had to add is a connection field in the qlikview security table as, if you don't include it and you have sereral conection/sources in your .nsq when importing the users, groups and filters it sets the default conection source to the filter.

0 Likes
JonnyPoole
Employee
Employee

Thanks for the update and sharing the extra step needed for multiple connections in an NSQ.

Just because its brand new, I would also suggest  taking a look at the technique outlined in the following document to assist when the source QVWs are secured by NTNAME (instead of USERID). It could decreases the required maintenance substantially.

Create NPrinting Reports from an NTNAME Secured Document

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2015-07-27 01:52 PM
Updated by: