Organizational Chart (Worksheet)

The Organizational Chart link (to the right of the Grid/Graph/Split buttons) is active when the following conditions are met:

1) A hierarchical relationship has been established between records of a given table, using the govern keyword available from the Verb Mapper dropdown.

2) The current Worksheet displays at least one of the fields used to define the hierarchy relation.

For example, if the Verb Mapper is used to link EmployeeID to ReportsTo, for the Employees table of Northwind, the Worksheet can immediately be used to display the organizational chart of the Northwind company. First, we need to post a question that involves the employees, such as "Show the employees." Because the SQL translation includes a reference to the EmployeeID field (one half of the <EmployeeID / ReportsTo> link, the hierarchy link becomes active. Clicking the link brings up the Select Aggregate Operator window. Since this is an optional argument, we'll just click OK to generate the "chain of responsibility".

The Hierarchy view shows a datasheet, and a graphical representation of the tree. The records of the grid are listed with ReportsTo, EmployeeID and LastName fields, in order of most superior, with an employee's subordinates listed immediately beneath him. Of course, the tree display gives a more intuitive picture of the organization.

Note that the Organizational Chart link becomes underlined when the hierarchical view is created. The tree portion of the view can either be closed or hidden. To hide the tree, click the X icon immediately above and to the right of the panel displaying the tree. Although the tree disappears, the Organizational Chart link is still underlined, indicating that it's still available. Reclick the link to unhide the tree view. To close the view entirely, click the underlined link. This closes the tree view and sets the Organizational Chart link back to its initial state. It can now be clicked again, in order to reopen the Select Aggregate Operator dialog, possibly to choose alternative options.

The aggregate options are most useful when there's numeric, data or other value information displayed in the Worksheet along with the members of the hierarchy. Taking as our example, "Show the birth date of employees.", the SQL produced is: SELECT DISTINCT Employees.BirthDate , Employees.EmployeeID , Employees.LastName FROM Employees ; Now, in addition to a hierarchy field, we have a value field to work with.

Setting the Apply aggregate operator checkbox opens the Aggregate field and Aggregate operator selectors. One common and quite useful trick is to simply associate the Employee's value (BirthDate) with his own position in the tree. For this, we select BirthDate, and -- as the aggregate operator - "First"

The aggregate calculations can also be carried up the tree, for instance to see the youngest employee in each department, or, say, the next birthday in each department. (This would be quite a feat to program in SQL.) Let's walk through both examples. In each case, we'll be using the BirthDate field as the aggregate field. To find the youngest employee in each department, we choose Max as the operator -- that is, the maximum (most recent) BirthDate. The displayed tree now shows the "hierarchical maximum", the most recent BirthDate carried up along each branch of the tree. The BirthDate of the youngest employee in the company is displayed alongside of Fuller (the company Vice President), since as the highest-ranking employee in the database, he sits atop the tree.

Notice that if Fuller himself is not the youngest employee, then one of his immediate subordinates will have the same date associated with their name. In this case, it's Buchanan. This means that either Buchanan is the youngest employee, or one of his subordinates fits that description. Beneath Buchanan, we find Dodsworth, with the same assigned BirthDate. Since Dodsworth has no subordinates, this represents his own BirthDate, which -- sure enough -- is confirmed by the datasheet at left.

Let's repeat the process, this time choosing Next Anniversary as the operator. Now we get a tree showing the closest (future) calendar date that happens to be someone's birthday, again propagated upwards through the organization. When this example was run (shortly before Access ELF XP's release in April 2002), it became visible at a glance that Fuller should remind Buchanan to arrange for birthday greetings for his subordinate King, whose birthday on May 29th 2002 was fast approaching.

If this seems like a flashy but ultimately useless capabiliity -- you're not thinking hard enough. For instance, let's assume that the Northwind database contained salary information for each employee. (Or add a Salary: Currency field to Employees and try this for yourself.) Now how long does it take to get, not just the total salary budget for the company, but the salary totals broken out by department. How fast can you type "Show the salary of each employee", click Respond, click Organizational Chart, click Apply aggregate, click Salary, click Sum, and click OK? If it takes longer then 10 seconds, practice your typing skills!