INFO: Using Timescale Axes with the Office Chart Component (289288)


The information in this article applies to:

  • Microsoft Office 2003 Web Components
  • Microsoft Office XP Web Components
This article was previously published under Q289288

SUMMARY

The timescale axis feature of the Microsoft Office Chart component provides several benefits:
  • Data may be grouped on a time interval of your choice. You can choose to group data in day, week, month, quarter, or year intervals.
  • Timescale axes are continuous, so any missing dates in your category data are automatically filled in on the axis so that data points always appear in the correct chronological location.
  • When you use a grouping for your data, you can plot a total (such as a sum or average) for each group.
This article describes how the Office XP Chart component might automatically apply a timescale to a chart axis and discusses ways in which you can customize the timescale programmatically. A code sample is also presented to illustrate the different ways that you can use the Chart component to plot chronological data.

MORE INFORMATION

When you use dates for categories on a chart, the Chart component may automatically use a timescale for the category axis if the first, last, and middle category values are dates. When the Chart component applies a timescale axis, it also determines a grouping for your data to minimize clutter on the category axis. The Chart component considers the number of days your category set represents and uses an algorithm to determine the grouping; the Chart component may decide to use a grouping that represents a number of days, weeks, months, quarters, or years. As illustrated in the demonstration presented later in this article, you can override the default grouping by setting the GroupingUnit and GroupingUnitType properties for the axis object. Grouping information is obtained from the first (innermost) axis; therefore, the GroupingUnit and GroupingUnitType properties are ignored for all other axes.

Because time is continuous, a timescale axis plots data continuously and evenly with no gaps in time. If the Chart component automatically applies a timescale to your category axis and you want to disable the timescale functionality, you can set the GroupingType property to chAxisGroupingNone.

When your data is grouped into intervals greater than one day, the data is grouped into bins and a function is used to calculate and plot a representation of the data in each bin. The default behavior is to plot the sum of the bin values. You can set the GroupingTotalFunction property of the axis to instead plot counts, averages, minimums, and maximums for bin values.

By default, the category axis displays tick marks and labels using the same grouping that you specify for the timescale. You can modify the intervals at which tick marks and labels appear by using the TickMarkUnitType and TickLabelUnitType properties for the axis. This is useful in situations in which you want to show greater detail in the plot but also minimize tick mark and label clutter on the axis. For example, suppose that you want to show data point markers in a plot for 52 weeks using week intervals. This automatically shows 52 tick labels on the category axis. To minimize clutter for the labels on the category axis, you can change the units for the tick labels to months or quarters to display fewer labels while maintaining the correct time relationship between the plotted data and the labels. This is illustrated in the following demonstration.

Demonstration

  1. Paste the following code into any text editor and save it as TimeScale.htm.
    <html>

<body>
<p align="Left"/>
<button id="btnChart1" style="width:30%">TimeScale With Missing Dates</button><br/><br/>
<button id="btnChart2" style="width:30%">TimeScale With Year (or Week) Grouping </button><br/><br/>
<button id="btnChart3" style="width:30%">TimeScale With Two Day Grouping</button><br/><br/>
<object classid="clsid:0002E551-0000-0000-C000-000000000046" id="SSheet" width="30%" height="75%">
</object>&#xa0;&#xa0;
<object classid="clsid:0002E556-0000-0000-C000-000000000046" id="CSpace" width="65%" height="75%">
</object>
</body>

<script language="VBScript">

Dim c
Set c = CSpace.Constants

Function BuildWorksheet1()
    
    'Builds a worksheet with dates in A2:A5 and numeric values representing
    'sales figures in B2:B5.
    Dim oSheet
    Set oSheet = SSheet.Worksheets(1)
    oSheet.Cells.Clear
    oSheet.Range("A1:B1").Value = Array("Date", "Sale")
    oSheet.Range("A2:B2").Value = Array(#1/1/2001#, 3000)
    oSheet.Range("A3:B3").Value = Array(#3/5/2001#, 1850)
    oSheet.Range("A4:B4").Value = Array(#3/23/2001#, 2790)
    oSheet.Range("A5:B5").Value = Array(#6/7/2001#, 4925)
    oSheet.Range("A2:A5").NumberFormat = "mmm d, yyyy"

End Function

Function BuildWorksheet2()

    'Builds a worksheet containing one year (365 days) of data in A1:B366. The first
    'column represents dates and the second represents temperatures.
    Dim aTempBase, StartDate
    Dim arr(364, 1)
    StartDate = #1/1/2001#
    aTempBase = Array(25, 22, 38, 50, 60, 68, 74, 81, 62, 50, 45, 32)
    For d = 0 To 364
        arr(d, 0) = StartDate + d
        arr(d, 1) = aTempBase(Month(arr(d, 0)) - 1) + CInt(Rnd() * 20)
    Next
    Dim oSheet
    Set oSheet = SSheet.Worksheets(1)
    oSheet.Cells.Clear
    oSheet.Range("A1:B1").Value = Array("Date", "Temp")
    oSheet.Range("A2:B366").Value = arr
    oSheet.Range("A2:A366").NumberFormat = "mmm dd"

End Function

Function btnChart1_OnClick()

    BuildWorksheet1
    
    'Bind the Chart component to the spreadsheet. Categories are in A2:A5 and
    'values are in B2:B5.
    CSpace.Clear
    Set CSpace.DataSource = SSheet
    CSpace.SetData c.chDimCategories, c.chDataBound, "A2:A5"
    CSpace.SetData c.chDimValues, c.chDataBound, "B2:B5"
    
    'Prompt to disable timescale functionality.
    Dim RetVal
    RetVal = MsgBox("Disable time scale functionality?", vbYesNo)
    If RetVal = vbYes Then
        CSpace.Charts(0).Axes(chAxisPositionCategory).GroupingType = c.chAxisGroupingNone
    End If
    
End Function

Function btnChart2_OnClick()
    
    BuildWorksheet2
    
    'Bind the Chart component to the spreadsheet and plot 365 days of temperature data.
    'Ccategories are in A2:A366 and values are in B2:B366.
    CSpace.Clear
    Set CSpace.DataSource = SSheet
    CSpace.SetData c.chDimCategories, c.chDataBound, "A2:A366"
    CSpace.SetData c.chDimValues, c.chDataBound, "B2:B366"
    
    'The data is automatically grouped into months using a sum function. Change
    'this to a Max function so that the maximum temperature for each month is
    'plotted.
    CSpace.Charts(0).Axes(c.chAxisPositionCategory).GroupingTotalFunction = c.chFunctionMax
    
    'Prompt to ask if you want to change the plot bins to weeks and use a month interval
    'for the tick labels to minimize clutter on the category axis.
    RetVal = MsgBox("Plot data in week intervals but leave tick labels in month intervals?", vbYesNo)
    If RetVal = vbYes Then
        Dim oAxis
        Set oAxis = CSpace.Charts(0).Axes(c.chAxisPositionCategory)
        oAxis.GroupingUnit = 1
        oAxis.GroupingUnitType = c.chAxisUnitWeek
        oAxis.TickLabelUnitType = c.chAxisUnitMonth
        oAxis.HasMajorGridlines = True
        CSpace.Charts(0).GapWidth = 300
    End If
    
End Function

Function btnChart3_OnClick()

    BuildWorksheet2
    
    'Bind the chart to the spreadsheet and plot data for mid-January to mid-March.
    'Categories are in A17:A70 and values are in B17:B70.
    CSpace.Clear
    Set CSpace.DataSource = SSheet
    CSpace.SetData c.chDimCategories, c.chDataBound, "A17:A70"
    CSpace.SetData c.chDimValues, c.chDataBound, "B17:B70"
    CSpace.Charts(0).Type = c.chChartTypeSmoothLineMarkers
    
    'Add an additional category axis.
    'The first (innermost) axis has a grouping type of days and the second (outermost)
    'axis has tick lables displayed in month intervals.
    Dim oAxis1, oAxis2
    Set oAxis1 = CSpace.Charts(0).Axes(c.chAxisPositionCategory)
    Set oAxis2 = CSpace.Charts(0).Axes.Add(oAxis1.Scaling)
    oAxis1.GroupingUnit = 2
    oAxis1.GroupingUnitType = c.chAxisUnitDay
    oAxis1.NumberFormat = "d"
    oAxis2.NumberFormat = "mmmm"
    oAxis2.TickLabelSpacing = 1
    oAxis2.TickLabelUnitType = c.chAxisUnitMonth
    oAxis2.TickMarkUnitType = c.chAxisUnitMonth
    oAxis2.HasMajorGridlines = True

End Function

</script>
</html>


    Note If you are using Office 2003 you may have to change the classid accordingly.
  2. Start Internet Explorer and browse to Timescale.htm. A Web page containing a spreadsheet and a chart is rendered.
  3. Click Timescale with Missing Dates. The sales data in cells A2:B5 in the spreadsheet is plotted in the chart. Because the data used for the category axis are dates, the Chart component automatically applies a timescale to the category axis and determines a two week grouping interval. Note that the axis shows a continuous timescale even though data for every two week interval is not represented in the spreadsheet. A message box prompts you to disable the timescale feature. Click Yes to disable the timescale (this sets GroupingType to chAxisGroupingNone) and observe the difference in the manner in which the data is plotted.
  4. Click TimeScale With Year (or Week) Grouping. One year (365 days) of temperature data in cells A2:B366 of the spreadsheet is bound to the chart. The chart automatically applies a timescale and opts to group the data in month intervals. The maximum temperature value for each month is displayed because the GroupingTotalFunction property is set to chFunctionMax. A message box prompts you to plot the data in week intervals while leaving the tick labels in month intervals. Click Yes and observe the changes to the plot.
  5. Click TimeScale with Two Day Grouping. The chart is bound to cells A17:B70 on the spreadsheet; A17:B70 represents daily temperature data for mid-January through mid-March. Note that two levels for the timescale axis appear: the innermost level displays in two day intervals and the outermost level displays in one month intervals.

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

288907 INFO: Binding the Office XP Chart Component to a Data Source

For more information, see the following Microsoft Web sites:

Microsoft Office Web Components
http://support.microsoft.com/ofd

Microsoft Office Developer Center
http://msdn.microsoft.com/office/

(c) Microsoft Corporation 2001, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.

Modification Type:MinorLast Reviewed:8/23/2005
Keywords:kbinfo KB289288 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.