Customer Forum

Report showing Organisations with no current Employees

Workbooks Support Posted: 2013-04-11 08:59

We were recently asked how to produce a report which shows Organisations which currently have no Employees associated to them. Please see below how to create this report:

Create a new blank report, based on Organisations. Add in a column for the Object Reference, plus any other columns regarding the Organisations that may be of interest, such as Organisation name.
Add a calculated column, called 'Employees', that uses the following formula
- 'GROUP_CONCAT( employees.object_ref SEPARATOR ', ' )'

In non-reporting language, the formula says 'Take all the Employees who are related to Organisations and put them all in one row, separated by a comma'.

The Report currently shows all Organisations and any Employees who are related, however we want to only show the Organisations without any Employees. To do this add a Calculated Criteria which states that 'Employees is blank'. This will now show all the Organisations without any Employees.
Finally you need to Summarise by 'Object Reference'.

You should end up with a report which looks something like the one below (click to enlarge).

As you can see, all of the Organisations shown don't have any current Employees.

Workbooks Support Posted: Mon, 12.08.2013 - 10:10

In next release of Workbooks, due out in late summer, you can build the same report by undertaking the following steps:

Create a new blank report based on Organisations. Within the Details views add a column for Object Reference and Organisation Name. Also add a column for Employee Reference (Employee > Object Reference) and call it 'Employee Ref'.

The details view now shows all employees and which Organisations are their Employers. A summary view can now be built to Concatenate Employees into a single line for each Organisation to see which Organisations don't have any Employees. To do this:

Select 'Add Summary View' and 'Add grouping column'. Group by 'Object Reference'. Also add a column for 'Organisation Name'.
Add a Calculated column and use the following formula:
- 'GROUP_CONCAT( PARENT('Employee Ref') SEPARATOR ', ' )'

In non-reporting language, the formula says 'Take all the Employees who are related to Organisations and put them all in one row, separated by a comma'.

Add a criteria which says that 'Employees is blank'. This will now show all Organisations without any employees.

You should end up with a report which looks similar to the one below (Click to enlarge).