Finalizing maps with Report Builder 3.0

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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in February 2010

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 + ": " +

FormatCurrency(Sum(Fields!SalesYTD.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)

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)

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)

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)

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)

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.


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.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top