6 Replies Latest reply: Aug 8, 2017 8:23 AM by Kevin Garforth RSS

    Section Access

    Kevin Garforth

      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.



        • Re: Section Access
          Rasly K

          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


          • Re: Section Access
            Jonathan Dienst

            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, ...



            • Re: Section Access
              Miguel Angel Baeyens de Arce

              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

              • Re: Section Access
                Kevin Garforth

                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.

                  • Re: Section Access
                    Rasly K

                    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.