Problem solve Get help with specific problems with your technologies, process and projects.

Finalizing maps with Report Builder 3.0

Like an onion or parfait, maps in SQL Server Report Builder have many layers. Here you'll learn how to finish off a map by adding point and tile layers in your report.

Part 1 | Part 2 | Part 3

Once you've created your report and added the polygon layer, you're almost done with your basic map. The final steps involve two more layers to map creation with Report Builder 3.0 – the point and tile layers. Part three demonstrates what each of these layers entails.

We'll be adding a point layer to provide markers that indicate where the sales people reside. The layer will be based on the data we're retrieving through our dataset. To create the point layer, click the New Layer Wizard icon at the top of the Map Layers pane. When the New Map Layer wizard appears, select SQL Server spatial query as the source for spatial data.

On the next screen, select SalesPeople as the dataset. You'll then be prompted to choose the spatial field and the layer type. The field should be the SpatialLocation column, and the layer type should be Point (both of these options should have already been selected by default). We'll also create a basic marker map -- the default map visualization option.

When you reach the screen that prompts you to choose the color theme and data visualization, we'll select PushPin as the marker type, but leave the other settings with their default values before clicking Finish. The point layer is added to the map and listed in the Map Layers pane.

What else can you do with Report Builder 3.0?

Check out Robert Sheldon's two previous series on adding charts and gauges to SSRS reports.

The next step is to configure the point properties for the new layer. Click the down-arrow next to the point layer in the Map Layers pane and select Point Properties. On the General page of the Map Point Properties dialog box, we'll configure two properties with expressions.

For the Label text property, open the Expression dialog box (by clicking the expression button) and create the following expression:

=Fields!City.Value + ", " + Fields!StateProvinceCode.Value

The expression concatenates the city name and state code so they're displayed by the pushpin markers on the map.

For the Tooltip property, open the Expression dialog box and add the following expression:

=Fields!FullName.Value + ": " +


This expression concatenates the sales person's name and sales amount so that they are displayed when someone hovers over the marker.

Finally, reduce the Marker size property to 7 points. The Map Point Properties dialog box is shown in Figure 7.

Figure 7 (click to enlarge)
General tab of the Map Point Properties dialog box

After modifying the properties on the General page, we'll then modify settings on the Font page. In this case, I've used 8-point Arial, bold with white type. That's all there is to adding the map layer. The point and polygon layers should now look like the map shown in Figure 8.

Figure 8 (click to enlarge)
Design view of point and polygon layers

You'll notice that the pushpin markers now appear, along with the <<Expr>> placeholder, which indicates that the label is based on an expression.

Also note that the state colors are darker than they were after we added and configured the polygon layer (as you'll recall, we set the Transparency property to 40). By adding another layer, however, that property was reset to 0. As a result, we'll need to reset the visibility after we've added all the layers. (Normally, I would not have set the property until I finished adding layers, but I wanted to demonstrate this particular Report Builder 3.0 feature.)

Figure 9 shows what the report now looks like when run. As you can see, the pushpin markers have been added along with the names of the cities and states.

Figure 9 (click to enlarge)
Report that includes a map with point and polygon layers

If you were to hover over a pushpin marker, you would see the name of the sales person and the total amount of sales. Also, you probably noticed that the labels are difficult to read in the current rendering. Not to worry; the next layer we add will be much darker, so let's take a look at that.

Adding a tile layer to a map

The final layer is the Virtual Earth background, which will make the report look far more interesting. You cannot use the New Layer wizard to add a tile layer to a map. Instead, you need to click the Add Layer icon at the top of the Map Layers pane and select Tile Layer. The layer is added to the map and listed in the Map Layers pane.

By default, the tile layer is configured as a road type, but we want an aerial map. To change the type, click the down-arrow next to the tile layer in the Map Layers pane and select Tile Properties. On the General page of the Map Tile Layer Properties dialog box, set the Type property to Aerial and then click OK.

Now we need to go back and address that visibility issue. Open the layer properties for the polygon layer and, on the Visibility page, set the Transparency property to 40. Our map, as it appears in design view, will now look like the one shown in Figure 10.

Figure 10 (click to enlarge)
Design view of point, polygon, and tile layers

As you can seen, the polygon and point layers lie on top of the tile layer, and the transparency allows some of that layer to show through. Finally, our finished report can be seen in Figure 11.

Figure 11 (click to enlarge)
Report that includes a map with point, polygon, and tile layers

All three layers are now incorporated into one map, providing a seamless source that links spatial and analytical data, while the legend provides a quick overview of the range of sales. Of course, this is only a basic map. There are other features you can incorporate that would make this far richer, including a line layer to connect points. Still, it should be clear that the map functionality in Report Builder 3.0 is a tremendous addition to its data visualization toolset, one that can make a variety of reports more dynamic and aesthetically pleasing.

 Part 1: Geospatial mapping
 Part 2: Adding a polygon layer
 Part 3: Finalizing maps

Robert Sheldon
is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at

Dig Deeper on Data Visualization Techniques for SQL Server

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.