geoffgillespie
asked on
mschart data and legendtext
Hi,
I'm trying to make a 2dline chart for a report that will track archived stats based on dates and percentages. A sample of the data looks like this:
ID #ofEvents #ofClicks DateArchived
1 100 10 01/01/00
1 200 20 01/02/00
2 10 1 01/01/00
2 50 2 01/02/00
I'm pulling the data from a table using ADO, and have it populating an array. This charts just fine, with a horizontal line for however many distinct id's i have, with the percentage (#ofclicks/#of events) being shown up the side of the chart, and the dates being shown as columns along the bottom.
My problem is, getting the 'names' for each row to show in the legend. I'm using:
MSChart.Plot.SeriesCollect ion(j).Leg endText = 'rowName' in a loop to set the names. This works great for 4 or less rows, but gives me an "Index given out of bounds" error when I reach the j = 5 loop. I'm not sure how MSChart determines how many series there are, so I can't figure where or how to set this.
I've tried using the mschart.row = 1, mschart.rowlabel = "name" method, but while the labels are properly set to the names, they don't show in the legend.
If anyone knows how to manipulate the series collection, that'd be the easiest way, but if I'm way off track, please let me know an easier way =)
Thanks a million in advance,
Geoff
Here's a shortened version of the function I'm using:
Public Function GetChart(SQL As String, startDate As Date, endDate As Date)
Dim arrData()
Dim itemCount As Integer
Dim daysCount As Integer
Dim SQLChart As String
Dim i, j As Integer
Dim rsChart As ADODB.Recordset
Dim rsItemId As ADODB.Recordset
Set rsChart = New ADODB.Recordset
Set rsItemId = New ADODB.Recordset
Call OpenConn 'opens adodb.connection
rsItemId.Open SQL, myConnection, adOpenStatic, adLockOptimistic, adCmdText
daysCount = DateDiff("d", startDate, endDate)
rsItemId.MoveLast
itemCount = rsItemId.RecordCount
j = 1
ReDim arrData(0 To (daysCount - 1), 0 To itemCount)
rsItemId.MoveFirst
Do Until rsItemId.EOF
CampItemId = rsItemId(0)
SQLChart = "SELECT campaign_item_id,events_se rved, clicks_served, date_archived FROM as_performance_archive WHERE " & _
"date_archived BETWEEN " & Chr(39) & CDate(startDate) & Chr(39) & " AND " & Chr(39) & CDate(endDate) & Chr(39) & _
" AND campaign_item_id = " & CampItemId & " ORDER BY campaign_item_id, date_archived"
rsChart.Open SQLChart, myConnection, adOpenStatic, adLockOptimistic, adCmdText
rsChart.MoveFirst
For i = 0 To daysCount - 1
arrData(i, 0) = rsChart(3)
arrData(i, j) = (rsChart(2) / rsChart(1)) * 100
rsChart.MoveNext
Next i
frmChart.MSChart.Plot.Seri esCollecti on(j).Lege ndText = GetAdName(CampItemId)
'GetAdName is another function to pull the name
j = j + 1
rsItemId.MoveNext
rsChart.Close
Loop
rsItemId.Close
With frmChart.MSChart
.ChartData = arrData
.ShowLegend = True
.TitleText = "Click Rates from " & startDate & " to " & endDate
.Legend.Location.LocationT ype = VtChLocationTypeBottom
End With
Call CloseConn
Set rsChart = Nothing
Set rsItemId = Nothing
End Function
I'm trying to make a 2dline chart for a report that will track archived stats based on dates and percentages. A sample of the data looks like this:
ID #ofEvents #ofClicks DateArchived
1 100 10 01/01/00
1 200 20 01/02/00
2 10 1 01/01/00
2 50 2 01/02/00
I'm pulling the data from a table using ADO, and have it populating an array. This charts just fine, with a horizontal line for however many distinct id's i have, with the percentage (#ofclicks/#of events) being shown up the side of the chart, and the dates being shown as columns along the bottom.
My problem is, getting the 'names' for each row to show in the legend. I'm using:
MSChart.Plot.SeriesCollect
I've tried using the mschart.row = 1, mschart.rowlabel = "name" method, but while the labels are properly set to the names, they don't show in the legend.
If anyone knows how to manipulate the series collection, that'd be the easiest way, but if I'm way off track, please let me know an easier way =)
Thanks a million in advance,
Geoff
Here's a shortened version of the function I'm using:
Public Function GetChart(SQL As String, startDate As Date, endDate As Date)
Dim arrData()
Dim itemCount As Integer
Dim daysCount As Integer
Dim SQLChart As String
Dim i, j As Integer
Dim rsChart As ADODB.Recordset
Dim rsItemId As ADODB.Recordset
Set rsChart = New ADODB.Recordset
Set rsItemId = New ADODB.Recordset
Call OpenConn 'opens adodb.connection
rsItemId.Open SQL, myConnection, adOpenStatic, adLockOptimistic, adCmdText
daysCount = DateDiff("d", startDate, endDate)
rsItemId.MoveLast
itemCount = rsItemId.RecordCount
j = 1
ReDim arrData(0 To (daysCount - 1), 0 To itemCount)
rsItemId.MoveFirst
Do Until rsItemId.EOF
CampItemId = rsItemId(0)
SQLChart = "SELECT campaign_item_id,events_se
"date_archived BETWEEN " & Chr(39) & CDate(startDate) & Chr(39) & " AND " & Chr(39) & CDate(endDate) & Chr(39) & _
" AND campaign_item_id = " & CampItemId & " ORDER BY campaign_item_id, date_archived"
rsChart.Open SQLChart, myConnection, adOpenStatic, adLockOptimistic, adCmdText
rsChart.MoveFirst
For i = 0 To daysCount - 1
arrData(i, 0) = rsChart(3)
arrData(i, j) = (rsChart(2) / rsChart(1)) * 100
rsChart.MoveNext
Next i
frmChart.MSChart.Plot.Seri
'GetAdName is another function to pull the name
j = j + 1
rsItemId.MoveNext
rsChart.Close
Loop
rsItemId.Close
With frmChart.MSChart
.ChartData = arrData
.ShowLegend = True
.TitleText = "Click Rates from " & startDate & " to " & endDate
.Legend.Location.LocationT
End With
Call CloseConn
Set rsChart = Nothing
Set rsItemId = Nothing
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER