icon on the Insert menu. When the wizard appears, the first screen prompts you to select a source for your spatial data. You can choose from one of three source types:

• Map Gallery -- Reports that have spatial data embedded in them. The Map Gallery is installed by default when you install Report Builder 3.0. The gallery includes maps of the U.S. and individual states.
• ESRI Shapefile -- Files containing data that complies with the Shapefile spatial data format identified by the Environmental Systems Research Institute (ESRI). The files specify geographical or geometrical shapes.
• SQL Server spatial query -- Data from a SQL Server database that contains columns configured with the geometry or geography spatial data types.

For our first layer, we'll use the Map Gallery. When you use the Map Gallery, a polygon layer is automatically created, and you cannot use the Map Gallery to create any other type of layer. In addition, when you use the Map Gallery, you must select the map in the Map Gallery tree on the bottom half of the wizard screen. In this case, we'll use the USA by State map.

For the rest of the New Map wizard, simply click through the wizard screens and accept all the default settings. Here we will create a basic polygon layer and use the default view and color options. When you finish running the wizard, the first map layer is added to the design surface. You can then resize the map as appropriate -- just be careful not to distort the shape of the states.

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.

 By default, the map includes all 50 states. For the purposes of this report, however, we'll be using only the mainland states, so we can delete Alaska and Hawaii. We can delete states because the map is actually made up of individual polygons; one for each state. To remove a state, right-click it and select Delete Polygon. The map will then automatically zoom in and center on the remaining states, as shown in Figure 1.

Figure 1 (click to enlarge)

When deleting the states, the Map Layers pane should appear (if the pane is not visible, double-click the map). The Map Layers pane provides details about each layer that you create. So far, only the polygon layer would be listed. The Map Layers pane also includes the Map view section, which allows you to zoom in or out of the map or change its center.

The next step is to change the map title. Simply right-click the placeholder title (Map Title) and select Title Properties. In the Map Title Properties dialog box, I can change the title to something like "Sales over $1 Million in Mainland United States." Next, we'll modify several properties of the polygon layer to prepare it for working with the analytical data.

First, let's modify the layer properties. To access the properties, click the down arrow next to the layer in the Map Layers pane, and then select Layer Data. In the Map Polygon Layer Properties dialog box, select the Analytical data page. The purpose of this page is to match your source of analytical data to the spatial data in the Map Gallery report. The Map Gallery, in addition to providing the spatial data needed to create the polygons, also includes details about the spatial data, such as the name of the state. The goal is to match the state codes in the Map Gallery to the codes we retrieve from SQL Server through our dataset. That way, the states that appear in the Map Gallery can be linked to the states in our result set.

To create a match, select SalesPeople as the analytical dataset, then click the Add button. From the spatial dataset (the Map Gallery), select STUSPS from the drop-down list. These are the U.S. Postal Service codes used to identify states. Then, from the analytical dataset (SalesPeople), click the StateProvinceCode field from the drop-down list. As you'll recall from the query used in the dataset, I trimmed the names of the StateProvinceCode column. If I had not done that, there would have been an extra space in the values, and the state codes would not have matched between the two sources. Figure 2 shows the Analytical data page with the properties configured.

Figure 2 (click to enlarge)

After the Analytical data page is configured, select the Visibility page and set the Transparency property to 40. Then click OK to close the dialog box.

Next we will configure the color rules so the state colors reflect the sales amount for each sales person in that state. The colors will correspond to a graduating range of values, based on the sales amounts. To configure the color rules, click the down-arrow next to the layer in the Map Layers pane and select Polygon Color Rule. On the General page of the Map Color Rules Properties dialog box, select the option Visualize data by using color ranges. When you select this option, several other properties appear, as shown in Figure 3.

Figure 3 (click to enlarge)

For the Data field property, I selected [Sum(SalesYTD)], with green as the start color, yellow as the middle color, and cornflower blue as the end color. You can, of course, play with these colors at any time to come up with a range that works best for you. The best way to try these out is to experiment with them and see how they look on the rendered report.

Next, we'll configure the properties on the Legend page so the default legend displays data about the sales amount and the colors those amounts map to. First, clear the Show in color scale option and then select Legend1 as the legend. Next, we need to modify the expression in the Legend Text property. This is a .NET expression that specifies how to format the text. All we need to do is to change N0 to C0 so the amounts show as currency. The expression should then look as follows:

#FROMVALUE{C0} - #TOVALUE{C0}

Figure 4 shows the legend page as it should appear with the updated property values.

Figure 4 (click to enlarge)

Finally, we must configure the polygon properties so that only the states with sales associated with them are colored. To modify the polygon fill colors, click the down-arrow next to the layer in the Map Layers pane and select Polygon Properties. On the Fill page of the Map Polygon Properties dialog box, change the color to No Color.

The last step is to change the legend title. Right-click the title, click Legend Title Properties, and modify the title. I used "Sales Range" for my title. Now, looking at the polygon layer in design view, it should look similar to Figure 5.

Figure 5 (click to enlarge)

As you can see, the legend shows ranges of amounts and the color scale. The colors of the states should match the range of values. When you view the map in design view, however, it uses sample data, so all the states are colored even though there is no data for most of those states. To see what the map will actually look like, you need to run the report. My results are shown in Figure 6.

Figure 6 (click to enlarge)

Notice that the Sales Range legend now reflects the actual range of sales over $1 million and the colors in the scale match the color of the states that fall within a specific range. As you can see, we've been able to integrate analytical data with spatial data in a single layer. By adding additional layers, however, we can provide more specifics in our map.

Continue to part three

ADDING MAPS IN REPORT BUILDER 3.0
 Part 1: Geospatial mapping
 Part 2: Adding a polygon layer
 Part 3: Finalizing maps

ABOUT THE AUTHOR
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 www.rhsheldon.com.