Monday, 16 July 2012

Section access - part one (including inline wizard)

So you have written your first few reports, your company has gone wild for Qlikview and know it's time to think about how you're going to implement security on your reports.


This is the first of two parts on section access, Section Access is how Qlikview restricts data based upon your login.

Here's my raw data:


As you can see it's a list of 10 transactions that have been split into two Departments 

  • Sales 1
  • Sales 2

and three products.

  • A
  • B
  • C

Inline Data Wizard


I have decided to load this data inline to show off the 'Inline Data Wizard' - simply type in 


load * inline


and then hit the hammer symbol at the side of the script.



This will open the below window that you can copy and paste your data into.

Double click the field headers to amend them 
Then click ok, save and reload.

I then added a table box to my report and a multibox containing Dept and Product.
All the data from the original

Section Access

Now let's talk about limiting this data based on who is viewing the document.  Here is my raw security data.


As you can see we have 5 users, the passwords they will use to log into (section access based off Active Directory will be covered in part 2), the level of access they have to the document (User or Admin), their Dept and the product they should have access to.
Things to note
  • Section Access works on both conditions, in this example Bob will have access only to transactions belonging to "Sales 1" and "Product A"
  • A * in Section Access denotes "Select All Available Values", this means that users with this access will be able to see all values listed 
  • If a value isn't listed in the security, it will not be available to anyone!
  • All values in Section Access must be in upper-case

To add Section Access, the first thing I did was amend my dataset - adding two fields that cast the dept and product to upper-case so that my section access could select on them (All section access values are upper-case). I then dropped the original table to avoid confusion.

I then added the script
SECTION Access;

and used the table file wizard to import my security table.  I then renamed the user field to USERID and cast the selection values to upper-case.

Then save the report, but before reloading save the file under a different name (just in case you miss a step and lock yourself out of the report, which would mean you have to start over).

#Protip I am using relative paths in my reports, however this may not be suitable if pulling the data from a central location that is different to your development area.

The open document properties, on the opening tab select "Initial Data Reduction Based on Section Access"

Then reload your document and hit save - but don't close the report.

Now open a second instance of Qlikview and try to open the same report.  

If you have followed all the steps you will then be asked to enter your user ID and password, if not then close your second instance of Qlikview and look through your already open version to see if you have missed any steps.
Success!
Now if I log in as Bob, I can only see Product "A" in Dept "Sales 1".  Whilst if I log in as Admin, I can still see all the original data.


The first thing to do before trying to apply section access to your own reports, is to take a copy. If you don't accidentally lock yourself out of the report at least once you aren't trying hard enough!


In the second part of this on Section Access, I will discuss using Active Directory security, the OMIT function, Cartesian loads and generating Section Access based off "OR" functionality instead of "and".