Creating Custom SSRS Spatial Map Reports for Dynamics CRM 2011

A CRM system in an organisation is often the central place to store business data related to customers, sales, and markets, which are highly valuable for any business. The BI feature in Dynamics CRM 2011 is very powerful which allows developers or even end-users to create report tables or charts very easily. However,  it is better to visualise some types of business data against a geographical background, e.g, customers geographical distribution or sales geographical distribution.

This blog post goes through the steps to create a spatial map report (customers geographical distribution in UK) using SSRS for Dynamics CRM 2011. Please find the sample report from my Codeplex site.

image

image

1. Create a SSRS project in BIDS, and add a report.

2. Create a Data Source connecting to the Dynamics CRM organisation database, and create a Data Set to query the customer lists (in this example, I used Account entity) from the CRM database.

3. Convert UK postcode (stored as customer’s attribute in Account entity) to spatial data format type (latitude and longitude). Although the Account entity has fields to store latitude and longitude values of an address, few of users would actually input latitude/longitude into MSCRM. Instead, most of users prefer to store postcode in MSCRM to record the location of an account. However, the spatial data type supported by SQL Server 2008 R2 is converted from latitude/longitude instead of postcode. Therefore, we need convert postcode (in UK) to latitude/longitude using a UK postcode dataset. Alex Whittles has a blog post which suggests a number of UK postcode datasets, and also introduces how to convert postcode to geography data type and how to use the spatial data in SSRS report.

Below is the complete query for the dataset in this example (based on the query suggested by Alex Whittles).

SELECT p.postcode,
    geography::STPointFromText('POINT(' +
         CAST(MAX(p.longitude) AS VARCHAR(20)) + ' ' +
         CAST(MAX(p.latitude) AS VARCHAR(20)) + ')', 4326).STBuffer(2000)  AS Geog,
       Count(*)as [Count]
FROM [YOURSERVER_MSCRM].[dbo].[FilteredAccount] a
Inner join [UKPostcodeTable] p on  p.postcode =
  CASE WHEN
            CHARINDEX(' ', a.address1_postalcode)=0 THEN a.address1_postalcode
            ELSE LEFT(a.address1_postalcode, CHARINDEX(' ', a.address1_postalcode)-1)
           END
GROUP BY p.postcode

4. Add a Map to the report which consumes the spatial data from the dataset we just created.

clip_image002

clip_image004

5. Deploy the report we just created in BIDS on to MSCRM 2011.

 

6. Publish the report for external use

clip_image007

7. Get the URL of the deployed report (though web service of the report server)

8. Create a IFRAME which links to the report (don’t forget to hide the toolbar of the report viewer by adding ‘rc:Toolbar=false’ to the URL), and add the IFRAME to a dashboard.

And then, we have a spatial map showing on MSCRM 2011.

image

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: