Dual reporting visualisation using pivot tables and look-ups – OrgVue Portal

The method described gives clear, clean visualisation of dual reporting functionality by using 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:

Related articles