9 Replies Latest reply: Feb 19, 2015 10:52 AM by Mario Morling RSS

    Section Access on Sheet Level

    Staffan Johansson

      Hi,

       

      Problem: Lets say I have 10 users from a AD-group, 5 of them should have access to a sheet.

      If I use a list with there NTNAME and all other things i Section Access. The list is only include the 5 that should have access.

      The problem is that the 5 that not should have access to the sheet even dont get access to the document.

      This is because they are not in the list with there NTNAME, is there a way to use "all other users"?

       

      I only know about the user that should have access, the other users is "hide" in AD-groups.

       

      SA.png

      This is my settings in document setting.

        • Re: Section Access on Sheet Level
          Bill Britt

          Hi,

          This will not work if you are using groups. When using groups it would mean all members of the group would have access.

           

          Bill

          • Re: Section Access on Sheet Level
            Jebamalai Dass

            You can also use the sheet properties and select Condition show.

             

            here you can create the condition based on the users.

             

            Rest all will not be able to view.

             

            Try and let me know

              • Re: Section Access on Sheet Level
                Staffan Johansson

                Hi Jebamalai,

                 

                thats exact the solution we are running today. This is a very consultant friendly solution but the customer will have possibility to manage this from a list themself. So I moved all user from the Conditional show into the list. But the problem is when user who's not in that list dont get access to the application.

                • Re: Section Access on Sheet Level

                  Hi!

                   

                  Sounds interesting, the "Condition show"-solution. Do you have an example for how to write the condition there?

                  I'm quite new in QV and still have some problem with their syntax.

                   

                  As a admin, which solution is easiest to admin? Sheet-condition-based or an table in the db (if i got that to work).

                    • Re: Section Access on Sheet Level

                      Thanks for information!

                      This is how i solved it. It seems to work, so far at least. I have few users to handle.

                       

                      TABLES IN XLS
                      QV_Ek_Per

                       

                      ACCESS

                      NTNAME

                      ANS_ID

                      BH_GRUPP

                      ADMIN

                      \mario00

                      1100

                      BH_ALL

                       

                      ANS_ID - different key-values for what they can see in QV. Multiple AND_ID for 1 user needs multiple rows.
                      BH_GRUPP - rules of what sheets they have access to (according to table QV_Ek_Per_Sheet).
                      All rows for 1 user must have the same BH_GRUPP-value.

                       

                      QV_Ek_Per_Sheet

                       

                      BH_GRUPP

                      SH02

                      SH09

                      SH07

                      SH12

                      SH19

                      SH20

                      BH_ALL

                      1

                      1

                      1

                      1

                      1

                      1

                      BH_EJ_MV

                      1

                      1

                      1

                      1

                      1

                      0

                       

                      SH02 etc is COLS named after Sheet-ID, just so it will be easier to know what it is.
                      1= Access to sheet, 0= hidden sheet
                      All USERS just have 1 BH_GRUPP in QV_Ek_Per even if thay multiple rows with different ANS_ID

                       

                      SCRIPT

                      SECTION ACCESS;

                       

                      LOAD
                      UPPER(ACCESS) AS ACCESS,
                      UPPER(NTNAME) AS NTNAME,
                      UPPER(ANS_ID) AS ANS_ID,
                      UPPER(BH_GRUPP) AS BH_GRUPP
                      FROM
                      [...\Excelfiler\Behorighet2.xls]
                      (biff, embedded labels, table is QV_Ek_Per$);

                      SECTION APPLICATION;

                      BH_GRUPP:
                      Load BH_GRUPP,
                      SH02,
                      SH09,
                      SH07,
                      SH12,
                      SH19,
                      SH20
                      FROM
                      [...\\Excelfiler\Behorighet2.xls]
                      (biff, embedded labels, table is QV_Ek_Per_Sheet$);

                       

                      SHEET PROPERTIES (this example from Sheet SH20)
                      Show sheet
                      Condition marked with condition   “=SH20=1”

                       

                      So, next week I will try it live, would be intresting to see if it works ous as I wish. =)

                  • Re: Section Access on Sheet Level
                    Joseph Simmons

                    Hi Staffan,

                     

                    have a look at this thread, should help with sheet level section access

                    http://community.qlik.com/docs/DOC-4435

                     

                    hope that helps

                    Joe

                    • Re: Section Access on Sheet Level
                      Avinash R

                      Hi Staffan,

                       

                      Follow the below approach.

                      First load 10 user data from the DB or the actual source with NTNAME

                      then for the sheet access load the list of user who want access from an excel with the value 1 and in section application use this to get the sheet access. Please try the below code.

                       

                      ODBC CONNECT TO database

                       

                       

                      SECTION Access;

                      LOAD Upper(if(ADMINISTRATOR='true','ADMIN','USER')) as ACCESS,

                         

                          Upper("EMPLOYEE_USERID") as NTNAME

                          ;

                      SQL SELECT *

                      FROM  table ';

                       

                       

                      SECTION Application;

                      LOAD Upper(USERID)as NTNAME,

                           SHEET

                      FROM

                      excel;


                      now use this sheet condition in the sheet properties to hide and show on the basis of the user login

                        • Re: Section Access on Sheet Level
                          Staffan Johansson

                          Hi All,

                           

                          I actually solved this, I will try to explain how I do this.

                          First of all, I have 14 different AD groups that I reduce and distribute this application to. Some of the user should have access to a specific sheet. It could be from 0-all in these 14 AD groups.

                           

                          The first thing I did was to read all AD members from all 14 AD groups into a QVD file. I read this direct from AD database. I flag these users with a '0' by default as sheetaccess. We also create a AD group that should have access to the sheet. I read these from AD database and flag them as '1' as sheetaccess. In the script where I did this I now do some join,concatenate and group by so I got a distinct member with either a 0 or 1, because all sheetmebers will have both 0 and 1 first. In the script I also tell witch of the users that should be Admin or Users. I use that in the Section Access later. Now I have a qvd file that contain all my User, I now read this as a Section Access:

                           

                          Section Access;
                          LOAD
                            ACCESS, //User or Admin
                               upper('Domain\'& NTNAME) as NTNAME

                              
                          FROM
                          $(QVDPath)SheetAccess.qvd
                          (qvd)
                          where 1=1;

                          SECTION Application;


                          SheetSequrity:
                          LOAD

                          upper('Domain\'&NTNAME) as NTNAME,
                              SHEET,  //1 or 0
                          FROM
                          $(QVDPath)SheetAccess.qvd
                          (qvd)
                          where 1=1 and NTNAME <> 'QlikService';

                           

                          QlikviewService is the account that runs the qlikview services. When the distribution from QMC is done Qlikview "belives" that this is the only account that should access the application. You can find this in an other tread here at the community.

                           

                          In the Document Properties --> Opening i tick Initial Data Reduction Based on Section Access and on the sheet conditional on the specific sheet  =sum(if(upper(OSUser())=NTNAME,SHEET))

                           

                          Now when a user is access the application (the distributed one he has access to) it compare the NTNAME and OSUser and see if he is a 0 or 1 and gives access to the sheet.