Dual reporting visualisation using pivot tables and look-ups

The method described gives clear, clean visualisation of dual reporting functionality by leveraging standard OrgVue functionality.

In summary, these are the steps to be taken:

  • Define the primary and secondary manager IDs
  • Put the manager names and ids in lookup tables
  • Define new properties Primary manager name and Secondary manager name with lookups
  • Build a pivot using the primary and secondary manager names as axes: filter the table so only roles with dual reports show i.e. secondary manager id is not blank

The following is a worked example.

Step one: define the primary and secondary manager IDs

The following table is the basis of the worked example:

Employee ID Line Manager ID Role Name Region Department Regional Rpt ID
1   Managing Director Barbara Gray HQ Executive  
2 1 Regional Director Spencer Sinclair North Executive 5
3 1 Regional Director Joseph McCool South Executive 10
5 2 Sales Manager Thomas Lee North Sales 1
6 2 Engineering Manager Logan Price North Engineering 5
7 2 Distribution Manager Frances Bush North Distribution 5
8 3 Engineering Manager Sienna Miah South Engineering 10
9 3 Distribution Manager Elise Morris South Distribution 10
10 3 Sales Manager Omar Webb South Sales 1
11 1 Head of Sales Lora Williams HQ Sales 1
12 1 Head of Engineering Steven Bias HQ Engineering 11
13 1 Head of Distribution Sebastian Coates HQ Distribution 11

where Line Manager ID is the primary manager ID, and regional reporting ID the secondary

Import this data into the dataset - when pasting, ensure you chose option Tree (by IDs) which will look like this:

If you change to Tree View and switch Parent By between the two IDs, it should be possible to visualise the different hierarchies:

 

 

 

Step two: put the manager names and ids in lookup tables

The sample tables are Primary Manager and Regional Manager respectively

Line Manager ID Name
1 Barbara Gray
2 Spencer Sinclair
3

Joseph McCool

 

Regional Rpt ID Name
1 Barbara Gray
5 Thomas Lee
10 Omar Webb
11

Lora Williams

 

Create two datasets in the Lookup tab.

When copying in the data, ensure you chose option Flat Data.  These are what the table contents should look like:

 

Step three: define new properties primary manager name and secondary manager name with lookups

  • In the main dataset, you will need to ensure that Line Manager ID is a text property (from the property dialog).
  • In the property dialog for Line Manager ID in the main dataset, click on the eye until it says dataset, then select from the drop down list the correct lookup dataset.
  • This will bring in all properties from the lookup.  You can then select PrimaryManager in the property selector on the right under the guise of Line Manager ID:Name
  • Use the same method for Regional Rpt ID ... Regional Manager ... name property

Step four: build a pivot using the primary and secondary manager names as axes: filter the table so only roles with dual reports show i.e. secondary manager id is not blank

  • navigate to the Pivot Table view, configure the filter and apply

  • configure the table so it shows the dual reporting lines on the two axes

  • this is what the final table will look like:

 

 

Have more questions? Submit a request

Comments