Section Access -hierarchy parent child

[jpsathya]

Hi,

I am trying to give the below section access, user 1 should be able to view his own data, user2 and user3 data.

User2 should see his own data and user3 data

user 3 should see his own data only.

Here is my code:

SECTION Access;
LOAD Upper(ACCESS) as ACCESS,
USERID,
Upper(USERID) as PERMISSION // This authorisation field been added here

FROM [lib://F_Facts_section.xlsx]
(ooxml, embedded labels, table is Sheet5);

SECTION Application; // Hierarchy making comes under section application
THierarchy:

HierarchyBelongsTo(USERID,Supervisor_id,Name,AncestorID,AncestorName)

Load * Inline [
USERID,Name,Supervisor_id
1,NA\AJ,1
2,NA\SJ,1
3,NA\LS,2

];

Hierarchy:
Load
Name as NAME,
//USERID,
AncestorID,
Upper(AncestorID) as PERMISSION

Resident THierarchy;
DROP Table THierarchy;
Fact:
load * inline
[
NAME,SALARY,PROJECT
NA\AJ,10000,QA
NA\SJ,5000,QIS
NA\LS,25000,QCT

];

EXIT SCRIPT;

Below is my section excel file:

ACCESS  USERID

ADMIN  NA\AJ

ADMIN   NA\LS

ADMIN  NA\SJ

ADMIN   NA\SA-ITS-JAN-EUREKA-PR

Can anyone take look at this code and let me know what is the error

[Mario_De_Felipe · Accepted solution]

Hi @jpsathya ,

the problem is that you are using USERID (string values) and AncestorID (integer values) for fetching PERMISSION values. 

You could try this:

THierarchy:

HierarchyBelongsTo(USERID,Supervisor_id,Name,AncestorID,AncestorName)

Load * Inline [
USERID,Name,Supervisor_id
1,NA\AJ,1
2,NA\SJ,1
3,NA\LS,2
];

mNameAncestor:
mapping
Load
USERID,
Name
Resident THierarchy;

Hierarchy:
Load
Name as NAME,
//USERID,
AncestorID,
Upper(ApplyMap('mNameAncestor',AncestorID)) as PERMISSION
Resident THierarchy;
DROP Table THierarchy;

Fact:
load * inline
[
NAME,SALARY,PROJECT
NA\AJ,10000,QA
NA\SJ,5000,QIS
NA\LS,25000,QCT

];

[jpsathya]

Hi Mario, 

Thank you for your response, used the below code  , association is between Anccestor name to USERID as Permission.

SECTION Access;
LOAD Upper(ACCESS) as ACCESS,
USERID,
Upper(USERID) as PERMISSION // This authorisation field been added here

FROM [lib://JANUS-Eureka-Medical.Insights/02.Input/F_Facts_section.xlsx]
(ooxml, embedded labels, table is Sheet5);

SECTION Application; // Heirarchy making comes under section application
THierarchy:

HierarchyBelongsTo(USERID,Supervisor_id,Name,AncestorID,AncestorName)

Load * Inline [
USERID,Name,Supervisor_id
1,NA\AJ,1
2,NA\SJ,2
3,NA\LS,1

];

Hierarchy:
Load
Name as NAME,
//USERID,
AncestorID,
Upper(AncestorName) as PERMISSION

Resident THierarchy;
DROP Table THierarchy;
Fact:
load * inline
[
NAME,SALARY,PROJECT
NA\AJ,10000,QA
NA\SJ,5000,QIS
NA\LS,25000,QCT

];