Formatting Charts

[rcollins]

I have a union query that I built so that I can include location average along with corporate average. I then sort the info on the graph so it is lowest to highes, so now the corporate average sits amongst the middle of the group. This givs a visual to where the locations compare to corporate. The problem is, I want the corporate one to be a different colo so it stands out. I know how to change it manually, but as the corporate average can move from quarter to quarter, I would like to use something like criteria where I can say if corp make datapoint dark blue. Is this even possible? If so how would I do this?

[ADezii]

@rcollins

1. First, set a Reference to the Microsoft Graph XX.X Object Library.
2. Set the Color Property of an Interior Object, for a specific Member of the SeriesCollection Collection (Series Object):
   

   If YaDaYaDaYaDa Then
     'By Series Name or Index
     Me![chtTest].SeriesCollection("2002").Interior.Color = vbBlue
                                'OR
     Me![chtTest].SeriesCollection(2).Interior.Color = vbBlue
   Else
     Me![chtTest].SeriesCollection("2002").Interior.Color = vbYellow
                                'OR
     Me![chtTest].SeriesCollection(2).Interior.Color = vbYellow
   End If

[ADezii]

@rcollins
Sorry rcollins, but you can elimate Step #1 in the prior Post since this Reference is not needed.

[rcollins]

ok, so my graph is Graph2, my x axis is CountyOfSurvey and Y axis is Expr1. "Corp" is always one of the values in CountyOfSurvey, this is the one I want to be dark blue. I am assuming I put this in the VB editor under graph2? Can you help to make sure I get the code right?

[ADezii]

@rcollins
Try:

Me![Graph2].SeriesCollection("Corp").Interior.Color = vbBlue

[rcollins]

this isnt changing anything. What am I doing wrong?

Private Sub Graph2_Updated(Code As Integer)
Me![Graph2].SeriesCollection("Corp").Interior.Color = vbYellow
 
End Sub

[ADezii]

@rcollins
Are you sure that 'Corp' is the Series that is plotted?

[rcollins]

Corp is one of the data in the series, the series is CountyofSurvey

[ADezii]

@rcollins
You would then have to check the value of the Data Point, then modify the Color of the Data Series, so the code to change the Color, depending on the Point Value would be:

Me![Graph2].SeriesCollection("CountyofSurvey").Interior.Color = vbYellow 

P.S. - If you are still stuck, and you don't mind, I'll send you my E-Mail Address in a Private Message. You could then Send the Database to me as an Attachment, and as soon as I get a chance, I'll take a look at it. It is up to you.

[ADezii]

This was a little more complicated then initially envisioned, but rather than going into a detailed, boring, discussion about this possible solution, I'll simply post the code. Should you have any further questions, feel free to ask. This code will replace the existing code in the Charts Command Button (Command17) on the OPCriteria Form.

'Seed the Random Number Generator
Randomize
 
'At a bare minimum, there must be Quarter specified in the 1st Drop Down
'since this Value is used as a Criteria in multiple, relevant, Queries
If IsNull(Me![Combo28]) Then
  MsgBox "You must enter at least 1 Quarter to analyze", vbExclamation, _
         "No Quarter Defined"
  Me![Combo28].SetFocus
  Me![Combo28].Dropdown
    Exit Sub
End If
 
'Open the Sub-Report as a Stand-Alone Report, apart from the context
'of the Main Form. This report is opened in Design/Hidden Mode
DoCmd.OpenReport "OverallBySite", acViewDesign, , , acHidden
 
'Modify the Interior Color of the Point corresponding to Corp Value. This Interior
'Color will be assigned 1 of 16 possible Random Values (0 - 14) which will be
'passed to the QBColor() Function.
Reports!OverallBySite.Report![Graph2].SeriesCollection(1).Points(9).Interior.Color = QBColor(Int(Rnd * 15) + 1)
 
'Close the Sub-Report containing the Chart, and Save the changes made
DoCmd.Close acReport, "OverallBySite", acSaveYes
 
'Open the Main Report containing the Sub-Report
DoCmd.OpenReport "OPGraphs", acPreview

P.S. - There is an occasional Error relating to Loading the ActiveX Control which I will leave to you to figure out.

[rcollins]

So, I got that done, and it always randoms the color, but here is the thing. I think I have you backwards. I need the color set to be random on point nine. "Corp" Does not fall on point 9 every time. It is the point I need to compare, if it is "Corp" no matter where it sits is what I need to vary. Sorry for the confusion.

[ADezii]

@rcollins
I guess you would have to iterate through all the Points in the Single Series, and see if its DataLabel is 'Corp'. If so then modify the Color. The pseudo code, NOT TESTED and intentionally kept lengthy for display purposes, would be analogous to:

Dim intCounter As Integer
 
For intCounter = 1 To Reports!OverallBySite.Report![Graph2].SeriesCollection(1).Points.Count
  If Reports!OverallBySite.Report![Graph2].SeriesCollection(1).Points(intCounter).DataLabel.Text = "Corp" Then
    Reports!OverallBySite.Report![Graph2].SeriesCollection(1).Points(intCounter).Interior.Color = vbYellow
  End If
Next

[ADezii]

You could also try to reference the Point by Name instead of Index, as in:

