Section Access or REDUCTION table with multiple criteria (OR)

rmainhart — Fri, 02 Apr 2021 08:41:09 GMT

I need help, please with setting up Section Access and/or a REDUCTION table for a complex set of rules.

I've reviewed https://community.qlik.com/t5/Qlik-Design-Blog/Data-Reduction-Using-Multiple-Fields/ba-p/1474917 and https://community.qlik.com/t5/Qlik-Design-Blog/Basics-for-complex-authorization/ba-p/1465872 but I haven't yet sorted how to apply those examples to this data model.

First, the relevant tables and fields from the data model:

[Sales]:

Load

INVOICE_NUM,

MANAGER_ID as SALES_MANAGER_ID, // one MANAGER is credited with the entire sale

etc.

// A single invoice may have involved multiple salespeople, potentially with different managers, but there is always only one manager credited with the sale in the [Sales] table.

// All salespeople associated with the sale are credited in the [Sales_Salesperson] table.

[Sales_Salesperson]:

Load

INVOICE_NUM,

SALESPERSON_ID,

etc

// Each year, salespeople are re-assigned to different managers

[Salesperson_Manager]:

Load

SALESPERSON_ID,

MANAGER_ID as SUPERVISOR_ID,

YEAR,

CURRENT_ASSIGNMENT_IND, // = Y if this assignment is the salesperson’s current assignment

etc

Business need:

A manager must be able to view all historical sales data for the manager’s current salespeople, no matter which manager received credit for the sale in the [Sales] table. But the manager must not be allowed to filter to a different manager’s group of current salespeople. But if no filter is applied at all, the manager can see aggregate data (only) for the entire company.

A manager must ALSO be able to view all historical sales data for which that manager received credit in the [Sales] table, even if the sale was made by a salesperson who currently belongs to a different manager. But the manager must not be allowed to filter to a different manager’s historical sales. But if no filter is applied at all, the manager can see aggregate data (only) for the entire company.

(And, obviously, senior staff can view everything; this requirement is not an issue.)

I’ve attempted to set up a REDUCTION table to manage the permissions, but unfortunately this creates a circular reference:

[MY_SUPERVISOR_MANAGER]:

Load

MANAGER_ID as MY_SUPERVISOR_ID, // will be $hidden

MANAGER_NAME as SUPERVISOR_NAME // will be searchable dimension

;

[MY_SALES_MANAGER]:

Load

MANAGER_ID as MY_SALES_MANAGER_ID, // will be $hidden

MANAGER_NAME as SALES_MANAGER_NAME // will be searchable dimension

;

[REDUCTION]:

Load

MANAGER_ID as USERRIGHTS // USERRIGHTS will be loaded in Section Access (ACCESS, USERID, USERRIGHTS)

;

Join

Load

MANAGER_ID as SUPERVISOR_ID

;

Join

Load

MANAGER_ID as SALES_MANAGER_ID

;

// the above joins permit all users to access all records, because of requirement to see company-wide data in aggregate

// next we give MY_SUPERVISOR_ID only where USERRIGHTS and SUPERVISOR_ID match; this provides the structure to give specific SUPERVISOR_NAME filters to appropriate users

Join

Load

MANAGER_ID as USERRIGHTS,

MANAGER_ID as SUPERVISOR_ID,

MANAGER_ID as MY_SUPERVISOR_ID

;

// and now we give MY_SALES_MANAGER_ID only where USERRIGHTS and SALES_MANAGER_ID match, which gives specific SALES_MANAGER_NAME filters to specific users

Join

Load

MANAGER_ID as USERRIGHTS,

MANAGER_ID as SALES_MANAGER_ID,

MANAGER_ID as MY_SALES_MANAGER_ID

;

(If it is unclear, the circular reference involves SUPERVISOR_ID, SALES_MANAGER_ID, SALESPERSON_ID, and INVOICE_NUM).

How can I structure the REDUCTION table (or Section Access) differently, so that the business needs described above can be met?

Thanks for any assistance!

topic Section Access or REDUCTION table with multiple criteria (OR) in App Development

Section Access or REDUCTION table with multiple criteria (OR)