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