Placeholder

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).