Last Sunday I delivered a brief talk at the Readify barcamp on the new Map control in the SQL 2008 reports toolkit. I thought it would be a good opening topic for my new blog here on my unused Second Square domain.

So what is this Map thingy anyway?

The November CTP on SQL Server 2008 R2 introduced several new features to the SQL core and support features. One of these features was the version 3.0 of the Report Builder tool, which is a standalone report creation tool for SQL Reporting Services that you can get here. Along with the new Report Builder came a neat new control which allows you to add Maps to your reports.

Adding a map to your SQL reports is obviously awesome to the power of rad but it also serves a practical purpose. Being able to visualise data with a geographical context add a new dimension (literally) to your ability to analyse data. There is also Bing maps integration so you can get the road, aerial and hybrid view underneath you data set to give it an even bigger injection of cool.

So how’s it work?

You can use several sources for you for your spatial data.

  • Gallery Reports (a set of preloaded spatial areas installed with the CTP)
  • ESRI Shapefiles
  • SQL 2008 spatial data queries (obviously)

There are 8 map types available in the CTP and they are group by the type of spatial data they represent.

Polygons

  • Basic Map
  • Colour Analytical Map
  • Bubble Map

Lines

  • Basic line map
  • Analytical line map

Points

  • Basic marker map
  • Bubble marker map
  • Analytical marker map

I won’t go in to the detail of what each of these types can do you can find a better and more concise definition on MSDN.

How do I use it?

First you will need to get your hands on the SQL 2008 R2 CTP, make sure you install SSRS :) . Once you’ve got it up and running you get stuck into to your reports… I will go through two examples.

The first is to display the Australian population by state. Using the Colour analytical map type I will have a ESRI shape file as the base data (States) and I will load the analytical data from a second dataset, SQL database table.

The second example will show the time I spent waiting at traffic lights(signals) on my way to work, for this I will use the bubble maker map with a Bing maps layer to show the street data. There is a single data source for this report which is a SQL table with a geography field which stores the latitude and longitude of the intersections. We will take a look at this in part two of the blog post.

For the purpose of this post I will be using the integrated report designer in VS2008 but the same can be accomplished in the new Report Builder.

Australia Population by State

The data for this example I just grabbed from the ABS website. The ESRI shape files I had lying around on my machine from VDS Technologies.

So the first step once is to create a new report and insert a map. This will launch the new map wizard.

Here you can see the 3 types of spatial data you can use I am going to use the ESRI shapefile I got from VDS. Once selected you will see a quick preview of the file.

Next step is to use the preview the map layout there a several controls here that allow you to modify the way the map is diplayed in the map appature in you final report. The slider on the right allows you to select the quality your map will be rendered in, this also effects the output quality when a report is export to a file like PDF.

Next step is to select the map type in this case the colour analytical map.

Then use the wizard to create your data source and specify your data query. This is bring load the reference data (population information) which we will associate to the areas (states) in our shape file.

Once you have your reference data you need to map it to the areas in your shapefile. This is easily achieved using the wizard, simple select the attribute in the shape file and the column from you analytical data you want to correlate, in this example I am linking the attribute GMI_ADMIN in the shapefile to the StateCode field in my database result.

Now to setup the data display. Select the field for which your analytical data set will be based on (the sum of the population estimate) then select the colour range that will be used to display you data range. Next click finish and we are pretty much done. You can make heaps of adjustments in the designer window, but as this is really just to peak you interest I will let you investigate this yourself.

And then the final result….tada

So I have run through a basic use of the Map control in the SSRS 2008 R2 toolkit. I know it’s a bit light on detail but half the fun of this stuff is discovering it for yourself.

But you might be thinking “But wait you said there was Bing Maps integration”

I sure did say that an I will go through it in Part 2 on the blog post in the next couple of days, believe me the coolness is definitely worth the wait.