MAPPOINT Q & A

Top | Post New Question | Read All | MP2K Asks Microsoft

635 Will Vereen - March 27, 2002

I have territories based on Counties. I have also imported sales by county. How can I get the sales to display by territory and to react dynamically to changes in county territory assignments?

Answer:

The only way to manipulate territories is to import them; there is no link or other programmatic manipulation.

So you have to do something like the following (this is untested as written and doesn't include variable declarations and error handling):

    arrFields(1, 1) = "StateCode": arrFields(1, 2) = geoFieldRegion1
    arrFields(2, 1) = "CountyName": arrFields(2, 2) = geoFieldRegion2
    arrFields(3, 1) = "TerritoryAssignment": arrFields(3, 2) = geoFieldData
    arrFields(4, 1) = "TerritorySales": arrFields(4, 2) = geoFieldData
    
    Set cn = New ADODB.Connection
    cn.ConnectionTimeout = 120
    cn.CommandTimeout = 120
    cn.Open "...insert database connection string..."
    
    Set objMap = objMapPoint.ActiveMap
    'Clear the map.
    For Each objDataSet In objMap.DataSets
        If Not objDataSet.Name = "My Pushpins" Then
            objDataSet.Delete
        End If
    Next
    
    With objMap.DataSets
        gstrUDLLocation = "...location of UDL pointer to database..."
        ' vwMapSalesTerritories is a SQL Server view that contains fields
        ' that defines the territories.
        sConnection = gstrUDLLocation & "!vwMapSalesTerritories"
        Set objDS = .ImportTerritories(sConnection, , _
                    geoCountryUnitedStates, geoDelimiterTab, _
                    geoImportFirstRowNotHeadings)
    End With
    Set objDS = Nothing
    
    With objMap.DataSets
        ' vwMapSalesByTerritory is a SQL Server view that contains sales
        ' data by territory returning the arrFields array.
        sConnection = gstrUDLLocation & "!vwMapSalesByTerritory"
        Set objDS = .ImportData(sConnection, _
                    arrFields, _
                    geoCountryUnitedStates, _
                    geoDelimiterDefault)
    End With
    
    objDS.DisplayDataMap DataMapType:=geoDataMapTypeShadedArea, _
                        DataField:=objDS.Fields("TerritoryAssignment"), _
                        ShowDataBy:=geoShowByRegion2, _
                        CombineDataBy:=geoCombineByNone, _
                        DataRangeType:=geoRangeTypeUniqueValues, _
                        DataRangeOrder:=geoRangeOrderDefault, ColorScheme:=3
    
    objDS.ZoomTo
    objDS.DataMap.LegendTitle = txtMapTitle.Text
    objMap.MapStyle = geoMapStyleData

After this is executed, the territories are drawn and the sales data is shown on a tooltip that is displayed when you hover over a county.

- Walt Cygan

Post New Answer / Follow-up

	MP2Kmag Internet