Microsoft KB Archive/326103

= HOW TO: Display Breaks for Null Data in an Office Web Component Line Chart =

Article ID: 326103

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft Office Web Components
 * Microsoft Office Web Components

-



This article was previously published under Q326103



SUMMARY
 * Plot a Discontinuous Line
 * Troubleshoot the Client Computer

REFERENCES



SUMMARY
This article describes how to chart null data points in the Chart control of Microsoft Office Web Components (OWC).

The technique in this article describes how you can use the Chart rendering events of the component to plot a discontinuous line chart that omits or skips null data points. This technique works only with line type or scatter type charts. For additional information about working with a chart type other than line or scatter, click the article number below to view the article in the Microsoft Knowledge Base:

286317 HOWTO: Create Null (or Omitted) Data Points with the Office Chart Component

back to the top

Plot a Discontinuous Line
The following sample code demonstrates how to plot a discontinuous line. Data points where the value is null are omitted in the line. The sample code uses a line chart, but the same technique can be used with a scatter chart.   Use a text editor or an HTML editor to create a new Web page. Name the Web page MyChart.htm and include the following contents:

   New Page 1

&quot; id=&quot;chsp&quot;

width=&quot;689&quot; height=&quot;652&quot;> To use this Web page interactively, you must have Microsoft Internet Explorer 4.01 Service Pack 1 (SP1) or later and the Microsoft Office XP Web Components or later. Click here to install the Office XP Web Components. See the Microsoft Office Web site for more information.

&quot;> <param name=DisplayTitleBar value=false> <param name=DataType value=XMLData> <param name=XMLData value=&quot;<?xml version="1.0"?>&#13;&#10; <Workbook

xmlns="urn:schemas-microsoft-com:office:spreadsheet"&#13;&#10; xmlns:o="urn:schemas-microsoft-com:office:office"&#13;&#10; xmlns:x="urn:schemas-microsoft-com:office:excel"&#13;&#10; xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"&#13;&#10; xmlns:html="http://www.w3.org/TR/REC-html40">&#13;&#10;

<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">&#13;&#10;

<Author>Microsoft Corporation</Author>&#13;&#10;

<Created>2002-06-27T23:44:24Z</Created>&#13;&#10; <Company>Microsoft Corporation</Company>&#13;&#10; <Version>10.3501</Version>&#13;&#10;

</DocumentProperties>&#13;&#10; <OfficeDocumentSettings

xmlns="urn:schemas-microsoft-com:office:office">&#13;&#10; <DownloadComponents/>&#13;&#10; <LocationOfComponents HRef="file:///\\"/>&#13;&#10; </OfficeDocumentSettings>&#13;&#10;

<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">&#13;&#10; <WindowHeight>10995</WindowHeight>&#13;&#10; <WindowWidth>14235</WindowWidth>&#13;&#10;

<WindowTopX>360</WindowTopX>&#13;&#10; <WindowTopY>105</WindowTopY>&#13;&#10; <ProtectStructure>False</ProtectStructure>&#13;&#10; <ProtectWindows>False</ProtectWindows>&#13;&#10;

</ExcelWorkbook>&#13;&#10; <Styles>&#13;&#10; <Style ss:ID="Default" ss:Name="Normal">&#13;&#10; <Alignment ss:Vertical="Bottom"/>&#13;&#10;

<Borders/>&#13;&#10; <Font/>&#13;&#10; <Interior/>&#13;&#10; <NumberFormat/>&#13;&#10; <Protection/>&#13;&#10; </Style>&#13;&#10; </Styles>&#13;&#10;

<Worksheet ss:Name="Sheet1">&#13;&#10; </Cell>&#13;&#10;

<Cell> </Cell>&#13;&#10; </Row>&#13;&#10;

<Row>&#13;&#10; <Cell> </Cell>&#13;&#10;

<Cell> </Cell>&#13;&#10;

</Row>&#13;&#10;

<Row>&#13;&#10; <Cell> </Cell>&#13;&#10;

</Row>&#13;&#10;

<Row>&#13;&#10; <Cell> </Cell>&#13;&#10;

<Cell> </Cell>&#13;&#10;

</Row>&#13;&#10;

<Row>&#13;&#10; <Cell> </Cell>&#13;&#10;

<Cell> </Cell>&#13;&#10;

</Row>&#13;&#10;

<Row>&#13;&#10; <Cell> </Cell>&#13;&#10;

<Cell> </Cell>&#13;&#10;

</Row>&#13;&#10; </Table>&#13;&#10; <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">&#13;&#10; <ProtectObjects>False</ProtectObjects>&#13;&#10; <ProtectScenarios>False</ProtectScenarios>&#13;&#10;

</WorksheetOptions>&#13;&#10; <PublishObjects

xmlns="urn:schemas-microsoft-com:office:excel">&#13;&#10; <PublishObject>&#13;&#10; <Id>28139</Id>&#13;&#10; <DivID>Book1_28139</DivID>&#13;&#10; <SourceType>SourceSheet</SourceType>&#13;&#10; <HtmlType>HtmlCalc</HtmlType>&#13;&#10; <Location x:HRef="C:\Documents and Settings\mico\Desktop\Page3.htm"/>&#13;&#10;

</PublishObject>&#13;&#10; </PublishObjects>&#13;&#10;

</Worksheet>&#13;&#10; </Workbook>&#13;&#10;&quot;>

<SCRIPT language=VBScript> Dim chartLeft,chartRight Const useMarker = True

Sub window_onload Dim webConst Dim newChart Set webConst = chsp.Constants

' Enable the events on the chart control chsp.EnableEvents = True chsp.AllowRenderEvents = True chsp.AllowPointRenderEvents = True ' Bind the chart to the spreadsheet chsp.Clear Set chsp.DataSource = Spreadsheet1 Set newChart = chsp.Charts.Add newChart.Type = chChartTypeLine ' Rely on certain information specific to line types newChart.SetData webConst.chDimCategories, 0, &quot;a1:a6&quot; newChart.SeriesCollection(0).SetData webConst.chDimValues, 0, &quot;b1:b6&quot; chsp.HasChartSpaceTitle = True chsp.ChartSpaceTitle.caption = &quot;Do not plot null data points.&quot; End Sub

Sub chsp_AfterRender(drawObj,chartObj) Dim webConst Dim oPoint Dim preCoorX,preCoorY Dim coor Set webConst = chsp.Constants If TypeName(chartObj) = &quot;ChSeries&quot; Then ' Initialize the starting point preCoorX = chartObj.Left preCoorY = 0 ' Set the Line style that you want drawObj.Line.DashStyle = webConst.chLineSolid For each oPoint in chartObj.Points ' Do not draw a Line If Null If(oPoint.GetValue(webConst.chDimValues) <> &quot;&quot;) Then Set coor = _ chartObj.valuetopoint(oPoint.GetValue(webConst.chDimCategories), _ oPoint.GetValue(webConst.chDimValues)) If preCoorY > 0 Then drawObj.DrawLine preCoorX, preCoorY, coor.x, coor.y           ' Point to point Line If useMarker Then drawObj.DrawRectangle coor.x - 5, coor.y + 5, _ coor.x + 5, coor.y - 5 End If ' useMarker Else ' Draw only the marker If useMarker Then drawObj.DrawRectangle coor.x - 5, coor.y + 5, _ coor.x + 5, coor.y - 5 End If ' useMarker End If 'preCoorY > 0 ' Set the starting coordinates preCoorX = coor.x           preCoorY = coor.y        Else ' Skip the Line If null by setting the starting y to zero. preCoorY = 0 End If ' (oPoint.GetValue(webConst.chDimValues) <> &quot;&quot;) Next 'oPoint

End If 'TypeName(chartObj) = &quot;ChSeries&quot;

End sub

sub chsp_BeforeRender(drawObj,chartObj, Cancel)

If TypeName(chartObj)=&quot;ChSeries&quot; Then ' This prevents the chart from rendering its typical line plot Cancel.Value = true End If ' typename(chartObj)=&quot;ChSeries&quot;

End sub

</SCRIPT>

Note Replace the <Class Identifier of Chart Control> and <Class Identifier of Spreadsheet Control> in the above code with the Class ID of the chart control and spreadsheet control being used.

For Microsoft Office Chart 10.0

0002E556-0000-0000-C000-000000000046

For Microsoft Office Spreadsheet 10.0

0002E551-0000-0000-C000-000000000046

</li> <li>Start Microsoft Internet Explorer and then move to MyChart.htm.</li> <li>After the page renders in Internet Explorer, you notice a chart that contains one line series. The line contains a break where the data is NULL. The chart is bound to the Spreadsheet data. The value for &quot;Pears&quot; in cell B3 is NULL.</li></ol>

back to the top

Troubleshoot the Client Computer
The client computer may report the following error message:

&quot;Error: Object doesn't support this property or method: 'chsp.Constants'&quot;

This error message means that the system does not have OWC 10 or OWC 11 installed. To install the Microsoft Office XP Web Components on the client, visit the following Microsoft Web site:

Office XP Tool: Web Components

To install the Microsoft Office 2003 Web Components, visit the following Microsoft Web site:

Office 2003 Add-in: Office Web Components

back to the top

<div class="references_section">