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?
