topic Re: Section access to restrict data in QlikView

Section access to restrict data

Thu, 24 Oct 2013 20:02:27 GMT

I'm trying to figure out how to use section access to restrict what data my users can see. I have a relatively straightforward set of sales data that is loaded in as follows:

TRACE Load Table sales_data;

TRACE ;

Sales:

LOAD

Date,

Salesperson,

Customer,

StockCode,

QtyInvoiced,

NetSalesValue,

SalesOrder,

SalesPersonName,

CustomerName

What I need to do is restrict access down based on the Salesperson field. For example I might have a manager that I want to be able to access all (or maybe a specific group of salespersons data) whereas the actual Salesperson can only see data for their own customers. Maybe easier to explain in tabular format, as per below:

Network username	Access to Salespersons Data
johnsmith	ALL
joebloggs	01, 02, 07
peterjones	07

Can anyone explain how I achieve this? It will be a million miles more economical than creating and maintaining lots of individual .qvs files for each user.

As ever, thanks for any help that you can provide!

Re: Section access to restrict data

Josh_Good — Thu, 24 Oct 2013 21:47:32 GMT

Here is an example that shows how to set it up. Note if a users need access to multiple data sets (in my example multiple regions) then you need to have multiple lines in the Section Access Table. Also do not forget to check "Initial Data Reduction Based on Section Access" under Setting -> Document Properties -> Openning

In the attached QVW you can enter user1 through user5 to gain access to the QVW and see different data sets being displayed. Below is the script used.

Section Access;

LOAD * INLINE [

ACCESS, USERID, REGION

ADMIN, ADMIN,

USER, USER1, AFRICA

USER, USER1, AMERICA

USER, USER2, AMERICA

USER, USER3, ASIA

USER, USER4, EUROPE

USER, USER5, AMERICA

USER, USER5, *

];

Section Application;

SALES:

LOAD * INLINE [

REGION, PROFIT

AFRICA, 1000

AMERICA, 2000

ASIA, 3000

EUROPE, 4000

OCEANIA, 5000

];

Re: Section access to restrict data

Fri, 01 Nov 2013 13:22:30 GMT

Have now finally made some decent progress with this one, but have arrived at an additional problem further down the line. So, using the following, I have managed to restrict access to the data based on network credentials of the user logging into the Qlikview access point:

Section Access;

LOAD * INLINE [

ACCESS, NTNAME, SALESPERSON

ADMIN, MYDOMAIN\JOEBLOGGS, *

USER, MYDOMAIN\JOHNSMITH,41

USER, MYDOMAIN\PETERJONES,40

USER, MYDOMAIN\PETERJONES,42

...];

Section Application;

Sales:

LOAD

Date,

Salesperson AS SALESPERSON,

NetSalesValue,

CustomerName

;

SQL SELECT *

FROM MyCompanyData.dbo.SQL_VIEW_SALES

The problem I've now hit is that I have multiple SQL Loads, all concatenating to the Sales table - I need to apply the same Section Application to each SQL Load, but so far via trial and error I don't appear to be having much success.

Can anyone suggest how to tackle this (for a relative beginner to Qlikview)?

Thanks

;

Re: Section access to restrict data

Fri, 01 Nov 2013 15:15:59 GMT

Have you thought about exporting the SQL statements to separate documents (with limited access for end users) and reload tasks with QVDs as output? Then you can load the data from the QVD into the original dashboard and keep structure that perhaps are easier to work with.

Good luck!

Re: Section access to restrict data

Josh_Good — Fri, 01 Nov 2013 16:51:21 GMT

One more thing I noticed is when using section access everything must be in uppercase. That is likely the issue. Wrap all your fields in the UPPER function.

Re: Section access to restrict data

Fri, 01 Nov 2013 18:06:16 GMT

This is how I've ended up tackling it and I feel I'm almost there.

The Upper case issue raised by Josh below I don't think is a problem here as the field name is in Upper case already and the contents are always numeric.

So, I've loaded all of my various SQL views into one QVD master file and have adapted my main dashboard to look at this QVD for it's source data. I think the reason that it's failing now is that there is a calendar script (provided by someone else) the output from which also needs to be incorporated into the QVD file.

The QVD file is created in a tab at the back end of my script as such:

TRACE Create QVD file;

Store Sales into \\Qlikview\Qlikview_Files\QVD_Files\MasterSales.qvd;

TRACE Finishing building master sales file;

TRACE Drop Sales Table Now QVD Built;

Drop Table Sales;

But..... just before this tab in my file I have two tabs creating a calendar (provided to me by someone else) and they essentially create a whole load of additional fields that I use in the dashboard. For example fields such as; Sales.IsLastYear, Sales.IsCurrentMonth etc. These additional fields aren't in my QVD and (I think) that is my final (hopefully) problem.

Any ideas how I get these into the QVD?

Re: Section access to restrict data

Fri, 01 Nov 2013 20:19:04 GMT

Problem solved - the previous post above was a red-herring.

The answer was:

Problem now solved - thanks to the guys above that helped/made suggestions, all incredibly helpful and collectively got me to the finish line!