Reports!OverallBySite.Report![Graph2].SeriesCollection(1).Points("Corp").Interior.Color = vbYellow

[rcollins]

So would this go in palce of the code in the previous post? It gives me an error that OverallBySite is misspelled or refers to a report that isn't open or doent exist, If I have the report open, it errors Object doesnt support this property or method. If I have the report in design view, it opens but the color doesnt change. Any advice?

[ADezii]

@rcollins
I actually have the Database on my other PC at home and will not be able to access it until tonight or tomorrow. At that time, I'll see if I can achieve the requested results.

[rcollins]

That will be fine. I am focusing on another report in the database so I have something to keep me busy until then

[ADezii]

@rcollins
I've been wrestling with this problem for awhile now, and the only thing that I can come up with is this rather clumsy, and somewhat complex, solution which has a fairly high degree of accuracy with the data tested. I iterate through the Points in the specified Series, and compare each point's DataLabel against a DLookup() on the RowSource for the specified Point. If there is a match, the Interior Color of the Point is set to Yellow, otherwise the Interior Color is set to a Default (Blue in this case). Hopefully, you may be able to play with it and improve on the accuracy as well as the logic itself, since I simply do not have the time. Replace the existing code in the Click() Event of the Command Button with this code. Should you wish to modify the Point Referenced, change the value of the Constant conPOINT_TO_COMPARE to any other Point. Good Luck, and let me know how you make out.

Dim intPtCounter As Integer
Const conPOINT_TO_COMAPARE As String = "Corp"
 
'At a bare minimum, there must be Quarter specified in the 1st Drop Down
'since this Value is used as a Criteria in multiple, relevant, Queries
If IsNull(Me![Combo28]) Then
  MsgBox "You must enter at least 1 Quarter to analyze", vbExclamation, _
         "No Quarter Defined"
  Me![Combo28].SetFocus
  Me![Combo28].Dropdown
    Exit Sub
End If
 
 
'Open the Sub-Report as a Stand-Alone Report, apart from the context
'of the Main Form. This report is opened in Design/Hidden Mode
DoCmd.OpenReport "OverallBySite", acViewDesign, , , acHidden
 
For intPtCounter = 1 To Reports!OverallBySite.Report![Graph2].SeriesCollection(1).Points.Count
  If Reports!OverallBySite.Report![Graph2].SeriesCollection(1).Points(intPtCounter).DataLabel.Text = _
     FormatNumber(DLookup("[Expr1]", "OverallBySite", "[CountyOfSurvey]='" & conPOINT_TO_COMAPARE & "'"), 2) Then
       Reports!OverallBySite.Report![Graph2].SeriesCollection(1).Points(intPtCounter).Interior.Color = vbYellow
  Else
    Reports!OverallBySite.Report![Graph2].SeriesCollection(1).Points(intPtCounter).Interior.Color = vbBlue
  End If
Next
 
'Close the Sub-Report containing the Chart, and Save the changes made
DoCmd.Close acReport, "OverallBySite", acSaveYes
 
'Open the Main Report containing the Sub-Report
DoCmd.OpenReport "OPGraphs", acPreview

[ADezii]

@ADezii
P.S. - The reason for the other than 100% accuracy is that you are comparing the DataLabel of the Point, which has 2 Decimal places of Precision to the actual Point Value which has a higher Degree of Precision, as in:

Corp with 2009 Quarter2 results with the following comparison:

3.93 <==> 3.93331635684577

[ADezii]

@ADezii
I use FormatNumber to force 2 Decimal Places, but it seems as though the comparison now becomes:

3.93 <==> 3.94

In any event, I'm sure you'll work out this minor point. To confuse you even more so, when you use a single Quarter of 2009 Quarter 2, the Test Results are perfect on all 15 Points. Figure that out, and therein lies your answer!!!

[rcollins]

I really appriciate your help. I have it working to the extent that you did, and set the values to be 2 dec places. I will keep working on this since the only qwuarter that changes color is 2009 quarter 2. I wish I had more data than 3 quarters, but we will get there.

[rcollins]

So when I go into the properties of the graph to set percision, I realized that every time I rerun the report, the settings are not there. The query where the info comes from is a union, and I am not sure how to set precision in sql. Here is the query I am using for this chart

SELECT OPQuestionAvg1.CountyofSurvey, OPQuestionAvg1.SurveyCode, Avg(([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12]+[13]+[14]+[15]+[16])/16) AS Expr1
FROM OPQuestionAvg1
GROUP BY OPQuestionAvg1.CountyofSurvey, OPQuestionAvg1.SurveyCode
UNION SELECT "Corp" AS CountyOfSurvey, OPQuestionAvg1.SurveyCode, Avg(([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12]+[13]+[14]+[15]+[16])/16) AS Expr1
FROM OPQuestionAvg1
GROUP BY OPQuestionAvg1.SurveyCode
ORDER BY Expr1;
 

Thanks

[ADezii]

@rcollins

not sure how to set precision in sql

I think you are referring to the Scale which is the number of Digits to the right of the Decimal Point. This Setting does not exist in Access Databases, but does in *.adps and SQL Server Tables. I didn't give up yet, there must be a way to directly access the actual value of a Data Point for a given Series, but it still eludes me.