Using Format Maps to Conditionally Format Data Points and Data Series
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Michael Stowe
Microsoft Corporation
April 2001
Applies to:
Microsoft® Office XP Chart Component
Summary: The Microsoft Office XP Chart component introduces a new feature that allows you to conditionally format data series on a chart. This article provides several examples that illustrate how to use this new feature. (7 printed pages)
Download ODC_ocwebFormat.exe.
Contents
Introduction The Format Map Object Model Applying a Format Map to a Data Series Format Map Examples
Introduction
Formatting can be an effective visualization tool; it can increase the visual density of a display while emphasizing your ideas. The Microsoft Office XP Chart Component introduces format maps, a new feature that allows you to conditionally format the data series on your charts. This article provides several examples that illustrate how you can make effective use of format maps in your charts.
The Format Map Object Model
Format maps extend the ChSeries object. A format map consists of three new objects (ChFormatMap, ChSegment, and ChSegmentBoundary) and one new collection (ChSegments), with their related methods and properties.
The following table describes some of the key properties and methods that apply to creating format maps.
Table 1. Key object model members related to format maps
| Object | Member | Type | Description |
|---|---|---|---|
| ChFormatMap | Segments | Property | Accesses the ChSegments collection in a format map. |
| ChSegments | Add | Method | Adds a new segment to the format map. |
| ChSegment | Begin | Property | Returns a ChSegmentBoundary object that represents the beginning of a segment. |
| ChSegment | End | Property | Returns a ChSegmentBoundary object that represents the end of a segment. |
| ChSegmentBoundary | Value | Property | The value of the boundary. |
| ChSegmentBoundary | ValueType | Property | The type of value used to calculate the boundary. |
| ChSegmentBoundary | Interior | Property | Returns a ChInterior object. You can use the properties of the returned object to format the segment. |
| ChSegmentBoundary | Border | Property | Returns a ChBorder object. You can use the properties of the returned object to format the segment. |
| ChSegmentBoundary | Line | Property | Returns a ChLine object. You can use the properties of the returned object to format the segment. |
Applying a Format Map to a Data Series
Figure 1. X-Y (Scatter) chart
The chart in Figure 1 is a standard X-Y (Scatter) chart. The chart shows the correlation between the total amount of revenue grossed by the movie and the number of theater screens that exhibited the movie on its opening weekend.
A standard X-Y (Scatter) chart only supports two variables. What if you want to evaluate the data based on a third variable? The answer is to use a format map.
A format map is made up of ChSegment objects. Use the Add method to add a ChSegment object to a ChFormatMap object. The following line of code illustrates how to add a segment to a data series:
Set segFirstSegment = ChartSpace1.SeriesCollection(0) _
.FormatMap.Segments.Add
Once you have added a segment to the format map, you need to specify the boundaries of the segment. To do this, set the Value property of the ChSegmentBoundary object returned by the Begin and End properties of the ChSegment object as in the following example:
segFirstSegment.Begin.Value = 0
segFirstSegment.End.Value = 100
Now that the segment boundaries have been established, you can specify the formatting to apply to the segment. You can format the following attributes of the segment:
- Various properties of the interior of the segment, such as the color or gradient fill
- Properties of the segment border
- Properties of the line, such as weight and color, if the chart is a Line chart or X-Y (Scatter) chart
- Properties of the font used for the series labels
The following example formats the segment so that it displays in the color yellow.
segSegment1.Begin.Interior.Color = "Yellow"
segSegment1.End.Interior.Color = "Yellow"
Some properties, such as the font size, line weight, and interior color of a segment can be scaled within a segment. To do this, you must pass different values for the beginning and ending values of the segment.
For example, suppose you want the smaller values at the beginning of the segment to be displayed in white, then larger values to be displayed in a light shade of red, and finally the largest values in the segment to be displayed in dark red. The following example illustrates how to do this.
segSegment1.Begin.Interior.Color = "White"
segSegment1.End.Interior.Color = "Red"
Once you have specified the formatting for the segment boundaries you must specify how you want the Chart Component to interpolate between the formatting between the segment boundaries. There are three properties of the segment to consider: Divisions, HasAutoDivisions, and HasDiscreteDivisions.
Set the HasAutoDivisions property to True if you want the Chart Component to assign the divisions in formatting automatically. Set the Divisions property if you want to specify the number of divisions in formatting. Set the HasDiscreteDivisions property to True if you want abrupt changes in formatting; set it to False if you want more changes to occur.
Once you have added and formatted one or more segments, you must use the SetData method of the ChChart object to bind the format map to the data that will be used to calculate the format map. It is important to note that this does not have to be the same data that is already plotted in the data series. The following example binds the format map to the Opening Gross column in the data source.
ChartSpace1.SetData ChartSpace1.Constants.chDimFormatValues, _
ChartSpace1.Constants.chDataBound, "Opening Gross"
Format Map Examples
Example 1. Classifying Movie Grosses Based on Their Opening Weekend Gross
Figure 2. Chart from Figure 1 after applying this example
In this example, a format map with three segments will be created. The first segment will consist of movies that grossed between $50 million and $100 million on their opening weekend. The data points that represent these movies will be displayed in yellow. The second consists of movies that grossed between $10 million and $50 million on their opening weekend. The data points that represent these movies will be displayed in purple. The second consists of movies that grossed $10 million or less their opening weekend. The data points that represent these movies will be displayed in blue.
To see this example, open the Format Map Examples 1.htm file in the sample download and then click Example 1.
Example 2. Highlighting the Leaders and Trailers
Figure 3. Chart from Figure 1 after applying this example
The first format map example visually sorted the data into discrete bins based on the opening gross. Now let's suppose you want to highlight the movies that had the best and worst opening weekend upon their percentile within the data set. The second example illustrates how to do this by highlighting the top 5% and lowest 20% of the movies based on their opening weekend gross.
To see this example, open the Format Map Examples 1.htm file in the sample download and then click Example 2.
Example 3. Interpolating Formatting to Display Trends
In the previous examples, the data was visually sorted into discrete groups. If a data point fell within a segment, then that segment's formatting was applied to the data point.
Figure 4. Northwind Trader stock data
Figure 4 shows a rather simple line chart that plots the closing stock price for the Northwind Traders Company over the course of a year. It would be useful to display the trading volume of the stock. To do this, we could add another data series to the chart. To do this, you would have to add a second value axis, making the chart unnecessarily complex. Or, you could apply a format map to the existing data series that adds the trading volume of the stock.
In this example, the thickness of the line is varied based on the trading volume of the stock.
Figure 5. Northwind Traders stock chart after format map
To see this example, open the Format Map Examples 2.htm file in the sample download and then click Example 1.