Section Access from SQL and Automating Wildcard 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

Leave a Reply