Nowadays, the project that I am working on is keeping me quite busy and all the documentation work is driving me nuts (you wouldn’t believe me if I said I hate documentation ). But somehow, I do find time to respond to the steady flow of queries that keep coming to me. And today, when one of my colleagues asked me how to implement bubble heatmap in SSRS map reports, I couldn’t resist the temptation to try it out at home and blog it down.
For demonstrating the solution, I have downloaded a map of Africa along with some statistics on AIDS. Follow the steps below to implement the solution:-
1) Import the shapefile of Africa into the database (Read the first part of Import Shapefiles into SQL Server and Aggregate Spatial Data (Geometry) for more details). This is not a necessary step, but I find it easier to deal with spatial data in a table rather than reading from the shapefile at runtime or embedding it within the report.
2) For the demonstration purpose, I have created two datasets in BIDS – one which will give the statistics on AIDS and the other one which will return the spatial data from the database.
3) Now drag and drop a Map item from the toolbox and select the source of spatial data as SQL Server spatial query. Click Next.
4) Choose the dataset which has the spatial data and click on next.
5) In the next screen, the spatial field should automatically be detected and we can click on next straightaway.
6) Choose Color Analytical Map as the visualization technique and click next.
7) Choose the analytical dataset containing the statistics in the screen that comes and then click on next.
8) Specify the common field in the spatial and analytical dataset which would be used for linking the map and the statistics. In this case, it is the Country field. Click on next.
9) In the next screen, choose any of the option as we will soon be disabling the visualization in the next step. Click on Finish.
10) Click twice on the map and press F4 to bring the MapPolygonLayer properties. Now disable polygon color rule as shown in the image below
11) Right click on the map and select the Show Center Points option
12) Now, on right clicking the map again, the Center Point properties should be enabled and we can change the Center Point Color rule and Center Point Size rule.
13) After the above step and bit more messing up with the aesthetics, we end up with the final end result as shown below
I am writing this post well past my bed time and so if I have missed out any vital steps, feel free to ask them in the comments section. I should be more than happy to detail them out for you. Time to catch a few winks now…
I had forgot to mention that this approach is for polygon based maps. If you have got a point based map, then the wizard itself will give you options to set bubble size and colour.