Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Section Access working only locally

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.

Tags (2)
1 Solution

Accepted Solutions
msheraton
Contributor III

Re: Section Access working only locally

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;

10 Replies
Not applicable

Re: Section Access working only locally

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

Not applicable

Re: Section Access working only locally

Noone wants to try his luck with this issue?

er_mohit
Honored Contributor II

Re: Section Access working only locally

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

msheraton
Contributor III

Re: Section Access working only locally

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

msheraton
Contributor III

Re: Section Access working only locally

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;

Not applicable

Re: Section Access working only locally

Mr Mark,

Sorry for late answer, I was on vacation

You are a genious. Thank you so much this really works!

BR,

Kuba

Not applicable

Re: Section Access working only locally

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

msheraton
Contributor III

Re: Section Access working only locally

Kuba please can you mark my answer as correct.

PS

Hope you had a nice vacation, I need one!

msheraton
Contributor III

Re: Section Access working only locally

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)

Community Browser