Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

Using SQL Server 2008 R2 Report Builder 3 with Bing Maps

This article shows you how to get started with Report Builder 3 (RB3) and Bing Maps.


I could use adventure works to show you this but I thought you might want to see a little bit of our green and pleasant land instead. To do this I have put  the previous SQLBits venues into a venue table..


CREATE TABLE [dbo].[Venues](
    [VenueID] [int] IDENTITY(1,1) NOT NULL,
    [VenueName] [nvarchar](50) NULL,
    [VenueLocation] [geography] NULL,
    [VenueURL] [nvarchar](100) NULL,
    [Attendees] [int] NULL)


and got the spatial locations into this by using this application from my fellow evangelist Mike Ormond.


image


Now I have some spatial data so I can use the new report builder 3 in SQL Server 2008R2 to make a report. As with SQL server 2008, this is either a click once install from report manger or a separate .msi install you can get from the SQL Server 2008 R2 feature pack.


When you open RB3 you get confronted with various wizards..


image


Following the map wizard will bring this up


image


While Cecil Rhodes remarked that


“Remember that you are an Englishman, and have consequently won first prize in the lottery of life.”


this doesn’t apply to spatial data so there are only shape files for the US, however you can play with Bing Maps right out of the box by selecting the last option; SQL Server spatial query.You’ll need to specify the data source where this data is


image


..and the query to retrieve it ..


image


note the venue location column returning spatial data then you’ll get something like this..


image  once


the orange arrows show where the tool has picked up the points form my query.  Now I can embed map data in my report and add a Bing Maps layer..


image


and choose what sort of Bing Map I want (here I went for aerial) and you can stil see my data points.


Now I can define how I want to present my data points..


image


 


 


 


 


 


 


 


I am going for a bubble map, and now I need some analytical data for that..


image


which for me is just the same data set as I used before, but could be a separate one if you want.


image


 


which in my case makes the next screen look odd as I am joining a data set to itself on VenueName!


image


There are also sorts of options you can then experiment with to get the effect you want, I have gone for colour to denote attendance and have use VenueName as the label with an ocean them for the whole map.


You can then edit the map further in Report Builder by clicking on the map layers and then using the dropdon arrow for the layer you want to edit. In this case I changed the point title font to white..


image 


 


and I have also dragged the legends of the map so I can see my data.


That’s about it now I can save and deploy my report. One thing to note here is that the end user doesn’t need an internet connection to see the Bing maps as I embedded the relevant map tiles in the report.