Even with the advent of Qlik Sense Security rules in the QMC you may find yourself working with Section Access. Maybe you don’t have a server, maybe you want to guard against users just taking the QVF file (and all your data with it) or maybe you want to implement section access reduction. it was the latter that prompted the bog, seeing an interesting behaviour for the first time took a bit of investigating.

Either way the functionality can be tricky to implement. You often have you test various circumstances to see what works and in the process it wasn’t uncommon (back in QlikView) to lock yourself out of your own dashboard.

Section Access can do two things:

1. Stop people accessing the dashboard
2. Reduce what people can see based on their logon Id

Lets focus on the first…… Setting up Section access is simple. You just need a table loaded in from any source (inline, CSV, database, etc) and the table has to be constrained within Section Access; and Section Application; statements.

Section Access;

LOAD *
Inline [
ACCESS ,USERID
USER ,DOMAINNAME\RPEARCE
];

Section Application;

In the table two fields are required

• ACCESS
• USERID

These have to be in UPPERCASE (as do all field names and values in this table). Once the data model has been reloaded the only person who can open the dashboard is user: DOMAINNAME\RPEARCE

That’s fairly simple, the next stage of the process will be to look at reducing the data based on access granted to the USERID in the section access table. Lets review the next script:

Section Application;

SectionAccess:
LOAD *
Inline [
ACCESS ,USERID ,REDUCTION 
USER ,DOMAINNAME\RPEARCE ,1 
];

Section Application;
TableA:
LOAD * INLINE [
REDUCTION , Type , Comments
1 , Red , Value
2 , Orange , Value
3 , Yellow , Value
4 , Green , Value
5 , Blue , Value
6 , Indigo , Value
7 , Violet , Value
];

TableB:
LOAD * INLINE [
Type , Price
Red , 100
Orange , 200
Yellow , 300
Green , 400
Blue , 500
Indigo , 600
Violet , 700
];

We now have an additional field REDUCTION which would link to TableA by the field with the same name, in turn that would also link to TableB by the field Type. Because our user only has the REDUCTION value of 1 when we reload the data, save and close the dashboard and re-open there will only be one record in both TableA and TableB.

We reduce the data not only in the first table but in subsequent tables based on possible values! This is key if your data model contains a link that needs to be bridged no matter what as in the example below:

Section Access Reduction Data Model

Here the REDUCTION starts on the left hand Department table so I’d have grouped staff USERID’s by the department they work in. We move through the model reduction the data by possible value. This works well for the first three tables and users will only be able to see the sales data associated with their department.

If you consider how the next link will work… In this example I want all staff to see all purchases but this isn’t the case here. If you follow the logic both the Calendar and Purchase Tables will now only have records where there’s been a sale on the same date (and then only sales which associate with the person opening the dashboard) which isn’t what we want.

In order to resolve the issue we need to add dummy records to the sales table. One for each date there’s been a purchase duplicate those for each SalesPersonID. We can of course add a flag to ensure that data isn’t used in any counts or sums….

If we want a user to have access to several REDUCTION lines we simple add them again in the Section Access table:

Section Application;

SectionAccess: 
LOAD * Inline [ 
ACCESS ,USERID ,REDUCTION 
USER ,DOMAINNAME\RPEARCE ,1  
USER ,DOMAINNAME\RPEARCE ,2  
USER ,DOMAINNAME\RPEARCE ,3  
]; 

Section Application;

If you want the user to see all the data there’s two ways to acheive this. You could change their ACCESS to ADMIN although the proper way would be to give them a * value for REDUCTION. (THIS CODE WILL LOCK YOURSELF OUT!!!!)

SectionAccess:
LOAD * Inline [
ACCESS ,USERID ,REDUCTION 
USER ,DOMAINNAME\RPEARCE ,*
];

The wildcard only refers to data within the Section Access table so in the example above there is no other data and as such the line isn’t loaded (and you’re locked out). In the example below you would see two records in tableA and tableB:

SectionAccess:
LOAD * Inline [
ACCESS ,USERID ,REDUCTION 
USER ,DOMAINNAME\RPEARCE ,*
USER ,DUMMY ,1
USER ,DUMMY ,2
];

If you have other people that need to see 1 or 2 then of course you don’t need dummy lines but if you do use star * then you have to ensure all possibilities are covered in the section access table even if that means using a dummy record. This is what caught me out recently!

Finally we can look at the OMIT field. Now rather than reducing the records we can remove columns (Hint – Don’t remove Key fields!). Here is the Default, we have the field OMIT here but there is no value. That’s the same as saying leave all the fields intact… Lets remove the Comments Fields !:

SectionAccess:
LOAD * Inline [
ACCESS ,USERID ,REDUCTION, OMIT 
USER ,DOMAINNAME\RPEARCE ,1 ,
];

We can add the first column name we want to hide from the user:

SectionAccess:
LOAD * Inline [
ACCESS ,USERID ,REDUCTION, OMIT
USER ,DOMAINNAME\RPEARCE ,1 , Comments
];

Now when we reload, save and re-open the dashboard the field comments still shows in the data model viewer although when you preview the data it’s gone (see below)!

Sense OMIT columns

We can repeat the process to remove more columns.

SectionAccess:
LOAD * Inline [
ACCESS ,USERID ,REDUCTION, OMIT
USER ,DOMAINNAME\RPEARCE ,1 , Comments
USER ,DOMAINNAME\RPEARCE ,1 , Price
];

So effectively rather that saying what records we want to keep using REDUCTION we are saying what we want to remove by using OMIT! Pretty powerful stuff and remember this is available for free in Qlik Sense desktop. If you buy server licenses you get the QMC and even more power over what users can see by hiding sheets and objects using the security rules!

Qlik Help : https://help.qlik.com/en-US/sense/September2018/Subsystems/Hub/Content/Sense_Hub/Scripting/Security/manage-security-with-section-access.htm