SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Maps in Reports!

I’ve been playing with SQL Server 2008 R2 for quite a while in the CTP’s and what not. But, I hadn’t made a concerted effort to look at the new version of Reporting Services… HUGE mistake. There are a number of, not insubstantial, updates to Reporting Services that, probably, are the primary selling points of 2008 R2. The big one, for me, was the incorporation of mapping directly into reports. Did I say big? I’m sorry, I meant, enormous, gigantic, galactic, really, really important… Why you ask? I work for an insurance company. We insure factories, warehouses, that sort of thing. Funny bit of information about a factory, when floods, high wind or earthquakes come calling, they just don’t seem to be able to get out of the way adequately. Weird, huh? With that in mind, it’s really useful to be able to know, not simply where a building is located, but if that location is in a 50 year or a 500 year flood zone, is it subject to high winds, does the New Madrid fault line run straight across the factory floor… Because that factory isn’t going anywhere, you want to know how it should be built to withstand that flood, wind, earth movement, etc.. In other words, spatial data is a big deal where I work.

Using maps in reports is incredibly simple. First you need a query. Here’s one that runs against AdventureWorks2008R2:

SELECT a.City ,
a.PostalCode ,
a.AddressLine1 ,
sp.Name ,
FROM Person.Address AS a
JOIN Person.StateProvince AS sp ON a.StateProvinceID = sp.StateProvinceID
WHERE sp.Name = ‘Arizona’

With that query, creating a report is simple. Open up BIDS, add a data source, create a report, and plug that query into it. Click Next, Next, Next however many times you need to in order to arrive a report. You can blank it out, removing all columns or leave it alone, any way you want, but, click on the Tools menu choice and select Map. Click on the report to place a map and you’ll get this dialogue box. You can then drill down a select a number of different maps, built right into Reporting Services, just as I did for the map of Arizona:

Once you have the map you want, click Next again and you’ll see a layout of what the map piece of the report is going to look like. You can work with a number of options, up to and including adding information from Bing Maps for, for free. This is slick stuff:

You can finish from there and you get a map. But, there’s no data on it. Now you need to click on the map and add a layer, again, you can use a wizard, the Layer Wizard, to select a spatial query, maybe the one we created at the top of this experiment. This is then placed on the map as points or shapes, depending on the data. Below is the finished map:

No big deal right? Except, this was free, I didn’t type a bit of code, I didn’t set any properties or muck about with all sorts of menu choices or do much of anything at all, but I ended up with what could be a useful report. It took longer for me to type this sentence than it did to create the map. Imagine what you can do if you actually tried to put some work into it. This is a fantastic new resource and one that I’m going to be spending a lot of time exploring. I think many of you will find it useful as well.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Posted by Steve Jones on 14 June 2010

Very cool!

Posted by Dukagjin Maloku on 14 June 2010

Nice post!

Posted by Seth Phelabaum on 14 June 2010

Awesome info Grant, thanks for sharing.

Posted by adrian.saunders on 15 June 2010

So does the SSRS2008 have to be hosted on SQL2008r2 or does the data source also have to be R2?

Posted by Grant Fritchey on 15 June 2010

Thanks Steve. I'm going to turn this into an article for you. It'll take a week or two, but watch for it.

Adrian.Saunders: You can install SSRS2008R2 independently of any SQL Server instance. Then it only needs a 2008 or 2008R2 server to pull spatial data from. R2 is worth having, just for the SSRS updates. As a matter of fact, I think we'll have the SSRS R2 installed in prod long before any R2 SQL databases are out there.

Posted by alan.powell 88783 on 13 May 2015

Where did you get the SpatialLocation information from?

Leave a Comment

Please register or log in to leave a comment.