Going beyond Geospatial Analysis with SSRS Map Reports

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) image 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]) image 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:- image 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. image 7) Click on preview and you should be able to see the result. image 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.

Posted by SQLJason



Wow, tooth decay analysis with SSRS. that's awesome!

From the Image-Maps site, how did you convert the x,y coordinates to POLYGON friendly cords (x y, x y, xy)

Lets say I get the following result from Image-Maps

I can then remove every alternate comma and wrap it inside a sql statement as shown below

select geometry::STPolyFromText('POLYGON((68 36,452 35,291 198,234 169,203 208,216 231,69 217,13 265,12 91,68 36))',0)

Also note that the first and last coordinates are the same if you are making a polygon

How did you get your custom image of teeth to display in the map report item?

This post basically shows you how to do that. Is there any specific step you didn't understand?

In your example, you had to trace everything that you wanted to have appear in the report. I have a floor plan where I only want to match data to some of the image; so I need to be able to see the image as well as the polygons over that image in the report. It doesn't seem like that is possible unless a create polygons for everything on the floor plan.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.