Web Charthtm

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

<Title>Spreadsheet and Chart Component</Title> <body>

<object classid="clsid:0002E551-0000-0000-C000-000000000046" id="Spread1" >

<h5>Data Source: Northwind Database </h5><p>

<object classid="clsid:0002E500-0000-0000-C000-000000000046" id="chrtSpace"

width=500 height=230></object>

<SCRIPT LANGUAGE="VBScript"> Sub Window_OnLoad() ' Declare variables

Dim strConnection, rst, strSQL, count, r, c, myData, cons, objChart ' the connection string strConnection="Provider=Microsoft.Jet.OleDB.4.0; data source=" & _ "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

' Create a Recordset

Set rst = CreateObject("ADODB.Recordset")

' define the SQL query to supply the data for charting strSql = "TRANSFORM Sum([Order Details].[Quantity]*(" _ & "[Order Details].[UnitPrice]-" _

& "([Order Details].[Discount]/100)*" _ & "[Order Details].[UnitPrice])) AS Sales " _ & "SELECT [Categories].[CategoryName] " _ & "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 (((DatePart('yyyy',[OrderDate]))=1997)) " _ & "GROUP BY [Categories].[CategoryName] " _ & "ORDER BY [Categories].[CategoryName] " _ & "PIVOT 'Qtr ' & DatePart('q',[OrderDate]) "

' Open Recordset (and execute the SQL statement above) ' use the connection string with a client-side cursor (3) ' and static dataset (3) rst.Open strSql, strConnection, 3, 3

' Enter field names as column headings in Spreadsheet control For count = 0 to rst.fields.count - 1 r = r + 1

With Spread1.ActiveSheet.Cells(1, r) .Value = rst.Fields(count).Name .Font.Bold = True .Font.Color = "blue" End with


' retrieve rows from the Recordset ' and fill an array with the resulting data myData = rst.GetRows()

' determine how many rows were actually returned returnedRows = UBound(mydata, 2) + 1

' read the array and copy data to the Spreadsheet control For r = 1 to returnedRows

For c = 1 to rst.Fields.Count

Spread1.ActiveSheet.Cells(r+1, c).value = myData(c-1, r-1) Next Next

' cleanup rst.close set rst = Nothing

'format the Spreadsheet control including the data With Spreadl

.DisplayGridlines = False .DisplayToolbar = False

.ViewableRange = Spreadl.ActiveSheet.UsedRange.Address .AutoFit = True With .ActiveSheet.UsedRange .Font.Size = 8

.Columns.AutoFit End With End With

Create a column chart with four series showing the category sales by quarter in 1997

' Clear the contents of the chrtspace object ' and format the title With chrtSpace .Clear

.HasChartSpaceTitle = True With .ChartSpaceTitle

.Caption = "Quarterly Sales (1997)" .Font.Size = 9 .Font.Bold = True End With End With

' Bind the Spreadsheet component to the chart chrtSpace.DataSource = Spreadl

' Get constants for the Chart component set cons = chrtSpace.Constants

' Add a new chart to chrtSpace set objChart = chrtSpace.Charts.Add

' Specify that the chart is a column chart objChart.Type = cons.chChartTypeBarClustered

' Add four series to the chart objChart.SeriesCollection.Add objChart.SeriesCollection.Add objChart.SeriesCollection.Add objChart.SeriesCollection.Add

' Series one contains Qtr 1 sales With objChart.SeriesCollection(O)

.SetData cons.chDimSeriesNames, chDataBound,"B1" .SetData cons.chDimCategories, chDataBound,"A2:A9" .SetData cons.chDimValues, chDataBound,"B2:B9" End With

' Series two contains Qtr 2 sales With objChart.SeriesCollection(l)

.SetData cons.chDimSeriesNames, chDataBound,"C1" .SetData cons.chDimCategories, chDataBound,"A2:A9" .SetData cons.chDimValues, chDataBound,"C2:C9" End With

' Series three contains Qtr 3 sales With objChart.SeriesCollection(2)

.SetData cons.chDimSeriesNames, chDataBound,"D1" .SetData cons.chDimCategories, chDataBound,"A2:A9" .SetData cons.chDimValues, chDataBound,"D2:D9" End With

' Series four contains Qtr 4 sales With objChart.SeriesCollection(3)

.SetData cons.chDimSeriesNames, chDataBound,"E1" .SetData cons.chDimCategories, chDataBound,"A2:A9" .SetData cons.chDimValues, chDataBound,"E2:E9" End With

'Specify position for the chart legend With objChart

.HasLegend = True

.Legend.Position = cons.chLegendPositionBottom End With

End Sub

Programmatically, you can add as many as 16 charts to the chart workspace. Multiple charts can be displayed in rows or columns. You can control the chart layout with the ChartLayout property of the ChartSpace object. The following statement will position the charts side by side:

chrtSpace.ChartLayout = cons.chChartLayoutHorizontal The following layout values are available:

0 chChartLayoutAutomatic

1 chChartLayoutHorizontal

2 chChartLayoutVertical

You can control the number of charts on a row or column by setting the ChartWrapCount property to the required number of charts. For example, to place four charts in a row or column, use the following statement:

chrtSpace.ChartWrapCount = 4

Figure D-5 presents a web page with a Chart component containing a bar chart and a doughnut chart placed in two rows. The data for both charts is supplied dynamically from the Northwind database when the web page is opened. The data is bound to arrays obtained from the ADO recordset and passed to the SetData method via the chDataLiteral constant, like this:

' Bind the charts to arrays With chrtSpace.Charts(0)


.SeriesCollection(0).SetData cons.chDimSeriesNames, _ cons.chDataLiteral, "Unit Price"

.SeriesCollection(0).SetData cons.chDimCategories, cons.chDataLiteral, categ .SeriesCollection(0).SetData cons.chDimValues, _

cons.chDataLiteral, values1 .HasLegend = True

.Type = cons.chChartTypeBarClustered .Axes(cons.chAxisPositionBottom).NumberFormat = "$#

End With

The categ and values1 in the code fragment above are the names of Variants that hold arrays with the list of categories and values to be charted. See the code below on how these arrays are filled from the ADO recordset.

Figure D-5:

A chart workspace can host multiple charts arranged horizontally or vertically.

Figure D-5:

A chart workspace can host multiple charts arranged horizontally or vertically.

To prepare the web page shown in Figure D-5, open Notepad and enter the HTML and VBScript code, as shown below. Ensure that the path points to the Northwind database on your server. Save the file as 2WebCharts.htm. You can find this file on the companion CD-ROM.

Was this article helpful?

0 0

Post a comment