Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
rmainhart
Contributor II
Contributor II

Section Access or REDUCTION table with multiple criteria (OR)

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!

Labels (1)
0 Replies