Fun with Reports and Maps! by Gus Collazo

In this blog, let's take a quick look at how to use a map to display nationwide sales data on a SQL Server report. You can jump quickly to the bottom of this blog entry to see how the report will finally turn out. To try out the steps below, you first need a server running SQL Server 2005 with the AdventureWorks database. You also need SQL Server Reporting Services configured and you'll need Visual Studio 2005 or 2008 on your machine.

Let's first start out by installing the Dundas Map for Reporting Services control on your machine. You do this by visiting http://www.dundas.com/Downloads/index.aspx and clicking the "Download Now" button next to "Dundas Map for Reporting Services". Follow the instructions on the Dundas website to install the control.

Once the control is installed, fire up Visual Studio. Create a new "Report Server" project.

Next, right-click on the Reports folder in the Solution Explorer and add a new report. Click through the wizard to setup a data source that points to your AdventureWorks database.

In the "Design the Query" step of the wizard, paste this SQL code in. The query will retrieve the total sales for each state.

SELECT z.[Name], CONVERT(INT, SUM(z.TotalDue)) AS StateTotal
FROM (
    SELECT DISTINCT s.[Name], o.TotalDue
    FROM Person.StateProvince s INNER JOIN Person.Address p ON s.StateProvinceID = p.StateProvinceID
        INNER JOIN Sales.SalesOrderHeader o ON o.BillToAddressID = p.AddressID
    WHERE s.CountryRegionCode = 'US'

    UNION

    SELECT DISTINCT s.[Name], 0 AS TotalDue
    FROM Person.StateProvince s
    WHERE s.CountryRegionCode = 'US'
) z
GROUP BY z.[Name]

Click the Next button from this screen, then click Next/Finish to end the wizard. You can accept the defaults of the wizard after the "Design the Query" step. Once the wizard is done, select everything on the report page and delete it. We will be using the map control and do not need the controls the wizard created.

Next, we'll need to add the Dundas Maps control to the toolbox if its not already there. Right-click the Toolbox window, and select the "Choose Items..." menu item. In the ".NET Framework Components" tab click on the "Browse..." button and browse to the installed Maps assembly (DundasRSMapDesigner.dll). After selecting the chart assembly make sure the "Dundas Map for Reporting Services" item is checked.

Now drag the map control to the report layout. This will start the Dundas Map wizard. On step 1, change the Gallery dropdown to "North America", click "USA", then click Next.

Next, select your data source for the map's data set. Add the "Name" and "StateTotal" fields to the "Displayed fields" list. Also, click "Row Groups" to add the "Name" field to the row groups list. Click Next.

Finally on the wizard, change the Field to StateTotal and Text to #STATETOTAL. You can also tweak the color settings to your liking.

We're not done yet. The wizard gets us started, but we still need to tweak the properties of the report a little. Right-click the map itself and select "Properties". On the General tab, change the settings to reflect the screen shot below.

On the Data Appearance tab, change the settings to reflect the screen shot below. In particular, set the Legend Text to "#FROMVALUE{N0} - #TOVALUE{N0}". Also, set the "Show In Color Swatch" setting.

Finally, in the Shape Appearance tab, you can configure the color settings as below.

That's about it. You should have a report like the one below. To test your report, click the Preview pane. In a later blog entry, we'll explore adding drilldown functionality to the map report so users can click on the map itself to load a map with more details for the state selected. Stay tuned!