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.

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

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

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

1. Publish the report for external use

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

2. 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.

• SSIS/SSAS/SSRS/MDX