Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I am facing strange issue withh QV9 report. I have implemented section access for company report and it works. but only locally.
When I am publishing it, I can see all data in QV Access point.
What I've already checked:
- tickbox for "initial data reduction" is ticked in document properties
- credentials in data protection section on reload tab in QEMC are properly entered
- I properly checked "recipient information" field in Distribute -> Loop field in Document.
- I am doing complete refresh, and then I am putting saved file to Production folder
I have no other clues unfortunetely.
I will also paste security implementation, as maybe someone will find problem there:
SecurityTable:
LOAD
UPPER(AccountName) as LOGINID,
'USER' as AccessT,
If(IsNull(organization_id) or organization_id='','*',organization_id) as OrgIdT,
UPPER(If(IsNull(division) or division='','*',division)) as DivisionT,
UPPER(If(IsNull(SBU) or SBU='','*',SBU)) as SBUT;
SQL SELECT *
FROM DATABASE_DETAILS
Concatenate(SecurityTable)
Load * Inline
[LOGINID,AccessT,OrgIdT,DivisionT,SBUT
QV_USER,ADMIN,*,*,*];
//Inline is user configured in data protection section on reload tab in QEMC
SecurityTbl:
Load
*,
RowNo() as GROUPIDT
Resident SecurityTable;
Drop Table SecurityTable;
star is *;
Section Access;
Load
LOGINID as NTNAME,
AccessT as ACCESS,
GROUPIDT as LINK1,
GROUPIDT as LINK2,
GROUPIDT as LINK3
Resident SecurityTbl ;
Section Application ;
L1:
Load Distinct
OrgIdT as OrgId_Fltr,
GROUPIDT as LINK1
Resident SecurityTbl;
L2:
Load Distinct
DivisionT as Division_Fltr,
GROUPIDT as LINK2
Resident SecurityTbl;
L3:
Load Distinct
SBUT as SBU_Fltr,
GROUPIDT as LINK3
Resident SecurityTbl;
//OrgId_Fltr, Division_Fltr and SBU_Fltr are links to data shown in report (in other table they are also UPPER CASE)
I would greatly appreciate all sugestions.
I found your logic overcomplicated (or I'm just thick).
So I simplied it and I've tested it on my access point and it works fine. See the attached file, (login as ADMIN / ADMIN ).
Code is also here for ref
Data_temp:
LOAD * INLINE [
ID, Name, OrgID, Division, SBU
1, Frank, 1, Phones, A
2, Anton, 1, Phones, A
3, James, 1, Cars, A
4, Mark, 1, Phones, B
5, John, 1, Phones, B
6, Alex, 2, Phones, B
7, Frederic, 2, Cars, B
8, Kathie, 2, Phones, C
9, Lucas, 3, Cars, C
10, Peter, 3, Cars, D
11, Tom, 4, Computers, D
12, Adam, 4, Computers, D
];
Data:
LOAD * ,
Upper(OrgID) as LINK1
,Upper(Division) as LINK2
,Upper(SBU) as LINK3
RESIDENT Data_temp;
DROP TABLE Data_temp;
//GET REGULAR USERS FIRST
LOGIN_USERS:
LOAD Upper(Accountname) as USERID,
'USER' as ACCESS,
Upper(Accountname) as PASSWORD,
UPPER(If(IsNull(organization_id) or organization_id='','*',organization_id)) AS LINK1,
Upper(If(IsNull(division) or division='','*',division)) AS LINK2,
Upper(If(IsNull(SBU) or SBU='','*',SBU)) AS LINK3
FROM
[access_matrix.xls]
(biff, embedded labels, table is Arkusz1$);
// NOW APPEND ADMINS
Concatenate(LOGIN_USERS)
ADMIN_USERS:
LOAD * INLINE [
ACCESS, USERID, PASSWORD, LINK1, LINK2, LINK3
ADMIN, ADMIN, ADMIN, *, *, *
];
star is *;
Section Access;
//ADMINS
SECURITY:
load * Resident LOGIN_USERS;
// Populate LINK1,LINK2 & LINK3 with all avaiable values for *
Concatenate(SECURITY)
LOAD
'ADMIN' AS ACCESS
,'X' AS USERID
,'ASDASDSADWQRQSWDS' AS PASSWORD
,LINK1
,LINK2
,LINK3
Resident Data;
Section Application;
LINK1_TBL:
LOAD LINK1
,USERID AS USERID1
Resident LOGIN_USERS;
LINK2_TBL:
LOAD LINK2
,USERID AS USERID2
Resident LOGIN_USERS;
LINK3_TBL:
LOAD LINK3
,USERID AS USERID3
Resident LOGIN_USERS;
drop table LOGIN_USERS;
I am attaching sample Qvw file in which security implementation works perfectly fine locally bbut doeas not work on access point. Can someone kindly check it?
BR,
Kuba
Noone wants to try his luck with this issue?
try to do section access in notepad for admin, user and password then call that fields through inline load in qlikview
See the link
http://community.qlik.com/thread/74941
see the attached document
hope it helps
"When I am publishing it, I can see all data in QV Access point."
Which user are you viewing the access point as, ie if you're viewing it as an ADMIN, seeing everything is fine...
I found your logic overcomplicated (or I'm just thick).
So I simplied it and I've tested it on my access point and it works fine. See the attached file, (login as ADMIN / ADMIN ).
Code is also here for ref
Data_temp:
LOAD * INLINE [
ID, Name, OrgID, Division, SBU
1, Frank, 1, Phones, A
2, Anton, 1, Phones, A
3, James, 1, Cars, A
4, Mark, 1, Phones, B
5, John, 1, Phones, B
6, Alex, 2, Phones, B
7, Frederic, 2, Cars, B
8, Kathie, 2, Phones, C
9, Lucas, 3, Cars, C
10, Peter, 3, Cars, D
11, Tom, 4, Computers, D
12, Adam, 4, Computers, D
];
Data:
LOAD * ,
Upper(OrgID) as LINK1
,Upper(Division) as LINK2
,Upper(SBU) as LINK3
RESIDENT Data_temp;
DROP TABLE Data_temp;
//GET REGULAR USERS FIRST
LOGIN_USERS:
LOAD Upper(Accountname) as USERID,
'USER' as ACCESS,
Upper(Accountname) as PASSWORD,
UPPER(If(IsNull(organization_id) or organization_id='','*',organization_id)) AS LINK1,
Upper(If(IsNull(division) or division='','*',division)) AS LINK2,
Upper(If(IsNull(SBU) or SBU='','*',SBU)) AS LINK3
FROM
[access_matrix.xls]
(biff, embedded labels, table is Arkusz1$);
// NOW APPEND ADMINS
Concatenate(LOGIN_USERS)
ADMIN_USERS:
LOAD * INLINE [
ACCESS, USERID, PASSWORD, LINK1, LINK2, LINK3
ADMIN, ADMIN, ADMIN, *, *, *
];
star is *;
Section Access;
//ADMINS
SECURITY:
load * Resident LOGIN_USERS;
// Populate LINK1,LINK2 & LINK3 with all avaiable values for *
Concatenate(SECURITY)
LOAD
'ADMIN' AS ACCESS
,'X' AS USERID
,'ASDASDSADWQRQSWDS' AS PASSWORD
,LINK1
,LINK2
,LINK3
Resident Data;
Section Application;
LINK1_TBL:
LOAD LINK1
,USERID AS USERID1
Resident LOGIN_USERS;
LINK2_TBL:
LOAD LINK2
,USERID AS USERID2
Resident LOGIN_USERS;
LINK3_TBL:
LOAD LINK3
,USERID AS USERID3
Resident LOGIN_USERS;
drop table LOGIN_USERS;
Mr Mark,
Sorry for late answer, I was on vacation
You are a genious. Thank you so much this really works!
BR,
Kuba
Now I am struggiling with full access... when I implemented this to my report with live data, as a full access user I am missing lots of data... It is probably because use of *
How can we fix it?
BR,
Kuba
Kuba please can you mark my answer as correct.
PS
Hope you had a nice vacation, I need one!
You need to be more specific. How has the script being modified when integrated with yours? Who are you logging in as? Are they an ADMIN? What data is missing?
Can you upload your script ? (scramble data if it is sensitive)