When I came to the US six months ago, I had set myself a goal of doing more presentations than I did last year in the UK (which was two – one at the London BI User group and another at SQLBits 9). Well, I am glad to say that I achieved this goal within the last two months where I was able to present at four SQL Saturdays (#118, #130, #129 & #121), one code camp (Carolina Code Camp) and at my local UG in Charlotte. I have already got two other SQL Saturdays confirmed – #122 (Louisville, KY) on July 21 and #158 (New York City) on August 4 (so if you are around, please don’t forget to come over and say a hi). One of my favorite topics to present is on Map Reports in SSRS, and a statement that I just can’t stress enough is that the map reports feature can be used much beyond geospatial analysis. You don’t necessarily have to restrict yourself with geographical boundaries, and this post will give an example on the same. (By the way, if you are reading this post before June 26 and are interested in learning more about map reports, you might want to attend my online session – ‘Fast Track to Spatial Reporting using SSRS 2012’ through PASS BI Virtual Chapter)
The inspiration for this post is a demo from the Tableau product gallery where tooth decay is being visualized using images for the tooth, and I thought of replicating the same with SSRS. Follow the steps below to make a simple report:-
1) Get an image for the tooth set and then by using an online image mapping site like Image-Maps, you can trace the coordinates. Once you get the coordinates, you can convert it into polygons using the SQL function - geometry::STPolyFromText. You can download the SQL query for the tooth set that I created from the image below
There are many other ways in which you can obtain the same results, though I usually use Image-Maps because it is free.
2) Create a table called dbo.Teeth and store the results of the above query into this table.
3) Create a table called dbo.[Teeth Data] and store the results of the csv sheet below into this table.
4) Now that you have both the spatial data as well as the analytical data, create a new report and make 2 datasets – DST_Spatial for the spatial dataset (which will have the columns from table dbo.Teeth) and DST_Analytical for the analytical dataset (which will have the columns from the table dbo.[Teeth Data])
5) Drag and drop the map report item from the toolbox. From the wizard, select the SQL Server spatial query as the data source and choose DST_Spatial as the Spatial dataset. Select Color Analytical map as the map visualization and choose DST_Analytical as the analytical dataset. The match fields should be Name and Tooth as shown below:-
6) Select Decay_Scale measure as the field to visualize and click on finish. Now we should be able to see the map report item in design mode.
7) Click on preview and you should be able to see the result.
Here, I have just made a very simple report and this can be extended with all the common SSRS features like drill-down, report parameters, color formatting, tooltips, labels, etc. Compare this sort of a spatial report against a tabular report. Here, a dentist might be able to make more sense of the effect in tooth decay on neighboring teeth and hence can be very useful. Hopefully, this short post will make you think of the map report item in a new light now.