Download code for this article

Introduction

Today many developers are turning to Microsoft SQL Reporting Services™ to deliver great looking reports for their end users. And while having a professional looking report containing tabular data is great, sometimes users demand a more graphical representation of the data such as a chart or a map.

In this article we will step through the process of building a report based on data included in the AdventureWorks2000 database that is installed with SQL Reporting Services. Once we have a sales report showing the sales data, we will add a map of the United States displaying the sales data thematically by state.

(Note: This article covers the Webforms Edition of Map Suite version 1.1 and Microsoft SQL Reporting Services with SP1 applied. You will also need to have the AdventureWorks2000 sample database installed as well as Visual Studio 2003 and the .NET Framework v1.1. You can download the trial version of Map Suite Webforms Edition by following the link below. A short simple registration is required.)

Register and download the trial version
 

Getting Started

After you have downloaded and installed the trial version of Map Suite, open Visual Studio.NET, let's get started by creating a new reporting project called MappingReports. To do this open up Visual Studio and click File, New Project and choose Report Project from the Business Intelligence Projects folder. Go ahead and name the project MappingReports and click OK.

Next, let's setup the shared data source to the AvdentureWorks2000 database. To do this you can right mouse button click on the Shared Data Sources folder underneath your MappingReports project and choose Add New Data source. This will prompt you with the following screen. Go ahead and set your connection properties and click OK.

Building the Report

Now that we have the data source setup, we can add the report and retrieve our sales data. Right mouse button click on the Reports folder under the MappingReports project and choose Add New Report. This will start the new report wizard. Click next to begin. The second screen will prompt you to select a data source. Choose AdventureWorks2000 and click the Next button. Next we will be prompted to design the query to return the report data. We will use the query below to retrieve the total sales amount, orders count and customer count for each state from the AdventureWorks2000 database. Enter the following query and click the Next button:

SELECT s.Name AS 'State', Sum(o.TotalDue) AS 'TotalSalesAmt', Count(c.CustomerID) AS 'OrderCount', Count(Distinct c.CustomerID) AS 'CustomerCount'
FROM Customer c JOIN CustomerAddress ca ON c.CustomerID = ca.CustomerID
JOIN Address a ON ca.AddressID = a.AddressID
JOIN StateProvince s ON a.StateProvinceID = s.StateProvinceID
JOIN SalesOrderHeader o ON c.CustomerID = o.CustomerID
WHERE s.CountryRegionCode = 'US' GROUP BY s.Name

Now we need to select a report type, for our purposes a tabular report will work the best. Choose Tabular and click the Next button:

Next we are prompted to design our table to hold the tabular data. Highlight all of the available fields and click the Details button and then click the Next button. This will display all the fields from the query into the detail section of our report:

Our next step is to give our report table a style. For this example let's choose corporate and click the Next button:

Finally we are prompted to give our report a name, let's name the report SalesReport and click the Finish button:

Now that we have a basic report created, let's do a few things to make it look a little more professional. First, we'll drag the table and heading items to a width of 6.5 inches to stop the table headings from wrapping. This will also give us plenty of room to insert the map later on. One other thing we need to do is format the TotalSalesAmt field as currency. To accomplish this, right mouse button click on the TotalSalesAmt field and choose properties. Then choose currency from the format list and click the OK button.

As one last step for setting up the report, let's create a parameter for this report so the user can select the type of map they want to see. To do this we need to make sure the report is in layout mode and then choose Report Parameters from the Report menu item. You will be prompted with a report parameters dialog box and it will need to be filled out like the picture below:

Click the Add button and put MapDisplay in the name field. Also change the prompt to say "Display Map By". Next we need to add some selectable values for this parameter so let's add the following information to the Available values section:

Building the Map

Now that we have the basic report setup it's time to move onto building the map. The map will be added to the report through an external URL so the first thing we need to do is create the URL that will generate the map. To do this we need to add a new ASP.NET project to our solution by clicking File, Add Project, New Project. Then choose ASP.NET Web Application under the Visual Basic Projects folder and give your web application a name of MapBuilder.

This will create a new web project with a Webform1.aspx page already added. Let's rename the Webform1.aspx page to something more meaningful by right mouse button clicking on it and changing the name to GetMap.aspx.

(NOTE: Before moving on to the next steps you will need to install the evaluation version of Map Suite Webforms Edition it can be downloaded here. A short simple registration is required.)

Now we need to add the Map Suite ASP.NET server control that will be building the map for us. To do this right mouse button click on the Web Forms tab of the control toolbox and choose Add/Remove items and then Browse to the C:\Program Files\Spatially Aware\[YOUR MAP SUITE WEBFORMS VERSION]\WebformsEdition.dll. This will add the map control to your toolbox (The Map Suite Webforms control is represented by a little icon of the world.)

The next step is to drag the map control onto the GetMap.aspx page and set the Map1.Width equal to 600px and the Map1.Height equal to 400px, this will give us the right sized map to fit into our report later on. The last thing we need to do before we can begin coding is set up the web.config entries for the Map control and the connection string to the AdventerWorks2000 database. Open up the web.config file and insert the entries below the xml tag:

<appSettings>
<add key="connectionString" value="Server=local;Database=AdventureWorks2000;uid=sa;pwd=YOURPASSWORD;" />
</appSettings>

In addition, add the following web.config setting defining the http handler for the map control under the system.web XML tag:

<httpModules>
<add name="ImageStream" type="MapSuite.Webforms.ImageStream, WebformsEdition" />
</httpModules>

One last setup task is to add our map data to the project. To accomplish this right click on the MapBuilder project and choose Add, then Add Existing Item. Then browse to 'C:\Inetpub\wwwroot\MapSuiteVBSampleApps\SampleData\USA' and add the States.SHP, States.SHX and States.DBF files to the project. These files provide the data necessary to draw the map of the United States.

Now it's time to start coding! Let's begin by declaring some Imports statements at the top so we don't have to type all of those long namespaces. Go ahead and add the following Imports statements to the top of the GetMap.aspx code-behind page GetMap.vb. You can reach the code-behind page by double clicking on the ASPX page or by pressing the F7 function key.

Imports MapSuite
Imports MapSuite.Webforms
Imports MapSuite.Geometry
Imports System.Drawing.Imaging
Imports System.Data.SqlClient
Imports System.Drawing.Drawing2D

The first part we'll focus on is the Page_Load event of GetMap.aspx code-behind where we can enter in the code listed below:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'Put user code to initialize the page here
    Try
        'Clear the Layers so we start from scratch
        Map1.Layers.Clear()
        'Default the report type to the TotalSalesAmt
        Dim ReportType As String = "TotalSalesAmt"
        'Set the Report Type based upon the Query String passed into the URL
        If Request.QueryString.Count > 0 Then ReportType =
        Request.QueryString("ReportType").ToString
        'Set the Map Unit to Decimal Degrees because that is the unit our map data is in
        Map1.MapUnit = MapLengthUnits.DecimalDegrees
        'Load the layer defining the United States
        Dim StatesLayer As New Layer(Me.Server.MapPath("") & "\States.shp", True)
        'Call the BindSalesDataToMap() Function to load are custom sales data
        StatesLayer.CustomFields.Add(BindSalesDataToMap(ReportType))
        'Call the SetMapRendering() Routine to set the display features of the map
        SetMapRendering(StatesLayer, ReportType)
        Map1.Layers.Add(StatesLayer)
        'Set the map to only show the lower 48 states
        Map1.CurrentExtent = New RectangleR(-125, 50, -66, 23)
        'Retrieve Bitmap from the Map Control
        Dim salesMap As Bitmap = Map1.GetBitmap(CInt(Map1.Width.Value), CInt(Map1.Height.Value), False)
        'Write the JPEG of the Map Out using the Response Object
        Response.ContentType = "image/jpeg"
        Response.Charset = ""
        salesMap.Save(Response.OutputStream, ImageFormat.Jpeg)
    Catch ex As Exception
        Throw ex
    End Try
End Sub

The above code block starts out by clearing any existing layers loaded into the map. In a real world example you may want to keep the layers loaded, but for simplicity sake in this example we will just load the map from scratch each time. Next we interrogate the query string to see how we need to build the map (we will describe setting the query string parameter in the report a little later on). The next piece of code loads the States Layer object with the files State.shp file we added to our project earlier. We then pass the State layer into two routines called BindSalesDataToMap() and SetMapRendering(). These routines handle the data binding and map formatting which we will get to shortly. Finally, after all of this is completed we add the states layer to the map control and write the resulting image out using the Response object.

Binding Custom Sales Data to the Map

Now we will look in more detail on how to bind our custom sales data to the map of the United States. Before we can do any binding we must retrieve the same data that our report uses from the AdvetureWorks2000 database. We can do this by using the same SQL statement that we defined for the report earlier. With that completed all we need to do is open up a connection to the database using the connection string we defined earlier in the web.config and execute the SQL statement returning a SQLDataReader object. Now that we have our custom sales data it's time to bind it to the map. We start by dimensioning a salesData variable as a CustomField object. The CustomField object allows us to bind any custom data to an already existing map layer. In our case this is the states layer.

The binding is pretty straightforward. We first set the CustomFieldName property to what we want to name our custom data. In this case we will name it "SalesData". This allows us to reference the custom data through the Map control API just like it was included in the Map from the beginning. Next we have to set the LinkFieldName property, this property acts as the join argument between our custom data and the data inside the States layer. Since we have our custom data grouped by state name we will join it to the map using the STATE_NAME column that is a column in the States.DBF data file.

