How to handle multi-level same-type hierarchical structures and aggregations

Question

This is not a pure development- or UI-question, rather a 'behavioral' question but I don't seem to find the correct StackExchange group to post it. So please don't down-vote me if this question is not appropriate, but rather point me to the correct group.

In my application I have a hierarchical structure of items that are all the same type. E.g. departments, where departments can belong to other departments.
I also have reports that show all departments with their characteristics, e.g. the amount of work done by each department, or the revenue generated by each department.
If a department contains other departments, the values of the sub-departments should be added to the value of the department itself.

E.g. suppose that we have departments A1, A2, B1 and B2, and departments A1 and A2 belong to department A0, and departments B1 and B2 belong to department B0, and suppose they all did 100 units of work, than the report would show this:

• A0: 300
• A1: 100
• A2: 100
• B0: 300
• B1: 100
• B2: 100

Problems begin if the user decides to perform aggregations on these departments. E.g. group all departments on their last digit:

• group 0: 600
• group 1: 200
• group 2: 200

This still seems quite logical, but what if an aggregation contains both top-level departments and low-level departments. E.g. departments grouped by the nationality of the department-leader (fictional example):

• group {A0, A1, B1}: 300 (so not adding A1 and A2 to A0)? or 500 (adding A1 and A2 to A0)? or 400 (only adding A2 to A0)?
• group {B0, A2}: 200 (not adding B1 and B2 to B0)? or 500 (adding B1 and B2 to B0)?
• group {B2}: 100

Things become even more complex if the user can filter out departments (e.g. don't take departments into account that contain less than 5 people).

Of course, we could still add a configuration option to the report where the user can indicate whether he wants sub-departments to be taken into account or not, but that seems moving the problem to the user.

Are there any known articles that handle these kind of problems?

Answer 1

In this case I would take on a different approach, and only allow reporting hours on the hierarchcal leaf level. That way you can always calculate upward in the tree no matter the depth of hierachy.

This way you can have any associations you like, since you only calculate the leaves of the tree.

Even if you redraw the lines of the calculations, they would still be valid - and the top node (with all leaves) always have the correct value no matter what the leaves beneath is calculated to.

Edit (values do exist on more than just the leaves)

^{download bmml source – Wireframes created with Balsamiq Mockups}

According to the comment, node values exist even higher up in the hierarchy. To solve this issue we need different types of values on each node; (1) the value of the node itself and (2) the calculated value of the node.

The calculated value (in red font color in the above image) is the sum of all the values of subordinate nodes + this node value. This technique requires more advanced programming, but it's quite straight forward to implement. After all - computers are very good at calculating.

Answer 2

Either you make an assumption about the user, or you decide for him. It depends on the context and its beosin. In this case, without knowledge of the context, I would decide for him, with a footnote indicating the choice of pages.

"All sub-department are automatically included in the result of a department", which gives the following solution:

• Group {A0, A1, B1}: 400,
• Group {B0, A2}: 400,
• (For example, do not take into account the departments with less than 5 people). : If A1 contains three people, A0 stills is always equal to 300.

Another track, consider A0 as two separate element, the tree vs. its result. Rephrase, offered the choice between:

• "The result of the team only A0"
• "The result of all departments of A0, A0 included"