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.
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’
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
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!