Building a Simple Map in SSRS 2008 R2

In a previous post we discussed mapping the SQL Server geography data type in Mathematica. Now we’re going to build a simple map in a SSRS report using the same query as before. To start, we’ll open Visual StudioBusiness Intelligence Development Studio (BIDS) and create a simple data source connection. Right click on Shared Data Sources and click on, “Add New Data Source”.

In the next window that pops up, name the connection (here we just called it Localhost) and then click on the Edit button.

In the window that pops up, enter the server and the database that you would like to connect to.

You can click the Test Connection button if you wish but really, if you can select the database, you can connect to the database. Click OK twice to create the shared data source.

Once that is done, we will want to create a report. As usual, you can just right-click on Reports to get the menu. If you click on, “Add New Report”, it’ll take you to the Add New Report Wizard. I prefer to just create a blank report and create from there. To do that, click on Add -> New Item…

Select the Report option and give it a name. I named mine, Top 500 Sales Around the World.

Next, click on your Toolbox pane, select the Map object, and drag it over to your report.

When you drag it over, a new window will pop up with options for adding a new map layer. Select the option for a SQL Server spatial query and hit, “Next”.

The next window asks if you wish to choose an existing dataset or create a new one. We will want to create a new one (it’s at the bottom). So select that option and then hit the Next button.

Click the “New…” button on the next window that pops up to create a new data source.

On the Data Source Properties window, name the connection something sane and then select the, “Use shared data source refence” option and in the dropdown list, select the shared data source we created before. Click OK.

Then click Next.

The next window that pops up will want you to create a query. Select, “Edit as Text” and enter the following query:

SELECT TOP (500) Address.SpatialLocation

FROM Person.Address AS Address INNER JOIN

Sales.SalesOrderHeader ON Address.AddressID = Sales.SalesOrderHeader.ShipToAddressID

Click Next. SSRS will detect that the values we are using are points. With the points themselves just being points in space, you’ll want some form of underlying map to plot them on to. At the bottom, select the option to add a Bing maps layer and click Next.

Select the type of marker map you want to use and click Next.

Select your final options such as theme, marker types, and color options and then click Finish.

Once that is done, you can click on the Preview pane and see your shiny new SSRS map!

Please note that while my map is called Top 500 sales locations, it isn’t a top 500 in the usual sense (500 highest grossing sales). It’s merely the first 500 rows in the Person.Address table that have matching values in the Sales.SalesOrderHeader table. Building a map in SSRS is impressively easy.