Wednesday, March 23, 2016

Maps in SSRS Reports : Showing the right visual for geographical data

As a CRM system contains plenty of interesting data, Microsoft Dynamics CRM offers several levels of reporting. Some, as views, charts or even integrated dashboards, as quite simple and can be created by users without any coding skill. But these first levels of reporting are often (always ?) insufficient for managers and directors. That's why Dynamics CRM offers the ability to create some custom SSRS (SQL Server Reporting Services) reports, that can be fully integrated inside the web application.

SSRS offers great reporting possibilities, and one of them is showing some data on a geographical map, that we will discuss today.

As a simple example, let's say you have categorized all your contact records by they residential US state. You have now a large amount of data, and you would like to display the amount of contacts, by state, on a US map, instead of a basic table. And of course focusing (using a color scale) on states that have the larger amount of contacts.

We will see now how to do this by building, step by step, our report.
Note : To follow the demonstration, you must be comfortable with SQL Server Data Tools for Business Intelligence, how to create datasets and manipulate layouts in SSDT for BI.
  • First, in SSDT for BI, create a new Reporting Services Project and a new Report (do not use the Report Wizard).
  • Add a new DataSource (your Dynamics CRM Organization Database if OnPremise, FetchXML connection string if Online) and a new Dataset on the Contact entity. Make sure you include the field State in your Dataset, that should look as below. Note that in my example, I used a Azure SQL Database instead of Dynamics CRM. The method is exactly the same, the resulting dataset should look like even with a CRM datasource.
  • The previous steps were just basics, here comes the interesting part. On the toolbox, drag and drop the item Map on your report :
  • By adding the map, a wizard is automatically displayed. On the first screen, choose the type of map you want to add. The first choice (that I choose in my example) allows you to pick a map in an embedded gallery. Only US maps are available. The second option allows you to import any shapefile you could have. You may have a look at the Wikipedia article about shapefiles, to know how they are built. If you want to display other maps than US ones, I recommend you to import an existing one, as you can find plenty of these free of download from the internet (for example, you can find french maps here, but you can find others elsewhere).
    Note : Shapefiles have a significant influence on the size of the final report (.rdl file), that can block them to be added to Dynamics CRM. If you have to use too big shapefiles, you should use Map Shaper, a free online tool useful to simplify your maps.
  • In our example, simply choose a US map, by State, and click Next:
  • Choose now the visualization of your map. Here, simply select Color Analytical Map, as below :

  • On the next screen, you will be asked to select the analytical dataset. Just select the dataset DS_Contacts you created before.

  • You will now select the match fields, the one on your spatial dataset (included in the shapefile) and the one in your analytical dataset (in our case, the contacts dataset). The wizard displays the fields contained in the shapefile and the ones contained in your dataset, just select the fields STUSPS and State, as below :

  • Finally, choose the right visualization for your map. You can select either fields contained in the map or in your dataset. Moreover, you can select an aggregate formula (Sum, Count, etc.) on your dataset fields. In our example, select the settings as below :

  • The map configuration is finished. You can (I let you explore by yourself) now customize the title, the colors, the legend, the color rules and labels, and preview your report, ready to be imported in Dynamics CRM :

It is just my own point of view, but I love it :).

No comments:

Post a Comment