Now that we have the field name and link field setup, all we have left is to add the custom sales data. To do this we iterate through the SqlDataReader adding each row to the custom data property; setting the key equal to the state name and the value equal to the custom sales data that we selected via the report type parameter. Once this is completed we return the CustomField object from the function so it can be added to the states layer object. You can copy and paste the Function below into your GetMap.aspx page to bind the sales data to the map.

Private Function BindSalesDataToMap(ByVal ReportType As String) As CustomField
    Dim sqlConn As New SqlConnection
    (System.Configuration.ConfigurationSettings.AppSettings("connectionString"))
    Dim sqlCommand As SqlCommand
    Dim salesDataReader As SqlDataReader
    Try
        Dim sql As String = "Select s.Name as 'State', Sum(o.TotalDue) as 'TotalSalesAmt',
        Count(c.CustomerID) as 'OrderCount', " & _
        "Count(Distinct c.CustomerID) as 'CustomerCount' " & _
        "From Customer c Join CustomerAddress ca on c.CustomerID = ca.CustomerID " & _
        "JOIN Address a ON ca.AddressID = a.AddressID " & _
        "JOIN StateProvince s ON a.StateProvinceID = s.StateProvinceID " & _
        "JOIN SalesOrderHeader o ON c.CustomerID = o.CustomerID " & _
        "Where s.CountryRegionCode = 'US' GROUP BY s.Name "
        sqlConn.Open()
        sqlCommand = sqlConn.CreateCommand
        sqlCommand.CommandText = sql
        salesDataReader = sqlCommand.ExecuteReader
        'Define a Custom Field to hold our sales data
        Dim salesData As New CustomField
        salesData.CustomFieldName = "SalesData"
        'This is the field name in the states.shp that will Link us to our custom sales data
        salesData.LinkFieldName = "STATE_NAME"
        Do While salesDataReader.Read
            'Add the Custom Sales Data
            Select Case ReportType
                Case "TotalSalesAmt"
                salesData.CustomData.Add(salesDataReader("State"), FormatCurrency
                (salesDataReader
                ("TotalSalesAmt")))
                Case "NumberOfOrders"
                salesData.CustomData.Add(salesDataReader("State"), salesDataReader("OrderCount"))
                Case "NumberOfCustomers"
                salesData.CustomData.Add(salesDataReader("State"), salesDataReader("CustomerCount"))
            End Select
        Loop
        Return salesData
    Catch ex As Exception
        Throw ex
    Finally
        sqlConn.Close()
    End Try
End Function

Rendering the Map

With the custom sales data included in our map, it's time to set the rendering properties so the map will display in the manner we want. First we start off be defining a Threshold. A Threshold is a mechanism to turn Layers on and off depending upon how far you are zoomed in or zoomed out of the map. In our example it is extremely simple because we always want to show the states Layer no matter what so we just define one Threshold between 0 and 10,000 miles. Next we define a Symbol Renderer that will show all the states with no sales data in a light gray color and add it to the Threshold.

(NOTE: For more information on Layers, Thresholds and Symbol Renderers see this article).

The last thing to setup in the rendering is the class breaks. Class breaks allow us to show a state in a different color depending upon its sales data. For this example we will use three different class breaks: 1) low sales, 2) medium sales and 3) high sales. This will shade the states with high sales numbers in green, medium sales in light green and low sales in light yellow. Once we have our area symbols defined for the different colors we need to decide on what values will be the class breaking points. As you can see by the case statement below, we set different break points based upon the report type parameter passed in. Finally we create the ClassBreakRenderers using the break points we set and then tie them to the custom data field we created in the BindSalesToMap() function called "SalesData". Once the ClassBreakRenderers are setup all we need to do is add them to the SymbolRenderers collection on the Threshold and then add the Threshold to the States Layer. You can copy and paste this routine into the GetMap.aspx page as well to setup the map rendering:

Private Sub SetMapRendering(ByRef StatesLayer As Layer, ByVal ReportType As String)
    'Create Threshold to Display the Map between 0 and 10,000 miles
    StatesLayer.ThresholdUnit = ThresholdUnits.miles
    Dim threshold As New Threshold(10000, 0)
    'Create Symbol Renderer to render the map in Light Gray for states with no data
    Dim symbolRenderer As New SymbolRenderer(New AreaSymbol(New Pen(Color.Black), New
    HatchBrush(HatchStyle.Percent80, Color.LightGray)))
    threshold.SymbolRenderers.Add(symbolRenderer)
    'Set Color for each of the custom sales data ranges
    Dim lowSales As New AreaSymbol(New Pen(Color.Black), New SolidBrush
    (Color.LightGoldenrodYellow))
    Dim mediumSales As New AreaSymbol(New Pen(Color.Black), New SolidBrush(Color.LightGreen))
    Dim highSales As New AreaSymbol(New Pen(Color.Black), New SolidBrush(Color.Green))
    Dim lowerBreak As Double
    Dim higherBreak As Double
    'Set the Break Values for the different Report Types
    Select Case ReportType
        Case "TotalSalesAmt"
            lowerBreak = 1000000 '1 Million
            higherBreak = 10000000 ' 10 Million
        Case "NumberOfOrders"
            lowerBreak = 100
            higherBreak = 1000
        Case "NumberOfCustomers"
            lowerBreak = 10
            higherBreak = 50
    End Select
    'Create some Class Break Renders to show different colors by state based upon sales data
    Dim salesClassBreaks As New SymbolClassBreakCollection
    'Create a Class Break for Low Sales as Light Green
    salesClassBreaks.Add(New SymbolClassBreakRenderer.ClassBreak(lowerBreak, lowSales))
    'Create a Class Break Cap For Medium and High Sales to use
    Dim ClassBreakCap As New SymbolClassBreakRenderer.ClassBreakCap(higherBreak, highSales, mediumSales)
    salesClassBreaks.Add(ClassBreakCap)
    'Tie the Class Break Renderers to our new custom field of SalesStats
    Dim salesClassBreakRenderer As New SymbolClassBreakRenderer("SalesData", salesClassBreaks)
    'Add the Class Break Renderers
    threshold.SymbolRenderers.Add(salesClassBreakRenderer)
    StatesLayer.Thresholds.Add(threshold)
End Sub

At this point we all of the code completed. Set the MapBuilder project as the startup project and set the GetMap.aspx page as the startup page for the project. You can now click the Start button or press the F5 function key to compile and execute the program. You should see your browser pop up with a map similar to the one below:

Adding the Map to the Report

With most of the work behind us, let's now go back to the report and add the map in. Bring the sales report up in layout mode and drag the bottom of the report down some so we have a blank area to insert the map. Drag an image report item from the toolbox onto the report. This will start the image wizard. Go ahead and click the Next button to begin. The first question we have to answer is where the image is located. For this example we need to choose web option.

Next we are prompted for the URL of the image, in our case we want to put in the GetMap.aspx URL that we developed to create the map. So enter http://localhost/MapBuilder/GetMap.aspx into the URL settings box and click next.

The image wizard should successfully retrieve the map and show a screen like the one below. Go ahead and click the Finish button to add the map to the report.

After the map has been added to the report you may want to rearrange the report a little so everything fits together nicely.

Changing the Map Using Report Parameters

Now that we have our map displaying on the report the only thing left to do is change the map based upon what the user chooses from the "Display Map By" report parameter. To add this functionality, make sure your report is in layout mode and click on the image and then choose View, Properties Window from the menu bar. This will display all the properties for the image report item containing the map. The property that we are interested in is the value property. Click on the value property and choose from the drop down list. This will open the expression builder dialog which we will use to programmatically set the URL value for the image using the reports "Display Map By" report parameter. Enter the following expression into the expression area so it matches the image below.

="http://localhost/MapBuilder/GetMap.aspx?ReportType=" & Parameters!MapDisplay.Value

Click OK and change the report mode to preview. Try choosing a different value for the "Display Map By" parameter and watch some of the states change colors to representing the different sales data and class breaks. If everything was done correctly you should have a report that looks similar to the one below.

Summary

In this example we showed that with a minimal amount of code a custom map can be added to a SQL Reporting Services report. We have only scratched the surface of what is possible using mapping technology to give a graphical representation of report data. Several common features like map labeling, point plotting and spatial querying can easily be accomplished using this same type of architecture. One other thing to keep in mind is that the map can easily be changed to include a geographic area of any size. So while this example represents data across the entire United States, the map could easily be built to only display a city, county or a particular state. With end user's demand for information growing year after year, having a tool to show data in a more meaningful way is always helpful.

Other Resources

There are several other resources at ThinkGeo to help you get started to developing feature rich mapping and spatial applications for Microsoft .NET. Some of those resources are listed below. You can always reach a ThinkGeo support representative by phone toll-free at (866) 847-7510 or via email at support@thinkgeo.com.

Sample Applications

There are 35+ sample applications included with Map Suite when you download the trial version that include the source code to show you how to do various operations using the Map Suite component. It is very beneficial to go through those examples and the code. Both VB.NET and C# are included.

View the Map Suite sample applications online

Discussion Forums

The Map Suite Discussion Forums are a great place to get answers to all of your Map Suite development questions. The ThinkGeo Support Team monitors the forums daily and can help you on your way to being successful with Map Suite.

Visit the Discussion Forums online