I normally like to include City and Country fields on the Views for Opportunities and Cases, to enable an easy export to Excel for PowerBI mapping. In CRM 2013, we could add the City and Country of the related Account to the view of Opportunities by using the ability to add fields from parent entities to a view. The problem was that this lead to wide clumsy column headings like “Address 1: City (Account)”:
Apart from the cosmetic problem here, the potential Customer could also be a Contact – so we may need to add the same two fields for Contact. This would lead to two pairs of City and Country fields, which is not easy to consume in PowerBI.
Using the new Calculated Field capability in Dynamics CRM 2015, you can sidestep these problems elegantly with the following steps:
- Create a new City field on Opportunity and specify the Field Type as ‘Calculated’:
- Click Edit to add the following Condition and Action for Opportunities related to an Account:
This effectively specifies that the calculation retrieves the ‘address1_city’ field from the parent Account record.
- Add a second Condition and Action for Opportunities related to the parent Contact record:
- Do the equivalent for the Country field: create a new Country record on Opportunity, specify the field type as ‘calculated’, then edit the calculation to retrieve the ‘address1_country’ field from both the parent Account and Contact fields.
- Edit Views to include the City and Country fields. Our subsequent view looks much better:
- Click on ‘Export to Excel’ and specify ‘Static Worksheet with Records from all pages in the current View’.
- In Excel, save the generated Excel file as an Excel Workbook to enable PowerMaps.
- On the Insert menu, click Map. Follow the Wizard to define your map, first identifying City and Country as Geographical indicators, and then selecting to map at the City level:
- Drag and drop from the field list to set the Height to the Sum of Est. Revenue, the Category to Probability and the Time dimension to Est. Close Date:
This yields a great animated visualisation – I often end my demos of Dynamics CRM on a high note with this.