Section Access from SQL with Automatic Wildcard and Also Mimic'ing Access

    This was based on a recent requirement to automate section access from a SQL database, but also allow some users to Mimic the access of others. I am not sure how helpful this is as I am sure it must have been covered before, but you never know!

    The posts are on my blog as well:

     

    http://qlikanddirty.com/2017/03/29/section-access-from-sql-and-automating-wildcard-access/

     

    http://qlikanddirty.com/2017/03/29/mimicing-section-access/

     

    While writing this I was able to code a method whereby a user can be given * access and it will show all values.

    Do NOT try and use section access on a key column (one qlikview uses to link on) while using the included QV code.

     

     

    SQL Setup

     

     

    The setup is simple, create a table with the following:

     

     

    Dashboard: Full path to the dashboard ON THE SERVER

    Username: domain\username in UPPERCASE

    ControlColumn: The name of the column we are controlling, case sensitive

    ValueValues: comma separated list of values valid for that user, * means all

    QVAccess: Should always be USER (uppercase)

    Notes: as required

    Id: id (identity column)

     

     

    Note that I also added a constraint on here to stop people creating duplicate data:

     

    CREATE TABLE [qlikview].[QlikviewSectionAccess](
      [Dashboard] [VARCHAR](250) NOT NULL,
      [Username] [VARCHAR](100) NOT NULL,
      [ControlColumn] [VARCHAR](100) NOT NULL,
      [ValidValues] [VARCHAR](MAX) NOT NULL,
      [QVAccess] [VARCHAR](5) NULL,
      [Notes] [VARCHAR](MAX) NULL,
      [id] [INT] IDENTITY(1,1) NOT NULL,
    CONSTRAINT [QlikviewSectionAccess_id] PRIMARY KEY CLUSTERED
    (
      [id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY],
    CONSTRAINT [ucQlikviewSectionAccess] UNIQUE NONCLUSTERED
    (
      [Dashboard] ASC,
      [Username] ASC,
      [ControlColumn] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    
    

    Here is the full Qlikview code, the comments should explain what it is doing

     

     

    //Set the variable to be our full doc path (matches the section access table)
    let v_filename =documentpath();
    
    
    //load the columns which are controlled for this document
    tmp:
    SELECT DISTINCT controlcolumn
    from qlikview.qlikviewsectionaccess
    where dashboard ='$(v_filename)';
    
      Section Access;
    //set up dummy table with no data which we can concatenate too later
    SATEST:
    LOAD * INLINE [
    NTNAME,ACCESS
    ];
    
    //set a variable to loop over the number of control columns we have
    let v_number_controls = FieldValueCount('controlcolumn');
    
    //start the columns loop
    for f=1 to $(v_number_controls)
    
      //load the control data for our column
      let v_access_column= FieldValue('controlcolumn',$(f));
      Concatenate(SATEST)
      LOAD NTNAME,ACCESS,subfield(ValidValues,',') as accesscontrolcolumn_$(f);
      SELECT UPPER(Username) as NTNAME, UPPER(QVAccess) as ACCESS, ValidValues
      from qlikview.qlikviewsectionaccess
      where dashboard ='$(v_filename)' and controlcolumn='$(v_access_column)';
    
      //now we need another loop to load all the possible values for the column
      //this is so the * qualifier works for people who should see everything
    
      //so count the number of field values
      let no_Rows = FieldValueCount('$(v_access_column)');
    
      //start the loop
      for i=1 to $(no_Rows)
    
      //write the data into our SA table, with dummy as the NT name and access
      let v_Value=FieldValue('$(v_access_column)',$(i));
      Concatenate(SATEST)
    
      LOAD 'DUMNMY' as NTNAME ,'DUMMY' as ACCESS,'$(v_Value)' as accesscontrolcolumn_$(f)
      AutoGenerate 1;
      //loop next field value
      next i;
    
      //rename the control field to the correct fieldname
      RENAME FIELD accesscontrolcolumn_$(f) to '$(v_access_column)';
    
    //loop into the next column we are controlling
    next f;
    
    // section application
      Section Application;
    
    drop table tmp;
    
    
    

     

     

    If you want to use the wildcard function then remember you mustn’t check the strict exclusion

     

     

     

     

     

     

    This is a follow-on from my previous post on loading section access from SQL

    http://qlikanddirty.com/2017/03/29/section-access-from-sql-and-automating-wildcard-access/

     

     

     

    What we will look at now is how you can create a system to mimic what other people can see, note because section access is applied in the document, users will still only be able to see their valid values. So if they choose someone with more values, it won’t matter. Our use case for this is that the users who are using this functionality are supersuers who can see all values.

     

     

    Note at the end of the previous post we dropped the tmp table, remove this line of code if you want to use this mimic setup.

     

     

    This is the code used in the Qlikview script:

     

     

    //Do some funky magic to allow people to 'view as'
    
    //Note this is controlled with field triggers on the MIMICNAME field which need to
    //be updated if any more columns are controlled in the future
    AcessMimic:
    LOAD * INLINE [
    MIMICNAME
    ];
    //set a variable to loop over the number of control columns we have
    let v_number_controls = FieldValueCount('controlcolumn');
    
    //start the columns loop
    for f=1 to $(v_number_controls)
    
      //load the control data for our column
      let v_access_column= FieldValue('controlcolumn',$(f));
      Concatenate(AcessMimic)
      LOAD NTNAME as MIMICNAME,subfield(ValidValues,',') as mimiccontrolcolumn_$(f);
      SELECT UPPER(Username) as NTNAME, ValidValues
      from qlikview.qlikviewsectionaccess
      where dashboard ='$(v_filename)' and controlcolumn='$(v_access_column)';
    
    
      //rename the control field to the correct fieldname
      RENAME FIELD mimiccontrolcolumn_$(f) to '$(v_access_column)_mimic';
    
    //loop into the next column we are controlling
    next f;
    
    drop table tmp;
    //End that funky magic
    
    

     

     

    o this is quite straight forward, we’ve just loaded our section access data and loaded the usernames into a column called MIMICNAME

     

     

    So we can now create a list box based on this field, and then add the necessary field triggers to it.

     

     

    For example in our live dashboard we have three fields which are controlled with section access, so I need to set up three triggers on the MIMICNAME column.

    Firstly clear all three, then make the relevant selections:

     

     

    Capture.PNG

     

     

    The code used to make the selection is simply:

     

     

    ='("'&Concat(Distinct ManagingDirector_mimic&;'"|"')&'")'
    
    

     

     

     

    Simples!