Good afternoon everyone,
I was just curious to see if anyone might be able to provide some insight into an issue I'm experiencing. I'm working a table for our HR department and they would like me to produce 4 rows of information based on leadership along with the number of employees in each category, i.e.
Leadership | # of Employees | Various other metrics / percentage fields tied to each level
VPs 36
Directors 141
Managers 508
Supervisors 3200
Our HR database tables are designed in such a way that each individual has a corresponding field for their own ID, along with VP_Id, Director_Id, Manager_Id, and Supervisor_Id (if applicable). I have gone ahead and come up with logic to group these IDs for the counts:
If(len(Trim(SUPERVISOR_SAPID)) = 0 and len(trim(MANAGER_SAPID)) = 0 and len(trim(DIRECTOR_SAPID)) = 0 and len(trim(EXECUTIVE_SAPID)) > 0 and left(POSITION_DESC, 3) = 'VP,', 1, 0) as VPFlag,
If(len(Trim(SUPERVISOR_SAPID)) = 0 and len(trim(MANAGER_SAPID)) = 0 and len(trim(DIRECTOR_SAPID)) = 0 and len(trim(EXECUTIVE_SAPID)) > 0 and left(POSITION_DESC, 3) = 'Dir', 1, 0) as DirFlag,
If(len(Trim(SUPERVISOR_SAPID)) = 0 and len(trim(MANAGER_SAPID)) = 0 and len(trim(DIRECTOR_SAPID)) > 0 and len(trim(EXECUTIVE_SAPID)) > 0, 1, 0) as MngrFlag,
If(len(Trim(SUPERVISOR_SAPID)) = 0 and len(trim(MANAGER_SAPID)) > 0 and len(trim(DIRECTOR_SAPID)) > 0 and len(trim(EXECUTIVE_SAPID)) > 0, 1, 0) as SupFlag
These accounts appear correct based off our organization chart, but I'm not entirely sure how to proceed from here (as far as making each leadership level rows instead of columns). If I simply use the counts or flags as dimensions they simply display in the table as:
VPS | DIRECTORS | MANAGERS | SUPERVISORS
which is about what you would expect, given the data set has these setup as columns. I'm thinking I might need a cross-table for this, but I would like to see if anyone else might have a different approach on this because I'm not sure if that will even work for what I'm trying to do. Any help or guidance you might be able to provide would be greatly appreciated.
Thank you
