We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now


Color Code Chart Space by Year

Student_101 asked
Medium Priority
Last Modified: 2012-08-13
Hi Experts,

I want to color code my Chart Space with different colors.

I have a XY Scatter Plot, which displays 10 years of data.

I just want to be able to differentiate each year by a different color of marker.

Please check the attached picture.

I am plotting this graph through the flex grid that I have at at run time right beside this Chart.

There are 3 columns, Col 1 is the date, and Col 2 and 3 are shown on the graph.
Watch Question

can you post the code that you are using to generate this chart?


Hi Arthur,

I have now attached the code.
Dim cmd_graph As New ADODB.Connection
Dim rs_graph As New ADODB.Recordset
With cmd_graph
.ActiveConnection = m_Conn
.CommandText = "SELECT tbl_Operation.Value, tbl_keelingPlot.Value2 FROM tbl_keelingPlot INNER JOIN tbl_Operation ON tbl_keelingPlot.Row = tbl_Operation.Row ORDER BY tbl_Operation.Date;"
.CommandType = adCmdText
End With
  With rs_graph
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmd_graph
  End With
  With Chart_1
        .HasChartSpaceTitle = True
        .ChartSpaceTitle.Caption = cbo_measurementParam.List(0) & " VS. " & cbo_measurementParam.List(1)
        If keeling_Select = True Then
            high = therowsel
            For j = therow To high
                .SetData chDimXValues, grd_result.TextMatrix(j, 2), rs_graph!value
                .SetData chDimYValues, grd_result.TextMatrix(j, 3), rs_graph!value2
            Next j
        .SetData chDimXValues, 2, expr1000  'Categories are first field
        .SetData chDimYValues, 3, rs_graph!value2     'Values are second field
        End If
    End With
  Set grd_graph.DataSource = RS_Results

Open in new window


Forgot to mention, the part where I have,

If Keeling_Select = true

that's where I m trying to get the graph of the highlighted rows of my flex grid only, but it doesn't seem to work.
If keeling_Select = True Then
            high = therowsel ' = grd_result.rowsel
            For j = therow To high ' therow = grd_result.row
                .SetData chDimXValues, grd_result.TextMatrix(j, 2), rs_graph!value
                .SetData chDimYValues, grd_result.TextMatrix(j, 3), rs_graph!value2
            Next j

Open in new window

1) the last line uses RS_Results - which is not defined in your code.

2) I see nothing in you code which would define the data by Year.  Are there separate sets of values for each year?  Where are the defined?



1) Yes, it should be RS_Grpah, I tried it with that 2 but it doesn't work.

2) We can use Year(date) in my SQL query to give us the year's.

And each date is unique, and contains corresponding sets of values which you see in the graph.

So we have column 1 = Date, column 2 = Value 1, Column 3 = Value 2 in my flex grid,

But on the Chart I only am only graphing value 1 and 2.

again, I have never used charts before, so If I need to do something differently please let me know :)


in order to color code each year's data, you must have the datasets separate by year.  As you have it now, you have all of the data (for all the  years), in a single recorset, so you cannot distinguish one year for the next.



Can we group by year and then distinguish it, without showing it on the grid?

Like in a separate recordset?
how are you going to identify the Year for each set of points that you are displaying.  As I see it now, you have one big recordset, which has all of the x-y pairs all together, with no indication of which year any x-y pair belongs to, much less the x-y pairs that belong to any one year.  YOu wuill need to have each years set of values in a separate recordset, and then you will need to have a way to indicate what color should be used for any particular year, and change the color od the markers for each year as that year's data is to be drawn on the chart.
what do you mean by "then distinguish it, without showing it on the grid"??

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


Thanks for your help, I understand the idea of separate recordsets now.



what I meant with that question was,

for instance, if I run a SQL query on a recordset, and use the result of that query, for example, the record count, and the other details in that query, instead of showing the result of the query on the flexgrid.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.