?
Solved

Color Code Chart Space by Year

Posted on 2009-02-17
10
Medium Priority
?
715 Views
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.
screenShot.JPG
0
Comment
Question by:Student_101
  • 6
  • 4
10 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 23670522
can you post the code that you are using to generate this chart?
0
 

Author Comment

by:Student_101
ID: 23670556
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
            .Refresh
            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
            
        Else
            
        .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

0
 

Author Comment

by:Student_101
ID: 23670579
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
            .Refresh
            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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 23670606
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?

AW
0
 

Author Comment

by:Student_101
ID: 23670659
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 :)

Thanks

0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 23671290
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.

AW
0
 

Author Comment

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

Like in a separate recordset?
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 1000 total points
ID: 23700780
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"??
 
AW
 
0
 

Author Closing Comment

by:Student_101
ID: 31547991
Thanks for your help, I understand the idea of separate recordsets now.
0
 

Author Comment

by:Student_101
ID: 23711060
AW

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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month8 days, 19 hours left to enroll

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question