Placeholder

Customer Forum

How to Report on the Related Organisations of an Opportunity

Workbooks Support Posted: 2014-10-17 13:43

In the Organisations tab of an Opportunity, you can list related Organisations and their relationships to the Opportunity...

...data in which you can report on as follows.

Firstly, create a new Opportunities report and add the columns for each piece of information about the Opportunity you'd like to report on. I would recommend adding a column for Object reference as we'll be using that later in the report. 

To access the key related Organisations and relationship information, add the columns below.

Related Organisation name: Related Roles > Related Organisations > Organisation name.

Relationship: Related Roles > Relationship.

Note that I added a criteria Related Roles > Related Organisation > Organisation name is not blank, as this removes any untidy entries. To limit the results displayed, in this example I also set a criteria for Opportunity name starts with Atlantic. Adjust your criteria accordingly depending on which Opportunities you wish to display.

Now to tidy up the report, add a calculated field which concatenates both the Related Organisation name and Relationship columns together. Include ' - ' in your formula so that there is a neat separator between the organisation name and its relationship. 

Which yields the final Details view below.

Now it's time to add a summary view to increase readability.

Add a grouping column on the Object reference, then bring through the value columns you'd like to see on the summary view. To get the related Organisations onto a single row, use the GROUP_CONCAT function on the Org and Relationship column, remembering to include a ' , ' separator.

Which leaves us with our final report!