Section Access

[#1]

Hi,

Please bear with me as I'm new to Section Access - I've been reading through as much as I can but I thought it'd be quicker to ask.

I've created a table that I want to use to limit the data that users can see in the report (see below).

SELECT ACCESS, USERID, PASSWORD, NTNAME, SERIAL, ACCESS_DEPARTMENT, ACCESS_DEPTCODE

FROM USERS_ACCESS

WHERE ORG = 'TEST REPORT'

Within this table I have got data like so (using the fields above):

ADMIN, *, *, JOE BLOGGS, *, *, *, TEST REPORT

USER, *, *, JOHN SMITH, *, *, *, TEST REPORT

USER, *, *, JOHN DOE, *, SALES, 10, TEST REPORT

USER, *, *, LISA JONES, *, ACCOUNTS, 11, TEST REPORT

Within the qlik report in Document Properties I have checked 'Initial Data Reduction Based On Section Access' and 'Strict Exclusion' and 'Prohibit Binary Load'.

In the Section Access section in Edit Script I need to link 'ACCESS_DEPARTMENT' from the table above to a field called Company_Department that is currently displayed in the report.

For example I would want JOE BLOGGS and JOHN SMITH to be able to access the report and view all departments as they have an asterisk in the ACCESS_DEPARTMENT and ACCESS_DEPTCODE fields. However I would then want JOHN DOE to only be able to access the SALES department and for LISA JONES to only be able to access the ACCOUNTS department.

Does that make sense?

I think my issue is with the Load bit of Section Access. Does this look right? Also I don't want anyone to be prompted for a UserId or Password.

Section Access;

Load ACCESS,USERID,NTNAME,SERIAL,UPPER(ACCESS_DEPARTMENT) as Company_Department,ACCESS_DEPTCODE;

SQL

SELECT ACCESS, USERID, NTNAME, SERIAL, ACCESS_DEPARTMENT, ACCESS_DEPTCODE

FROM USERS_ACCESS

WHERE ORG = 'TEST REPORT'

Section Application;

Thanks in advance.

[kusumanchir]

Hi Kevin,

Please follow these steps,

Section Access;

Load

ACCESS,

NTNAME,

UPPER(ACCESS_DEPARTMENT) as COMPANY_DEPARTMENT,

ACCESS_DEPTCODE;

SQL

SELECT ACCESS, USERID, NTNAME, SERIAL, ACCESS_DEPARTMENT, ACCESS_DEPTCODE

FROM USERS_ACCESS

WHERE ORG = 'TEST REPORT'

Section Application;

Table:

Load

COMAPANY_DEPARTMENT

...

;

Regarding the '*' in the section access table, which means he will not have access to all the values present in the model under that field.

Ex:

ACCESS,NTNAME,COMPANY_DEPARTMENT

ADMIN, JOE BLOGGS,  *

USER,  JOHN SMITH,  *,

USER,  JOHN DOE,  SALES,

USER, LISA JONES, ACCOUNTS

USER,  JOHN , 

JOE BLOGGS, JOHN SMITH will have access to company department (SALES,ACCOUNTS) but not all the company departments.

JOHN DOE will have access to only SALES dept.

LISA JONES will have access to only ACCOUNTS

But the last JOHN will have access to all the values present under the COMPANY_DEPARTMENT field.

Regards,

Rasly.K

[jonathandienst]

If you are using NT logins (no username/pw prompt) and not using a serial number, then you can leave those fields off the SA load:

SELECT ACCESS, USERID, PASSWORD, NTNAME, SERIAL, ACCESS_DEPARTMENT, ...

[Miguel_Angel_Baeyens]

Also NTNAME always uses the form DIRECTORY\USERNAME where DIRECTORY is your security directory as defined in the QMC > System > Setup > Directory Service Connectors or the AD if you are using NTFS authorization

[#5]

Thanks for all your replies.

Can someone just explain this for me?

Regarding the '*' in the section access table, which means he will not have access to all the values present in the model under that field.

Ex:

ACCESS,NTNAME,COMPANY_DEPARTMENT

ADMIN, JOE BLOGGS,  *

USER,  JOHN SMITH,  *,

USER,  JOHN DOE,  SALES,

USER, LISA JONES, ACCOUNTS

USER,  JOHN ,

JOE BLOGGS, JOHN SMITH will have access to company department (SALES,ACCOUNTS) but not all the company departments.

JOHN DOE will have access to only SALES dept.

LISA JONES will have access to only ACCOUNTS

But the last JOHN will have access to all the values present under the COMPANY_DEPARTMENT field.

If I want people to access ALL departments what do I need to have in the COMPANY_DEPARTMENT field in the table? Is it a *, a NULL or leave it blank?

From what I'm understanding if the users have an asterisk * in this field then they can only see the departments that have been added to this table (i.e. SALES and ACCOUNTS) but if another Department has not yet been added to this table such as I.T. then they won't be able to see any data for the I.T. department. Is that correct?

Once again thanks for your responses.

[kusumanchir · Accepted solution]

Hi Kevin,

In order to access all the departments the field COMPANY_DEPARTMENT needs to be left left blank in the section access table.

If '*' is present in the section access table, the user will be able to see the values that are specified for the other users.

For suppose, the COMPANY_DEPARTMENT field is holding 3 different values

SALES

ACCOUNTS

IT

Consider the same section access table from the earlier post,

JOE BLOGGS, JOHN SMITH will be able to see only SALES and ACCOUNTS . Because of '*' and those are the only two are mentioned in the section access table.

JOHN DOE will have access to only SALES dept.

LISA JONES will have access to only ACCOUNTS

JOHN is having blank under the COMPANY_DEPARTMENT field, he can view all the 3 COMPANY_DEPARTMENT (SALES,ACCOUNT,IT) , though IT is not present in the table of section access.

[#7]

Thanks Rasly K - I'll try it all out now.