Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to produce a pivot table that shows who each task is assigned to and the % complete that task is.
What I'm actually getting is just the one name - so if there is a Task3 with Nigel against it it also shows it as allocated to Task 2 and TaskName. Is there a way around this? I need to be able to roll up the data for overall % complete which is why a straight table isn't sufficient but I can't find a away to show where more than one person is responsible for the subtasks?
Subtasks have been created using hierarchy:
Tasks:
Hierarchy ( TaskId,TaskParentTaskId,TaskName,'ParentTask')
I have tried mapping but the same issue occurs - any help or suggestions appreciated
Carol
It sounds like you're trying to undo the hierarchy you've created in the first place, but I don't quite understand what the expected outcome is in this case. Is it something like:
Task3 - Nigel && Geoff && Bob (as a concatenated single field)
or
Task 3 - Nigel | Geoff | Bob (in three different columns)
or perhaps,
Task3 - Nigel
Task3 - Geoff
Task3 - Bob
or something else entirely?
So the plan is that the tasks are in a hierarchy
Bob is responsible for the overall TaskName
Geoff is overall responsible for Task2
Nigel is responsible for Task3
In a flat table the person allocated to the task can be seen against the task but in a pivot table only one name is put against all 3 (assuming this is something to do with rolling up?)
Could you add the actual data and code you're using (the sample, not the full original, of course)? I'm still not quite following what steps you're taking or what result you're getting.
The data is combined in a few temporary tables but the hierarchy happens in the last table where everything is pulled together from the final temp table (so Load *) but these are the fields that are affected.
Hierarchy ( TaskId,TaskParentTaskId,TaskName,'ParentTask')
LOAD * Inline[
TaskId, TaskparentTaskId,TaskName,TaskParentTask,AssignedTo,
1, , TaskName, , Bob,
2, 1, Task1, TaskName, Geoff,
3, 2, Task2, Task1, Nigel
];
Okay, so what you're actually building is this:
Which means "Taskname" is associated with all three people, Task1 is associated with two people, and only Task2 is associated with a single person.
There's likely a number of ways to deal with this. I went with this one (Note: Dimensions have been set to not show nulls)
I'm not sure if that covers your use case since you mentioned rolling up completion percentages, but those aren't included in the data you've provided so I can't say.