Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a data set where one table stores courses and the other table stores directors/managers. Managers just have direct reports and Directors have direct and indirect reports. Directors have managers as their direct reports and the managers direct reports are the directors indirect reports. I need to create a pivot table that shows this full rollup for directors down to their managers direct reports.
Director/manager table: (Justin is the director with Liz as a manager)
DIRECTOR MANAGER NAME | REPORTEE ID | REPORTEE NAME | REPORTEE TYPE |
Justin | 111 | Liz | Direct |
Justin | 222 | Jen | Indirect |
Liz | 222 | Jen | Direct |
Fact Table:
REPORTEE ID | EMPLOYEE NAME | COURSE | GRADE |
111 | Liz | 101 | 99 |
222 | Jen | 102 | 99 |
View I want in pivot table for director
Direct Report | Indirect report | Course | Grade |
Liz + | Jen | 102 | 99 |
Hi,
First of all, it would be helpful to see your script.
Anyways, I'm not sure that I understand the problem here. Just make a pivot table, dimensions Direct Report, Indirect report, Course, and an expression of something like "firstsortedvalue(Grade, [Indirect report], 1)".