LessThanDot

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

When SQL Server 2008 was introduced, it included the new spatial data types of geometry and geography. Report Builder 3.0 gave us the capability to add maps to reports, which can utilize those data types to visualize data.

The Map Wizard in Report Builder 3.0 provides a simple way for you to create a map or map layer, tying it to spatial data. To begin, you need to understand that a report needs spatial data, to show the points you’re trying to display, and can have analytical data, to show more information. I’ll walk you through using the wizard to create a simple two-layer map that shows the sales (analytical data) per AdventureWorks store (spatial data) in the state of New York.

The Chart Wizard can be accessed in two ways. It will appear on the splash screen when you open Report Builder. It can also be found under File > New.

The Wizard

Choose a source of spatial data

First, you need to choose where your data will come from.

Map gallery – These are predefined maps provided by Microsoft. They are .rdl files saved on your local computer.

ESRI shapefile – These are a set of files that conform to the ESRI Shapefile spatial data format.

SQL Server spatial query – You can reference the geometry or geography data types in a SQL Server database. (I will show this in a later example.)

Select Map Gallery, expand USA, expand States by County, and select New York.

Choose spatial data and map view options

Map resolution – You can choose quality on a scale from best quality or smallest size.

Embed map data in report – This option allows you to embed map elements that are used in the map layer. This is a default for the Map Gallery.

Crop map as shown above – Pan and zoom the map using the controls on the left. Once you have it set, you can check this option for a default view of the map.

Add a Bing Maps layer – This option adds in a Bing map tile which will provide a geographic background for the map view.

When you click Finish, you’ll be taken to the Report Builder main screen.

You can click on “Map Title” to change this.

When you click anywhere on the map, the Map Layers screen will pop up. Here, you can add a layer; delete a layer; access properties; shift the map view up, down, left, or right; and adjust the zoom level.

Running the report right now will result in a map of the state of New York, with the names of a few counties displayed. We need to add a dataset to tell the report what data to display.

I’m going to use AdventureWorks2008R2. You’ll need to go to your database and create this view:

Spatial field – Make sure this is set to the field in the query that contains the spatial data, SpatialLocation.

Layer type – Choose Point, to display one point. A polygon would outline and area, and line could be used to mark a path.

You won’t “Embed map data” or “Add a Bing Maps layer” on this layer, in this example.

Choose map visualization

This time, choose Analytical Marker Map.

Choose the analytical dataset

Pick the dataset you created earlier, which has the spatial data in it.

Choose color theme and data visualization

“Use marker types” will add pin shapes at the points on the map.

“Use marker sizes” will size the points differently, based on the data.

“Use marker colors” will color the points on the map differently.

Use marker types, based on City, and marker colors, for the SUM(Sales).

Click Finish and you’ll be taken back to the main screen. Now, you can see two layers have been added to the map.

Click Run to view the results.

Here, you’ll see a point for each city that has a store. Each point has a different marker (circle, square, pin, etc.) The color of the marker is going to vary based on the amount of sales – the lowest (Endicott) is white, the highest (Melville) is red.

Maps are a great addition to Report Builder 3.0. You can easily align data from sales, customers, products, campaigns, or even the miles you’ve run each week with built-in maps. This takes reporting to a new level.

This was a very basic introduction – there is much more you can accomplish with maps. Check out these additional resources for more information!

Related Posts

In this post, you’ll learn what report parts are, how to use them, and why…

About the Author

Jes Borland is a Senior SQL Engineer at Concurrency and a Microsoft Data Platform MVP. She holds an AAS - Programmer/Analyst degree and has worked with SQL Server as a developer, report writer, DBA, and consultant. She is a user group founder, a blogger, and a frequent speaker. In her free time, she runs, cycles, and cooks.