By: Ray Barley | Comments (15) | Related: 1 | 2 | > Reporting Services Development
Problem
I noticed a new feature in SQL Server 2008 R2 Reporting Services that allows you to render maps in your reports. Can you provide some details on this new feature and can I take advantage of it even though don't have any spatial columns in my data warehouse?
Solution
SQL Server 2008 introduced new spatial data types including a geography type that can be used to stored latitude and longitude coordinates. The new map feature in SQL Server Reporting Services 2008 R2 works with the geography type as well as with state abbreviations, state names, or state numbers. The built-in map feature allows you to add a map to a report and show the states in the US or the counties within a state. You can use the map to render one or more data points, providing a slick visualization of your data by state or county within a state.
In this tip I will walk through creating a report with a map. As an example I will create a simple report using Business Intelligence Development Studio (BIDS). A preview of the report is shown below:
My report will use the AdventureWorksDW2008R2 database and show reseller sales by state. I will use the following query to retrieve the total reseller sales for a given calendar year by state:
SELECT g.StateProvinceCode, SUM(f.ExtendedAmount) Sales FROM dbo.FactResellerSales f JOIN dbo.DimDate d ON d.DateKey = f.ShipDateKey JOIN dbo.DimReseller s ON s.ResellerKey = f.ResellerKey JOIN dbo.DimGeography g ON g.GeographyKey = s.GeographyKey WHERE d.CalendarYear = @CALENDAR_YEAR AND g.CountryRegionCode = 'US' GROUP BY g.StateProvinceCode
To add a map to a report, double click Map from the Toolbox as shown below:.
The Map wizard will be launched as shown below:
The easiest way to get started is to select Map gallery which includes the built-in maps. Choose USA by State Exploded then click Next to proceed to the spatial data and map view options as shown below:
The above dialog provides for customizing the map resolution, cropping, and adding a Bing Maps layer. The Bing Maps layer options are a road view, aerial view, or a combination of the two. With the Bing Maps layer you will need an internet connection when running the report in order to retrieve the Bing Maps. For our purposes just accept the defaults and click Next to continue to display the Choose map visualization dialog as shown below:
The Basic Map displays data on the map, the Color Analytical Map provides color-coding based on the data, and the Bubble Map shows bubbles proportionately sized based on the data. Choose the Color Analytical Map and click Next to display the dialog to select the state column in the dataset as shown below:
The dataset query that I'm using has a column named StateProvinceCode which is the two character state abbreviation. Click the check box in the Match Fields column next to STUPS (i.e. state abbreviation used by the US postal service) then click Next to continue and display the Choose color theme and data visualization dialog is displayed as shown below:
Choose a Theme, the Field to visualize and Color rule. The Theme is the color schema in the area around the map. The Field to visualize is the column in your dataset that you want to render on the map; in my case it is Sales. The Color rule sets the shading in the map. The map automatically analyzes the distribution of values in the Field to visualize and comes up with the various color shades and ranges of values for each. If the Display labels checkbox is checked, the value for the Field to visualize will be displayed on the map. In this case it's best to not check Display labels as the numbers are too large to render on the map within some of the states.
Click Finish to complete the Map Wizard, then click the Preview tab to see the report as shown below:
There are a number of additional customizations that can be done to the map. Click on the blank area of the map (i.e. the area inside of the red rectangle as shown below) to display the Map Layers dialog. Click on the blue area then select from the menu as shown below:
Some of the available options are to add a layer to the map, delete a layer, edit the Layer Data options, run the Layer Wizard, etc.
Next Steps
- The mapping capability in SQL Server Reporting Services 2008 R2 is a nice addition to the product and it's very easy to get started.
- You can download the AdventureWorks sample database used in the report from this CodePlex site.
- You can download the sample report here.
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
