Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I am trying to Implement Sheet Hide and show.
I have users from Active Directory. They have been assigned under QlikView_Read and QlikView_Write
Users under QlikView_Read can view only SHEET1 where He/She can't allow to add any objects or sheet whereas Users under QlikView_Write will have access to
SHEET1 and SHEET2. On SHEET2, user can add objects and can delete it.
Now I have few users who are not in AD and I want to add then from Excel.
Below is my Query:
AD_USERS:
SQL
SELECT
ACCESS,
NTNAME,
GROUPNAME,
EMPID
FROM FROM DBO.ADUSERS;
CONCATENATE(AD_USERS)
LOAD
'USER' AS ACCESS,
'ALL' AS NTNAME,
'ALL' AS GROUPNAME,
[Employee ID] AS EMPID
FROM $(vDimQVDStoragePath)\DIM_EMP_DATA.qvd(qvd)
WHERE NOT EXISTS (EMPID,[Employee ID] );
Section Access;
SECTION_ACCESS:
LOAD ACCESS,
NTNAME,
GROUPNAME AS %GROUPNAME,
EMPID AS %EMPID
RESIDENT AD_USERS;
CONCATENATE (SECTION_ACCESS)
LOAD ACCESS,
NTNAME,
GROUPNAME AS %GROUPNAME,
EMPID AS %EMPID
FROM
$(vQlikViewADMINUsersPath)
(ooxml, embedded labels, table is QlikViewADMIN_USERS);
Section Application;
DROP TABLE AD_USERS;
SHEET_ACCESS:
LOAD GROUPNAME AS %GROUPNAME,
SHEET1 AS %SHEET1,
SHEET2 AS %SHEET2,
SHEET3 AS %SHEET3
FROM
$(vQlikViewADMINUsersPath)
(ooxml, embedded labels, table is SHEET_ACCESS);
Dim_Employee:
LOAD
[Employee ID] AS %EMPID,
NAME,
DOB,
PROJECT
FROM $(vDimQVDStoragePath)\DIM_EMP_DATA.qvd(qvd);
Fact_Employee_Salary:
LOAD
[Employee ID] AS %EMPID,
Salary,
Incentive
FROM $(vDimQVDStoragePath)\FACT_EMP_DATA.qvd(qvd);
Now Suppose User Qlik/Shiven.Kumar is not available in AD Table and so AD_USERS will not have data related to Qlik/Shiven.Kumar
so what I am doing in section access I am adding this user from Excel sheet QlikViewADMIN_USERS
ACCESS NTNAME GROUPNAME EMPID
ADMIN Qlik/Shiven.Kumar ALL *
and excel sheet SHEET_ACCESS holds this data:
GROUPNAME SHEET1 SHEET2 SHEET3
ALL 1 1 1
QlikView_Read 1 0 0
QlikView_Write 1 1 0
After deploying this app to server, If I try to login with Qlik/Shiven.Kumar, I am allowed but I am not allowed to access this application.
Any users who is available in AD is abale to access this appplication.
What wrong I am doing?
Please notice that all the data in the Section Access table must be in upper case. This then has the implication that the data in the reduction field in the data model also must be in upper case.
Hi Shivendookumar,
Check these two.
Section Access: Strict Exclusion
Column level access using Section Access
Hope it will helpful.
Please notice that all the data in the Section Access table must be in upper case. This then has the implication that the data in the reduction field in the data model also must be in upper case.
Toni Kautto,
Thank you very much. Once I changed the below code and converted GROUPNAME and NTNAME to Upper Case and 'ALL' AS GROUPNAME to '*' AS GROUPNAME
It is working now.
AD_USERS:
SQL
SELECT
ACCESS,
NTNAME,
GROUPNAME,
EMPID
FROM FROM DBO.ADUSERS;
CONCATENATE(AD_USERS)
LOAD
'USER' AS ACCESS,
'ALL' AS NTNAME,
'*' AS GROUPNAME,
[Employee ID] AS EMPID
FROM $(vDimQVDStoragePath)\DIM_EMP_DATA.qvd(qvd)
WHERE NOT EXISTS (EMPID,[Employee ID] );
Section Access;
SECTION_ACCESS:
LOAD ACCESS,
UPPER(NTNAME) AS NTNAME,
UPPER(GROUPNAME) AS %GROUPNAME,
EMPID AS %EMPID
RESIDENT AD_USERS;
CONCATENATE (SECTION_ACCESS)
LOAD ACCESS,
UPPER(NTNAME) AS NTNAME,
UPPER(GROUPNAME) AS %GROUPNAME,
EMPID AS %EMPID
FROM
$(vQlikViewADMINUsersPath)
(ooxml, embedded labels, table is QlikViewADMIN_USERS);
Section Application;
DROP TABLE AD_USERS;
SHEET_ACCESS:
LOAD UPPER(GROUPNAME) AS %GROUPNAME,
SHEET1 AS %SHEET1,
SHEET2 AS %SHEET2,
SHEET3 AS %SHEET3
FROM
$(vQlikViewADMINUsersPath)
(ooxml, embedded labels, table is SHEET_ACCESS);
Dim_Employee:
LOAD
[Employee ID] AS %EMPID,
NAME,
DOB,
PROJECT
FROM $(vDimQVDStoragePath)\DIM_EMP_DATA.qvd(qvd);
Fact_Employee_Salary:
LOAD
[Employee ID] AS %EMPID,
Salary,
Incentive
FROM $(vDimQVDStoragePath)\FACT_EMP_DATA.qvd(qvd);
and Changed ALL to * in Below Excel Data
ACCESS NTNAME GROUPNAME EMPID
ADMIN Qlik/Shiven.Kumar * *
and excel sheet SHEET_ACCESS holds this data:
GROUPNAME SHEET1 SHEET2 SHEET3
* 1 1 1
QlikView_Read 1 0 0
QlikView_Write 1 1 0