In this walkthrough you'll add a map to a report showing order totals for the US states, summarized by state. The report without the map is very simple, it just lists the total of all orders for each state. Here's the whole of it:
OrderValue StateName
16325.15 Alaska
3490.02 California
115673.39 Idaho
1947.24 Montana
52245.9 New Mexico
30393.93 Oregon
31001.65 Washington
12489.7 Wyoming
You will add a map to this report that will fill each state with a color ranging from green for states with no orders to shades of yellow and red depending on the total orders amount. Additionally, each state will have a circular mark with the diameter proportional to the total, and a label stating that total. Finally, you'll add two small inset maps to show Alaska and Hawaii.
Note that this walkthrough uses the following files:
Complete the following steps:
Add a new report in the designer, with C1NWind.mdb as the data source, with the following SQL query:
SELECT Orders.ShipRegion, Orders.ShipCountry, StateNamesGeo.StateName,
Sum([Order Details].UnitPrice*[Order Details].Quantity) AS OrderValue,
(select Longitude from StateNamesGeo where StateNamesGeo.Abbr = Orders.ShipRegion) as Longitude,
(select Latitude from StateNamesGeo where StateNamesGeo.Abbr = Orders.ShipRegion) as Latitude
FROM ((Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID)
INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID)
INNER JOIN StateNamesGeo on Orders.ShipRegion = StateNamesGeo.Abbr
WHERE Orders.ShipCountry = "USA"
GROUP BY Orders.ShipRegion, Orders.ShipCountry, StateNamesGeo.StateName
ORDER BY Orders.ShipRegion;
Use the report wizard to add OrderValue and StateName fields to the detail section, run the report to make sure that it prints data shown above.
You'll add the map to the report's header:
Set the map's properties as follows (only non-defaults are shown here):
Note that because we are showing the map of the US, we set the coordinates manually as needed (in particular, we leave enough space on the right for the Alaska and Hawaii insets).
Open the MarkerStyles collection, add a single style to it. Set its properties as follows:
This style will be used to draw circle markers on states with size indicating the orders total. Note that the fill color is semi-transparent which works better in this case. Note also that the size of the marker is proportional to the square root of the order value (total). The style name (msTotalSales) will be used to reference this style.
You may set other properties (font, stroke and text colors) as you like.
You'll divide all states, depending on their orders total, into 6 groups:
So you'll need to create a KML item style for each of those groups. Open the KmlItemStyles collection, and add 6 styles with names corresponding to groups listed above, and FillColor values to differentiate the states on the map:
The names are very important here as we will use them in KML item expressions to select a style according to the state's order totals value.
The most important part of our map is the KML layer showing the state bounds and filling the states with the appropriate colors. To add it, go to the map's Layers collection editor and add a KML layer. Set its properties as follows:
KmlFileName: us_states_abbr.kmz (specifying the file name without the path will load the file from the same location as the report definition);
RecordSource:
SELECT Orders.ShipRegion,
Sum([Order Details].UnitPrice*[Order Details].Quantity) AS OrderValue
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID)
INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID
WHERE Orders.ShipCountry = "USA"
GROUP BY Orders.ShipRegion
ORDER BY Orders.ShipRegion;
ItemFilterExpr: kmlItemName=ShipRegion (ensures that KML item expressions, in particular the style used to draw the KML item, is evaluated with data for the state corresponding to the current KML item);
KmlVisibleExpr: kmlItemName<>"AK" (ensures that the main map does not show Alaska);
ItemStyleExpr:
iif (OrderValue > 100000, "ks100kup", iif (OrderValue > 50000, "ks50k100k", iif (OrderValue > 30000, "ks30k50k", iif (OrderValue > 10000, "ks10k30k", iif (OrderValue > 0, "ks0k10k", "ksNoOrders" )))))
The above expression evaluates to one of our KML item style names (see above) depending on the current OrderValue which, as per our RecordSource, is the sum of all orders for a state, and ItemFilterExpr specified above ensures that this expression evaluates for the state currently being loaded from the KML file.
To make the map even more visual, you'll also add a points layer with circular markers placed on states, sized proportionally to the state's total orders. To do it, add a PointsLayer to the Layers collection, and move above the KML layer so that it shows on top of it when the map is rendered. Set the layer properties as follows:
MarkerStyleExpr: "msTotalSales" (this will use the style that we already added to the map's MarkerStyles collection; proportional sizing is built into that style so no need to do anything else here)
Latitude: Latitude (use spatial data provided by the record source).
Longitude: Longitude (use spatial data provided by the record source).
You'll add two legends to our map: a legend that is just a title, in the top right corner, and a color key to our KML item styles, in the bottom right.
To add the color key, add another legend, set its alignment to BottomRight, and open its Items collection to add the following items:
We will add two inset maps, for Alaska and Hawaii. To do it:
Both inset maps will reuse styles defined on the main map, so no styles need to be added to the inset maps' style collections.
Layers will duplicate the layers of the main map, so add two layers to the Layers collection of each inset map:
A points layer, with MarkerStyleExpr set to "msTotalSales", and MarkerVisibleExpr set to
StateName = "Alaska"
("Hawaii" for the Hawaii inset), and all other properties as in the points layer of the main map.
A KML layer, with all properties (including RecordSource) copied from the corresponding properties of the KML layer of the main map, but with ItemVisibleExpr set to
kmlItemName="AK"
(kmlItemName="HI" for Hawaii). In particular note that ItemStyleExpr will work even though style names it evaluates to reference styles in another map's style collection.
Finally, add a legend with a single fixed text to each inset map, with the state's name.
That's it. Now run the report and make sure it works.