Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications


Dynamic CRM Map Reports - Creating a Customer Location Map

Post Author: Joe D365 |

Lately we've been creating Dynamics CRM map reports as a cool way of visualizing data. Using the SQL Reporting Services Map Wizard in Report Builder 3.0 or Visual Studio 2008/2010 you can quickly build a Map reports using CRM data.

Map integration has been a popular customization for several years and you will need some way of capturing Geo-data. CRM already has latitude and longitude fields that can be populated using JavaScript and an address verification web-service.

In my example I will be display the location of my Customers by adding a point layer to the map and using the SQL 2008 geography data type to supply our spatial data. Our SQL select statement will convert the latitude and longitude coordinates into a geography point.

CRM 2011 Map reports will typically have multiple layers depending on the data. In our example we will we use the US state map gallery for one layer and SQL Server spatial data for our point layer.

Launch Report Builder 3.0 and start a new report using the Map wizard. (Report Builder 3.0 is a free download and requires SQL 2008 R2)

First choose a map gallery and click 'Next'

CRM 2011 map reports - create a customer location map

Modify options as desired, click 'Next'

Select a map visualization, click 'Next'

Select to 'Add a dataset…', click 'Next'

Create your connection:

Design your query or edit as text and paste in mine:

SELECT name, customertypecode, address1_line1, address1_city, address1_postalcode, address1_county, address1_country, address1_stateorprovince, address1_latitude, address1_longitude, statecode, geography::Point(address1_latitude, address1_longitude, 4326) as SpatialLocation
FROM FilteredAccount
WHERE (statecode = 0) AND (address1_latitude is not null)

The key is: geography::Point(address1_latitude, address1_longitude, 4326) as SpatialLocation to capture the geo data.

Next match your state data (most likely your state data is the two character USPS abbreviation)

Select a color theme:

In my examples I'm removing the legends. Simply select and delete.

Not very exciting, but let's continue. Click the map twice to enable the Map Layers properties window.
Click the 'New Layer Wizard' button:

This time select SQL Server spatial query

Select the dataset we created earlier:

On the next screen our SpatialLocation column should be selected and we'll leave the Layer type as Point.
Optionally we can add a Bing maps layer, click 'Next'

Since we're simply displaying location we'll choose 'Basic Marker Map'

Select the marker type. Depending on how large your data set is you may play with different markers. More advanced technique could be to display a different marker type for type of customer or how amount of sales.

Click finish and run the report.
Continue formatting by adding a meaningful title. I also modified the tool tip property to display Customer name and city:

For the tool tip expression I entered:
=Fields!name.Value & " - " & Fields!address1_city.Value

Run the report and test your tool tip:

Save your report as a standard rdl and post the report in CRM and users may run as any other CRM report:

In a subsequent post I'll review more advanced techniques and features.

If this sounds interesting and you'd like to talk to a Microsoft Dynamics CRM expert at PowerObjects – give us a ping and we'd be happy to help. In the meantime, check out PowerMap, a PowerPack Add-on designed to let users choose an entity, choose a view, and plot individual records from that view all on one map!

Happy CRM'ing

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

One comment on “Dynamic CRM Map Reports - Creating a Customer Location Map”

  1. Hi,
    thank you for this good article.
    But how can I get maps for the EU or Asia?

    regards
    Matthias

PowerObjects Recommends