As promised in my previous post, now I will describe the basic steps for integrating a map into a simple sales report. Again I am using Report Builder 3 as maps are available only there (unless you are not using some third party software or other functionalities). So before we start, there is a serious prep to be done. There are three options when it comes to choosing map source for the report. First you can take advantage of some preinstalled maps in a gallery, which provide the actual spatial information that to be matched against the report data (for example to match states by their names). The second option you have is to choose and ESRI shapefile which to integrate into your report (information on ESRI shapefile standard you can find here). The third option (which we will use in our case) is to use spatial information from SQL Server query and to lay it out on a cool map from Microsoft’s Bing.
So, as I mentioned, I will shortly describe option three, just to show you the cool stuff you can do with Report Builder. As we are using SQL Server as source for our spatial information, our query for the dataset must have a column, that contains spatial data. My query is a very simple one, containing two columns – one with aggregated value and one with geography data (how I got to geography data, you can check in my previous post here). After you got the query and sorted out the part with the spatial data, you can create a new report following next few easy steps:
1. Start a report with new Map Wizard
2. Select the third option for source of spatial data (of course after that step you should define the data source and the query for the report dataset)
3. The MAP – here are some interesting things worth mentioning. First of all there is the screen:
In the upper left part you will see the drop down box where you should choose the filed from your dataset, that contains spatial data. Usually the wizard finds it itself, so just make sure you have it :).
Then on the upper right part you will see the layer type. In our case we are using Point layer type and for the rest I will provide additional info in one of my future posts.
Now on the bottom part is the interesting stuff! There you can check the option for adding a Bing map which will automatically display a map from Microsoft Bing. On the top of that, you can choose from three types of map display – Road, Aerial or hybrid.
4. Choose a map visualization – I will use bubble map which allows me quickly to configure my map pointers to be shaped as circle and depending on the value of my aggregated field, to change its size (the bigger the aggregate, the bigger is the circle)
5. Configuring map pointers, theme and visualization
In the data field you should choose the field from the dataset that will be used for determining how big the bubble will be. In our case – the aggregated field from the dataset. Of course there are few other quick customizations possible, but for the purpose of my demonstration I will try not to get carried away :-).
When you are done, just click Finish and the map layer will be displayed in Report Builder 3 where you can play with some other customization (adding additional map layers, different pointers, remove some legends, change titles, etc.).
Now there is a screenshot of my final report (after playing with some properties myself)
I will continue exploring the world of map integration into reports and share my experience so stay tuned 😉