Placeholder

Customer Forum

Reporting on the Organisations related to Opportunities

Workbooks Support Posted: 2013-04-04 14:43

We were recently asked whether or not it's possible to build a report that shows Organisations that have been related to Opportunities, which not only shows the Organisation's name but also their relationship to the Opportunity.

The answer is 'yes', and here's how:

  • Create a new blank report, based on Opportunities. Add in a column for the Object Reference, plus any other columns regarding the Opportunity that may be of interest such as the Opportunity Name, who it's assigned to and so on.
  • Add a calculated column that uses the following formula:
GROUP_CONCAT(IF( document_header_contacts.organisation.name IS NOT NULL,  CONCAT(document_header_contacts.organisation.name, ' - ',  document_header_contacts.document_header_contact_role), NULL) SEPARATOR ', ')
 

In non-reporting language, the formula says: 'If there is an Organisation related to the Opportunity, display that Organisation's name along with its relationship to the Opportunity (separated by a dash). If there is no Organisation related to the Opportunity, don't display anything. Then, take all the Organisations and their relationships and put them all on one row, separated by a comma [that's the GROUP_CONCAT function].'

  • Finally, in order to see one row of data per Opportunity, you need to summarise the report. Typically we'd advise using the Object Reference for this.

You should end up with a report that looks something like the one shown below (click to enlarge):

As you can see, OPP-1 and OPP-21 each have 3 different Organisations related to them, OPP-3 and OPP-4 have one each and OPP-14 isn't related to any Organisations. If you don't want to see rows for Opportunities that do not have relationships with Organisations, you could apply a criterion to eliminate those from your report.
Workbooks Support Posted: Mon, 12.08.2013 - 10:44

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 Opportunities. Within the Details views add a column for Object Reference and for related Organisations (Related Organisations > Organisation Name). Also, add a column to show the type of relationship between the Opportunity and the Organisation (Related Roles > Relationship).

The details view now shows all Opportunities, any Related Organisations and their type of relationship. A summary view can now be built to show a concatenated view of Organisations and their relationships to Opportunities. To do this:

  • Select 'Add Summary View' and 'Add grouping column'. Group by 'Object Reference'.
  •  Add a Calculated column which uses the following formula.

'GROUP_CONCAT(IF(  PARENT('Organisation name') IS NOT NULL,  CONCAT( PARENT('Organisation name'), ' - ',   PARENT('Relationship')), NULL) SEPARATOR ', ')'

In non-reporting language, the formula says: 'If there is an Organisation related to the Opportunity, display that Organisation's name along with its relationship to the Opportunity (separated by a dash). If there is no Organisation related to the Opportunity, don't display anything. Then, take all the Organisations and their relationships and put them all on one row, separated by a comma [that's the GROUP_CONCAT function].

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