You’ll then see a screen asking what type of map you would like to use. The default comes with various Maps of the USA. You can download maps to add to the gallery. A popular option would be to use an ESRI shape file. Select the Map Gallery button , and choose "USA by State Inset". This will allow us to create a report of the USA showing data by each State.
The next screen has some interesting options. You can adjust the resolution ( quality ) of the map which will affect the amount of data sent over the network. Using the slider on the left , you can zoom in and crop the map. And the nicest option is the checkbox that says "Add a Bing Maps background". You can choose Road , Aerial or Hybrid , and this will really up the look and feel of your report.
On the next screen you can the type of representation you want on the map itself. I haven’t used the basic map yet as it seems to be the most in depth to set up. What you can choose is either the "Color [sic] Analytical Map" which will represent the data in varying shades of colour, or the Bubble Map, which represents the data in varying sizes of bubble. Lets try the Colour Analytical Map first.
On the next screen you can create or browse to a data source connection. This is simple to set up – click next when you’re done. The next screen allows you to use the wizard to build a SQL query. Now I have a query off the AdventureWorksDW2008 database that will return the sales amount and order quantity per customer and state. This can be used to get a rolled up value of sales per state. We’ll have to define the query and then link it up to fields available on the map object itself. First, the query …
FROM AdventureWorksDW2008.dbo.FactInternetSales Fact WITH (NOLOCK)
INNER JOIN dbo.DimCustomer Cust
ON Fact.CustomerKey = Cust.CustomerKey
INNER JOIN dbo.DimGeography Geog
ON Cust.GeographyKey = Geog.GeographyKey
WHERE Geog.EnglishCountryRegionName = ‘United States’
This will return the following result
The StateProvinceName column is important here – we will use this to link it to the State Name column on the Map , so that the Map object knows which state the data is for. To do this , click on "Edit as Text" to bypass the wizard, then paste in the query. Hit the exclamation button to make sure it works and then click "Next". On the next screen , select the checkbox next to "StateName" and in the drop down box to the right select "StateProvincename". This will link the two.
The next screen is also important. Under the "Field to Visualize" section you need to select which field will be used as the data itself represented on the map. Choose [ Sum(Fields!OrderQuantity.Value ] , in other words , the Order Quantity field. Change the "Color Rule" drop down to [ Light – Dark ] and check Display labels. The data field will be the same.
Click Finish, and then Run , and you should see your map. The states on the west coast should be darker as the majority of sales occured there.