topic Re: Section access with two reductions in App Development

Section access with two reductions

migueldfr_23 — Wed, 22 Jan 2025 17:12:17 GMT

Hello everyone

Hope everyone find well

I am facing with an issue regarding the section access topic.

1. I find all the M-cold name in the active directory

// Cargar la jerarquía de managers desde la fuente principal LOAD UPPER(manager_email) AS m_email, worker_id; [hcm_hierarchy]: SELECT * FROM Aws"hr"."hierarchy"; // Conectar al origen LDAP LIB CONNECT TO 'LDAP'; // Cargar datos del Active Directory directamente sin usar bucles ni variables ActiveDirectory: LOAD UPPER(mail) AS m_email, 'M-COLD\' & UPPER(sAMAccountName) AS USR; SQL SELECT mail, sAMAccountName FROM 'LDAP/INTERNAL'; // Unión izquierda entre la jerarquía de managers y la tabla de Active Directory LEFT JOIN (hcm_hierarchy) LOAD UPPER(m_email) AS m_email, UPPER(USR) AS USERID RESIDENT ActiveDirectory; // Crear tabla temporal con los datos combinados HIERARCHY_AD: NoConcatenate LOAD m_email, worker_id, USERID RESIDENT hcm_hierarchy; // Eliminar tablas intermedias DROP TABLE hcm_hierarchy; DROP TABLE ActiveDirectory;

2. Secondly, I am building the section access

USERS: LOAD * Inline [ ACCESS,USERID,REDUCTION ADMIN,INTERNAL\SA_SCHEDULER,* ADMIN,M-COLD\QSSERVICE,* ]; // Load the access list from the Excel file (specific to HR) ACCESS_LIST: LOAD UPPER(ACCESS) as ACCESS, UPPER(NTNAME) AS USERID FROM [lib://FILES.01.SOURCE/Visibility/Visibility_HR_Workforce.xlsx] (ooxml, embedded labels); // Load hierarchy information (from Active Directory or another source) HIERARCHY_AD1: LOAD UPPER(USERID) as USERID, worker_id as REDUCTION RESIDENT HIERARCHY_AD; // Ajusta según tu fuente de datos // Join the access list with the hierarchy data to associate users with their REDUCTION INNER JOIN (ACCESS_LIST) LOAD USERID, REDUCTION RESIDENT HIERARCHY_AD1; // Limpia la tabla temporal de jerarquías DROP TABLE HIERARCHY_AD1; // Combinar la lista de usuarios con la lógica de ADMIN CONCATENATE (USERS) LOAD ACCESS, USERID, IF(ACCESS='ADMIN', '*', REDUCTION) as REDUCTION RESIDENT ACCESS_LIST; // Limpia la tabla de lista de acceso //DROP TABLE ACCESS_LIST; // Section Access: Carga la tabla final en la sección de acceso SECTION ACCESS; LOAD ACCESS, USERID, REDUCTION RESIDENT USERS; // Limpia la tabla temporal de usuarios DROP TABLE USERS;

But I have this structure on the file, so I have to adjust this script with this information.
Once I set the General Manager, it has to see only the people behind, ignoring the user profile, onces Role does not has anything, pay attention on the user profile.

ACCESS	NTNAME	USERPROFILE	ROLE
ADMIN	USER1	*
USER	USER2	EE01
ADMIN	USER3	*	GENERAL MANAGER
ADMIN	USER4	*

Thank you in advance

Re: Section access with two reductions

Ben_P — Thu, 23 Jan 2025 08:20:56 GMT

Can you combine USERPROFILE & ROLE into a single field, and use that for the access restriction?

Re: Section access with two reductions

migueldfr_23 — Thu, 23 Jan 2025 14:27:46 GMT

Thanks for answering.
I change the second logic to make it easier and redeable.

Even though, it does not work, I guess I am approaching to the goald.

USERS: LOAD * Inline [ ACCESS,USERID,REDUCTION, ROLE ADMIN,INTERNAL\SA_SCHEDULER,*, ADMIN,M-COLD\QSSERVICE,*, ADMIN,M-COLD\C-ALBERTM,*, ADMIN,M-COLD\C-MIOTTOA,*, ADMIN,M-COLD\C-ASTOLFA,*, ]; // Load Access List from Excel ACCESS_LIST: LOAD UPPER(ACCESS) AS ACCESS, UPPER(NTNAME) AS USERID, UPPER(USERPROFILE) AS REDUCTION, UPPER(ROLE) AS ROLE FROM [Visibility_HR_Workforce.xlsx] (ooxml, embedded labels, table is Sheet1); Left Join (USERS) LOAD USERID, worker_id as REDUCTION_by_ID Resident HIERARCHY_AD; Drop Table HIERARCHY_AD; SECTION ACCESS; LOAD ACCESS, USERID, IF(ROLE = 'GM', REDUCTION_by_ID, REDUCTION) AS REDUCTION Resident USERS;

As you said before, I get the result of that table with only three columns.

Does it make sense ?

Thank you

Re: Section access with two reductions

Ben_P — Thu, 23 Jan 2025 14:58:06 GMT

This might be of use: Data Reduction Using Multiple Fields - Qlik Community - 1474917

Re: Section access with two reductions

migueldfr_23 — Thu, 23 Jan 2025 15:16:45 GMT

Thttps://community.qlik.com/t5/Design/Authorization-using-a-Hierarchy/ba-p/1476319#comments

This will be the one, trying to understand and replicate in my case ...

Re: Section access with two reductions

migueldfr_23 — Fri, 24 Jan 2025 08:50:23 GMT

Hello,

Would you know how to set "view as" as is in Power BI? In order to check how would see that specific user

Thank you

Re: Section access with two reductions

Ben_P — Fri, 24 Jan 2025 09:04:03 GMT

The way I do this is to just remove the 'section access' line in the script, so that you load the access permissions table as a normal table. Then you can just filter for a specific user in this table to see how they'd see the app.