OrgChart Now allows you to reference values from a spreadsheet.

Overview

Referenced values can be included in tables.

This is especially useful with multi-chart documents that contain a table of metrics/information for each division. Typically some of the metrics/information cannot be derived from the employee records used to create the chart.

As an example, the HR team has requested the following table be shown for each division.

Division Sales
Planning Cycle Q3 2016
Approved Headcount 20
Actual Headcount 50
Division Regulatory Affairs
Planning Cycle Q3 2016
Approved Headcount 55
Actual Headcount 50

The 'Actual Headcount' field can be dynamically calculated using a formula; however, the other values do not exist within the source data. Other solutions require that user manually update tables for every division in the chart. If there are more than 5 divisions, this can be both time consuming and error prone.

With OrgChart Now, you can create a reference table spreadsheet to manage metrics/information such as 'Approved Headcount' and 'Planning Cycle' fields. This makes refreshing your chart an order of magnitude less work than manual updates.

Reference Table Spreadsheet

The format of the reference spreadsheet is as follows. Each general reference value (e.g. Planning Cycle) has a row and each reference value for a division requires a row in the spreadsheet.

Key1 Key2 Key3 Value
Planning Cycle Q3 2016
Sales ApprovedHC 20
Regulatory Affairs ApprovedHC 55
Quality Assurance ApprovedHC 10
ApprovedHC 12

Default values can also be defined for each key level.

Key1 Key2 Key3 Value
* Unknown
USA * USA
USA California * USA-CA
USA California N USA-CA-North

Setup

  1. Open any multi-chart document (created from an external data source)
  2. Create a spreadsheet (in Excel) as described in the previous section (Divisions must match the divisions in the multi-chart document)
  3. Click on the FILE tab and select the Upload (or Files) option
  4. Create a folder called 'Reference' (if the folder does not already exist)
  5. Upload your spreadsheet to the 'Reference' folder
  6. Click on the REFRESH tab
  7. Click on the Refresh Properties button and then select 'Advanced Options'
  8. Check the 'Reference Table' option
  9. Enter the name of the spreadsheet (make sure to include the extension .xls or .xlsx in the name)
  10. Click OK
  11. Save your chart
  12. Refresh your chart (or close and open the chart) to load the reference table

Creating a reference table

  1. Select the EDIT MASTER tab
  2. Create a table (See the table topic for more details)
  3. Position on the page. Recommended method is to Pin the table in one of the corners (using the Pin tool)
  4. Using the Layout Editor to modify the table (remove the header and size the table to have 2 columns and 4 rows)
  5. Drag calculations or fields into the table has appropriate (e.g. Headcount calculation)
  6. Make sure a <Free Text> element is placed in all other cells
  7. Exit the Layout Editor when done

Referencing External Values

  1. Select the EDIT MASTER tab (if not already selected)
  2. Click on any <Free Text> cell and edit using the syntax described below:
    • [$Key1] - Resolve Key1 from the reference table (e.g. [$Planning Cycle] will resolve to 'Q3 2016')
    • [$Key1.Key2] - Resolve Key1.Key2 from the reference table
    • [$$Division.Key2] - Resolve the current Division.Key2 from the reference table (e.g. [$$Division.ApprovedHC] will yield 20 when the Sales Division is selected and 55 for Regulatory Affairs)
    • [$$topboxid.Key2] - Resolve the current TopBoxID.Key2 from the reference table. This is useful for referencing a value associated with the top box of a subchart (e.g. Approved Budget for a manager).
    • [$$topboxid.$Division.Key3] - Resolve the current TopBoxID.Division.Key3 from the reference table. This is useful for referencing a value associated with the top box of a subchart (e.g. Approved Budget for a manager within a division).

Updating the Reference Table

  1. Click on the FILE tab and select the Upload (or Files) option
  2. Download the reference spreadsheet (created in the Setup step above)
  3. Edit the spreadsheet as desired (e.g. Upload the Planning Cycle Date or Add Divisions or Update Approved HeadCounts…)
  4. Upload the spreadsheet to the 'Reference' folder (Replace the existing spreadsheet - make sure to use the same file name)
  5. Refresh your chart (or close and open the chart) to load the reference table