Qlik Community

QlikView Documents

Documents for QlikView related information.

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

adamdavi3s
Honored Contributor

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!

Labels (2)
Version history
Revision #:
1 of 1
Last update:
‎03-29-2017 07:54 AM
Updated by: