Microsoft KB Archive/303016

From BetaArchive Wiki

Article ID: 303016

Article Last Modified on 3/9/2007


APPLIES TO

  • Microsoft .NET Framework 1.1 Service Pack 1
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Office Web Components
  • Microsoft .NET Framework 1.1 Service Pack 1


This article was previously published under Q303016


IN THIS TASK

SUMMARY

This step-by-step article describes how to create a chart using the Office XP Chart Component with data from a DataSet. The solutions that this article presents use Visual Basic .NET and ASP.NET.

back to the top

Sources of data for the Chart Component

The Office XP Chart Component supports different types of data sources, such as the following:

  • The Chart Component can bind to a data source that implements the IDataSource interface, a standard COM interface for data source controls. The other Office XP Web Components (that is, the PivotTable Component, the Spreadsheet Component, and the DataSource controls) implement IDataSource, and may be potential data sources for a chart.
  • The Chart Component can bind to an ADO Recordset object.
  • The Chart Component can bind to an XML stream if the data is persisted to the stream in the XML persistence format.
  • A Chart Component can use literal data in the form of a delimited string or array; a chart that uses literal data is not considered bound.

The DataSet class in ADO.NET does not implement IDatasource, and the .NET Framework does not support a direct means to convert an ADO.NET DataSet object to an ADO Recordset object. If you have a DataSet from which you want to create a chart, you can use one of the two solutions that are presented in this article. You can either transform the DataSet into a stream that uses the XML persistence format for ADO, or you can generate literal data from the DataSet.

back to the top

Use the XML persistence format

This solution describes how to transform a DataSet into an XML stream to which the Chart Component can bind. This solution has two parts: a server-side HTTP handler that creates a DataSet and transforms it into an XML stream, and client-side script that binds the Chart Component to the XML stream.

Create an HTTP handler to create a DataSet and transform it into an XML data source

  1. On the File menu in Visual Studio .NET, click New, and then click Project.
  2. Click Visual Basic Projects, click the Empty Web Project template, and then name it ChartDemo1.
  3. On the Project menu, click Add Reference, click to select the following assemblies, and then click OK:
    • System.Data.Dll
    • System.Dll
    • System.Web.Dll
    • System.XML.Dll


  4. On the Project menu, click Add Class, name the class MakeData.vb, and then click Open.

  5. Replace the code in MakeData.vb with the following.

    Note You must change User ID <username> and password = to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

    Imports System.Web
Imports System.Xml
Imports System.Xml.Xsl
Imports System.Data
Imports System.Data.SqlClient

Public Class MakeData
    Implements IHttpHandler

    Public ReadOnly Property IsReusable() As Boolean _
    Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property

    Public Sub ProcessRequest(ByVal context As HttpContext) _
    Implements IHttpHandler.ProcessRequest
        Dim sConn As String = "User ID=<username>;Password=<password>;Initial Catalog=Northwind;Data Source=YourSQLServer;"
        Dim sSQL As String = _
            "SELECT LastName, Sum([UnitPrice]*[Quantity]*(1-[Discount])) AS OrderTotal " & _
            "FROM Employees INNER JOIN (Orders INNER JOIN [Order Details] ON " & _
            "Orders.OrderID = [Order Details].OrderID) ON Employees.EmployeeID = " & _
            "Orders.EmployeeID GROUP BY LastName"

        'Connect to the data source.
        Dim nwindConn As SqlConnection = New SqlConnection(sConn)
        nwindConn.Open()

        'Build a dataSet for Employee Sales.
        Dim ds As DataSet
        ds = New DataSet("MyDataset")
        Dim da As SqlDataAdapter = New SqlDataAdapter(sSQL, nwindConn)
        da.Fill(ds)

        'Transform the dataSet using the stylesheet.
        Dim xmlDoc_ds As XmlDataDocument = New XmlDataDocument(ds)
        Dim xslTran As XslTransform = New XslTransform()
        xslTran.Load(context.Server.MapPath("Rowset.xslt"))

        'Return the persisted recordset.
        context.Response.ContentType = "text/xml"
        context.Response.Charset = ""
        xslTran.Transform(xmlDoc_ds, Nothing, context.Response.Output)

    End Sub

End Class

    NOTE: In the code, change the connection string in sConn to a valid connection string for a computer that is running Microsoft SQL Server and that contains the Northwind sample database.

  6. On the Project menu, click Add New Item, click the Web Configuration File template, and then click Open.

  7. Replace the contents of Web.Config with the following:

    <?xml version="1.0" encoding="utf-8" ?>
<configuration>
   <system.web>
      <httpHandlers>
         <add verb="*" path="MakeData.aspx" type="ChartDemo1.MakeData, ChartDemo1" />
      </httpHandlers>
   </system.web>
</configuration>
  8. On the Project menu, click Add New Item, click the XSLT File template, name the file Rowset.xslt, and then click Open.

  9. Replace the contents of Rowset.xslt with the following:

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="xml" omit-xml-declaration = "yes" indent="yes"/>
 <xsl:template match="/">
 <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
    xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
    xmlns:rs="urn:schemas-microsoft-com:rowset"
    xmlns:z="#RowsetSchema">
 <s:Schema id='RowsetSchema'>
 <s:ElementType name='row' content='eltOnly' rs:CommandTimeout='30'>
 <s:AttributeType name="LastName" rs:number="1" rs:writeunknown="true">
 <s:datatype dt:type="string" dt:maxLength="10" rs:maybenull="false" />
 </s:AttributeType>
 <s:AttributeType name='OrderTotal' rs:number='2' rs:nullable='true'>
 <s:datatype dt:type='float' dt:maxLength='8' rs:precision='15' rs:fixedlength='true'/>
 </s:AttributeType>
 <s:extends type='rs:rowbase'/>
 </s:ElementType>
 </s:Schema>
 <rs:data>
 <xsl:for-each select="MyDataset/Table">
 <xsl:element name="z:row" namespace="#RowsetSchema">
 <xsl:attribute name="LastName">
 <xsl:value-of select="LastName"/>
 </xsl:attribute>
 <xsl:attribute name="OrderTotal">
 <xsl:value-of select="OrderTotal"/>
 </xsl:attribute>
 </xsl:element>
 </xsl:for-each>
 </rs:data>
 </xml>
 </xsl:template>
 </xsl:stylesheet>
  10. Follow these steps to test the MakeData HTTP handler:
    1. On the Build menu, click Build Solution.
    2. Start Microsoft Internet Explorer and then browse to http://YourWebServer/ChartDemo1/MakeData.aspx
      (where YourWebServer is the name of your Web server).
    3. Examine the XML that the browser displays. The XML uses the schema that is defined for the XML persistence format.

back to the top

Bind the Office XP Chart Component to the XML data source

  1. Open the ChartDemo1 Web Application project in Visual Studio .NET.
  2. On the Project menu, click Add HTMLPage.
  3. Name the new page Default.htm, and then click Open. Default.htm appears in Design view.
  4. Right-click Default.htm, click Properties, change the Target Schema to Internet Explorer 5.0, and then click Apply.
  5. Change Default Scripting Language to VBScript, and then click OK.
  6. Follow these steps to add an Office XP Chart Component to Default.htm:
    1. On the Tools menu, click Customize Toolbox.
    2. In the COM Components list, click Microsoft Office Chart 10.0, and then click OK.
    3. Drag a Chartspace control onto Default.htm.
  7. Follow these steps to add a Button control to Default.htm:
    1. In the Toolbox window, click HTML.
    2. Drag a Button control onto Default.htm.
    3. Change the Value property of the button to Make Chart.
  8. Right-click the button that you added to Default.htm, and then click View Client Script.

  9. Add the following code to the Button1_onclick event:

        'Bind the chart to the recordset.
   Dim c
   Set c = Chchartspace1.Constants
   Chchartspace1.ConnectionString = "Provider=MSPersist"
   Chchartspace1.CommandText = "http://YourWebServer/ChartDemo1/MakeData.aspx"
   Chchartspace1.HasMultipleCharts = False
   Chchartspace1.PlotAllAggregates = c.chPlotAggregatesSeries
   Chchartspace1.SetData c.chDimCategories, c.chDataBound, "LastName"
   Chchartspace1.SetData c.chDimValues, c.chDataBound, "OrderTotal"

    NOTE: Change YourWebServer in the URL to the name of your Web server.

  10. On the File menu, click Save Default.htm.
  11. Follow these steps to test the solution:
    1. Start Internet Explorer and browse to http://YourWebServer/ChartDemo1/Default.htm
      (where YourWebServer is the name of your Web server).
    2. Click Make Chart. Note that a column chart with data from the DataSet appears.

back to the top

Use literal data

This solution describes how to generate literal data from a DataSet, and then use that literal data to build a chart. This solution uses an ASP.NET Web Form. The code that runs server-side generates a DataSet, and then returns the DataSet to the client as an XML data island; a PlaceHolder server control is used for the data island. Client-side script traverses the nodes in the data island to build arrays for the categories and values that are used to build the chart.

  1. In Visual Basic ASP.NET, create a new Web Application project named ChartDemo2. Webform1.aspx is created by default and is shown in Design view.
  2. Right-click WebForm1, and then click View HTML Source.

  3. Replace the following element

    <form id="Form1" method="post" runat="server">
</form>

    with the following:

    <form id="Form1" method="post" runat="server">
    <asp:PlaceHolder id="PlaceHolder1" runat="server"></asp:PlaceHolder>
</form>
<OBJECT id="CSpace" classid="clsid:0002E556-0000-0000-C000-000000000046"
style="Z-INDEX: 101; LEFT: 9px; WIDTH: 576px; POSITION: absolute; TOP: 43px; HEIGHT: 348px"
VIEWASTEXT></OBJECT>

<button id="Mybtn" style="Z-INDEX: 103; LEFT: 604px; POSITION: absolute; TOP: 46px"
    type="button">Chart Data</button>
<script language="vbscript">

Function Mybtn_OnClick

    'Extract the contents of the XML Data island and insert it into two
    'arrays: one array for the chart categories and one array for the 
    'chart values.
    Dim nCount, nodes, i
    Set nodes = dsXML.XMLDocument.childNodes.item(0).childNodes
    nCount = nodes.length
    Redim aNames(nCount)
    Redim aTotals(nCount)
    For i=1 to nCount
       aNames(i) = nodes.item(i-1).ChildNodes.item(0).text
       aTotals(i) = nodes.item(i-1).ChildNodes.item(1).text
    Next
    
    'Create a chart from the array data.
    'Bind the chart to the recordset.
    Dim c
    Set c = CSpace.Constants
    Dim oChart, oSer
    CSpace.Clear
    Set oChart = CSpace.Charts.Add
    Set oSer = oChart.SeriesCollection.Add
    oSer.SetData c.chDimCategories, c.chDataLiteral, aNames
    oSer.SetData c.chDimValues, c.chDataLiteral, aTotals
    
End Function
</script>
  4. On the View menu, click Design to return to Design view.
  5. On the View menu, click Code to view the code that is behind the Web form (Webform1.aspx.vb).

  6. Add the following code to the top of Webform1.aspx.vb:

    Imports System.Data.SqlClient
Imports System.Xml
Imports System.Xml.Xsl

  7. Add the following code to the Page_Load function in Webform1.aspx.vb.

    Note You must change User ID <username> and password = to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

    Dim sConn As String = "User ID=<username>;Password=<strong password>;Initial Catalog=Northwind;Data Source=YourSQLServer;"
Dim sSQL As String = _
    "SELECT LastName, Sum([UnitPrice]*[Quantity]*(1-[Discount])) AS OrderTotal " & _
    "FROM Employees INNER JOIN (Orders INNER JOIN [Order Details] ON " & _
    "Orders.OrderID = [Order Details].OrderID) ON Employees.EmployeeID = " & _
    "Orders.EmployeeID GROUP BY LastName"

'Connect to the data source.
Dim nwindConn As SqlConnection = New SqlConnection(sConn)
nwindConn.Open()

'Build a DataSet for Employee Sales.
Dim ds As DataSet
ds = New DataSet("MyDataset")
Dim da As SqlDataAdapter = New SqlDataAdapter(sSQL, nwindConn)
da.Fill(ds)

'Add the DataSet as XML to a data island in the PlaceHolder control.
Dim sw As System.IO.StringWriter = New System.IO.StringWriter()
ds.WriteXml(sw)
Dim lc As LiteralControl = New LiteralControl( _
    "<XML ID=""dsXML"">" & sw.ToString & "</XML>")
PlaceHolder1.Controls.Add(lc)

    NOTE: In the code, change the connection string in sConn to a valid connection string for a computer that is running SQL Server and that contains the Northwind sample database.

  8. Follow these steps to test the Web application:
    1. Press F5 to build and run the application. The Web application loads in Internet Explorer.
    2. On the View menu in Internet Explorer, click View Source. Note that an XML data island with the ID "dsXML" exists in the HTML source; the data island was generated by the code in the Page_Load event of the Web form.
    3. On the Web form, click Chart Data to build the chart from the data in the XML data island.

back to the top

Considerations

You can create and use ADO Recordset objects in the .NET Framework through the COM Interop layer. Whether you use ADO Recordset objects or ADO.NET DataSets for your solution is a design choice. If you have existing code that creates DataSets and you want to add charting functionality, you can use one of the solutions that are discussed in this article. However, if you access data solely to create charts, Microsoft recommends that you use ADO through COM Interop so that you can bind directly to the data without conversion.

back to the top

REFERENCES

For additional information about using ADO with Visual Basic .NET, click the following article number to view the article in the Microsoft Knowledge Base:

316337 How to convert an ADO.NET DataSet to ADO Recordset in Visual Basic .NET


For additional information, visit the following Microsoft Developer Network (MSDN) Web site:

Accessing an ADO Recordset or Record from ADO.NET
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconaccessingadofromadonet.asp


For additional information about the Office XP Chart component, click the following article numbers to view the articles in the Microsoft Knowledge Base:

288907 Bind the Office XP Chart Component to a data source


286212 How to use an XML DataSource with the Office Chart Component


315695 How to use the Office Web Components with XML data generated by an XML Web service created using ASP.NET


286277 How to use the Office XP Chart Component to create static charts with ASP



For additional information, visit the "Office Web Components" section of the following article on the Microsoft Developer Network (MSDN) Web site:

Microsoft Office Development with Visual Studio
http://msdn2.microsoft.com/en-us/library/aa188489(office.10).aspx


back to the top

Keywords: kbhowtomaster kbofficewebchart KB303016



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/303016&oldid=379474’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki