I have a dashboard for our audit dept that contains management hierarchies that don't always play nice together. There is a team of 8 or so executives that manage different aspects of the dept.
The dashboard includes:
Timesheet data for every employee in audit - tracking audit and non-audit tasks
Audit data that relates to audit tasks only
Entity data - describes the entity (or entities) being audited
Every employee reports to a team and each team rolls up to a executive on the executive team. Audits are distributed amongst the execs, but not all work done on an audit is done by employees that necessarily roll up to the exec who owns that audit. Likewise, entities are assigned to execs, but the audits are not carried out necessarily by execs who own the audit AND the entities being audited.
As a result, I wind up having three separate hierarch tables for execs, Audit_Exec, Employee_Exec and Entity_Exec. It would be nice for the Exec's to go into the dashboard, select there name once and navigate the various sheets always seeing the data associated with their employees/audits/entities, while still being able to look at their peers data as well by selecting a different exec name. I can't set all three table filters to the same name because as stated above, sometimes audits cross teams and entities. So it really depends on what sheet you are on as to which filter should be applied. If you are looking at general timesheet data, Employee_Exec. If you are looking at Audit data, Audit_Exec, etc.
If I am on a timesheet sheet and select Tom as the Employee_Exec and then navigate to another sheet that shows Entity data, the filter will restrict the data only to the entities that the employees that work for Tom have charged timesheet time to, which is not necessarily what Tom want's to see. He wants to see ALL his entities.
My initial approach was to create sheet events (triggers) that would lock the filters that pertain to the current sheet, clear all other filters and then unlock the fields that were locked. However I'm thinking that perhaps the Alternate States route might be a better choice if I understand them correctly. In essence I could have three alternate states and assign them to the sheets they pertain to. Selections in one state do not cross over to another state, but they are also not cleared when the user navigates across states and back again to his original state.
I thought about using a variable too, say vExecName, setting the appropriate filter in each sheet to the variable value in a trigger. but then I would have to make sure that one of the three filters was set to Tom, let's say and only Tom before I can make the assumption the user might want to set the new filter to Tom, and then clear the old filter to avoid conflicting exec filter issues.
Any ideas would be most welcome. Also, if I go the alternate state route, (this would be my first time) how much complexity does it add to all my set analysis expressions, etc.? Is it too much pain to make it worth my while?
