Maps in SQL Server 2008 R2 Reporting Services

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:.


http://www.mssqltips.com/tip.asp?tip=2174