Archive

Archive for November, 2009

Using Dundas Maps in Reporting Services 2008 R2

November 19, 2009 2 comments

One of the big additions in SQL Reporting Services 2008 R2 is the inclusion of Dundas Charts with the product. The importance of spatial data is growing , and we saw with SQL 2008 the spatial data capability being added to the database. We then needed a way to visualize the data , and that’s where Dundas Maps comes in. Now after playing with the maps , i found that they work a bit differently than a normal Reporting Services component, but it’s not too difficult to get up and running. In this article , I’ll show you the basics of using the Map Component.
 
I’ll start with Report Builder 3. The version I have is not the latest , but it does have the Map components. The easiest way to get started is to create a new report , and clikc on the "Map" button at the centre of the screen, which will launch the wizard.
 

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 …

SELECT Fact.SalesAmount,
        Fact.OrderQuantity,
        Cust.LastName,
        Geog.StateProvinceName
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.

Categories: Reporting Services