pauldownham
asked on
Chart / Graph scales - How to fix sensible values on the X axis
My Access runtime application includes plotting a graph from a table where the X axis is a date/time field with a variable time interval. The report can cover anything from a few hours up to several months, and from 2 to 2000+ records, so the AutoScaling feature is essential - and works well.
However, the axis label values that Access selects from the table nearly always look stupid, because say the range is 15/01/2004 15:54 to 18/01/2004 12:08 (which is 4096 minutes), Access will print 18 labels starting at the first record and then every 228 minutes (nearest 18th of 4096), eg 15/01/2004 15:54, 15/01/2004 19:42, 15/01/2004 23:30 etc. which is hardly easy to follow! A human would have chosen 4 hour gaps, and then plotted values at 16:00, 20:00, 24:00 etc.
(By the way, the number of labels you get seems to depend only on the size of font selected - the bigger the font, the less labels)
So, what I need is a way to stipulate the first value label (15/01/2004 16:00 in this example), and the gap (240 mins), whilst still graphing the full set of results as now.
Thanks for any help.
However, the axis label values that Access selects from the table nearly always look stupid, because say the range is 15/01/2004 15:54 to 18/01/2004 12:08 (which is 4096 minutes), Access will print 18 labels starting at the first record and then every 228 minutes (nearest 18th of 4096), eg 15/01/2004 15:54, 15/01/2004 19:42, 15/01/2004 23:30 etc. which is hardly easy to follow! A human would have chosen 4 hour gaps, and then plotted values at 16:00, 20:00, 24:00 etc.
(By the way, the number of labels you get seems to depend only on the size of font selected - the bigger the font, the less labels)
So, what I need is a way to stipulate the first value label (15/01/2004 16:00 in this example), and the gap (240 mins), whilst still graphing the full set of results as now.
Thanks for any help.
I'm assuming you use MS-Chart to create the chart.
Make sure that MS-Chart knows that you are working with a time scale (under "Chart / Options")
Try changing some of the scaling options to see if that gives you the result you want.
Hint: copy some actual data from the source query to MS-Chart, so that it makes sense... :)
If you need to change the various options by program, it is useful to know that the data model of MS-Chart is largely (or completely?) compatible with Excel. You can thus use Excel's macro recorder to learn how to program for Chart as well.
For example, I just changed dynamically the minimum scale of an embedded chart with:
Me.OLEUnbound0.Object.Axes
Try also .BaseUnit and .MajorUnit
Again, it is best to rebuild your chart in Excel, and then record all the changes you need for the scale. Then it will be easy to program that in Access.
Then you will discover that the logic behind setting a scale factor, a minimal value and a unit is far from trivial...
Lots of fun!
Make sure that MS-Chart knows that you are working with a time scale (under "Chart / Options")
Try changing some of the scaling options to see if that gives you the result you want.
Hint: copy some actual data from the source query to MS-Chart, so that it makes sense... :)
If you need to change the various options by program, it is useful to know that the data model of MS-Chart is largely (or completely?) compatible with Excel. You can thus use Excel's macro recorder to learn how to program for Chart as well.
For example, I just changed dynamically the minimum scale of an embedded chart with:
Me.OLEUnbound0.Object.Axes
Try also .BaseUnit and .MajorUnit
Again, it is best to rebuild your chart in Excel, and then record all the changes you need for the scale. Then it will be easy to program that in Access.
Then you will discover that the logic behind setting a scale factor, a minimal value and a unit is far from trivial...
Lots of fun!
ASKER
IDAnderson,
Thanks for your reply ... but I don't think I made my question clear enough!
The chart is printing fine, and covers all the records as intended, its just that the labels on the horizontal axis (time line), whilst perfectly correct, look silly because Access has chosen the values automatically and I would prefer to specify them at more obvious places. For example, Access takes the first record's date and time as the first label (which is fair enough!), but a human wouldn't normally choose "15/01/2004 15:54" as a good label, nor that the next one should be 228 minutes later. For clarity, I would much prefer "15/01/2004 16:00" as the first label, and the others at 4hr intervals.
I can't use fixed scaling (because the results to be plotted are so variable), so I was looking for any tips on how to somehow force sensible values as the labels. It may be that I don't let MsGraph work them out at all, but somehow print values derived from calculations on the side?
Thanks for your reply ... but I don't think I made my question clear enough!
The chart is printing fine, and covers all the records as intended, its just that the labels on the horizontal axis (time line), whilst perfectly correct, look silly because Access has chosen the values automatically and I would prefer to specify them at more obvious places. For example, Access takes the first record's date and time as the first label (which is fair enough!), but a human wouldn't normally choose "15/01/2004 15:54" as a good label, nor that the next one should be 228 minutes later. For clarity, I would much prefer "15/01/2004 16:00" as the first label, and the others at 4hr intervals.
I can't use fixed scaling (because the results to be plotted are so variable), so I was looking for any tips on how to somehow force sensible values as the labels. It may be that I don't let MsGraph work them out at all, but somehow print values derived from calculations on the side?
ASKER
Harfang,
Thanks for your suggestion.
My application is a runtime Access program intended for later distribution, so I can't use Excel as an alternative to the inbuilt MSGraph, although I appreciate you are mostly suggesting this as a test arena.
I will look at your code, but presumably this is just the same as fiddling with the options within the setup screen? As per my comment above, I suspect the solution may have to be found as a way to replace the standard values?
Cheers
Thanks for your suggestion.
My application is a runtime Access program intended for later distribution, so I can't use Excel as an alternative to the inbuilt MSGraph, although I appreciate you are mostly suggesting this as a test arena.
I will look at your code, but presumably this is just the same as fiddling with the options within the setup screen? As per my comment above, I suspect the solution may have to be found as a way to replace the standard values?
Cheers
Pauldownham,
Sorry for the misunderstanding.
If you are wanting to round the data to, say, nearest 4 hours you could add calculated fields to your base query so that they calculate the data between defined intervals (if the time is > 20:00 and < 24:00 sum the total number of labels printed).
Please advise if I am on the right track. If you advise that I am on the right track I will begin to tinker with the code to determine the values in the calculated field.
Needless to say, the calculated fields would provide the base data for your chart.
regards,
Ian..
Sorry for the misunderstanding.
If you are wanting to round the data to, say, nearest 4 hours you could add calculated fields to your base query so that they calculate the data between defined intervals (if the time is > 20:00 and < 24:00 sum the total number of labels printed).
Please advise if I am on the right track. If you advise that I am on the right track I will begin to tinker with the code to determine the values in the calculated field.
Needless to say, the calculated fields would provide the base data for your chart.
regards,
Ian..
ASKER
Ian,
I'm not sure if you are on the right track!
The example I've given is just an example. As per the original question, the graph is plotting results which can be anything from 2 records to 2000+, and can span a short time (in which case the labels may only be minutes apart), or up to a year (in which case they may be weeks apart). As a human labelling up the graph, you could always choose sensible labels to suit each occasion, even where some of these may not be data points:-
As another example, say the data points are (and I'll only do the times for clarity):
23:57, 00:03, 00:09; 00:15 .... (and then another 100 at 6 minute intervals).
A human would probably choose labels at half hour intervals (=20 labels), and start the axis labelling at 00:00. The first point plotted would still be 23:57, and all 104 results would still be plotted, but there would be an easy to follow timescale (00:00, 00:30, 01:00 ... ) printed along the axis.
With Access doing the labelling, you would likely get:
23:57, 00:32, 01:07, etc. (start at first, and then 35min gaps (18th of 624)).
which is an impossible timescale to follow!
I'm not sure if you are on the right track!
The example I've given is just an example. As per the original question, the graph is plotting results which can be anything from 2 records to 2000+, and can span a short time (in which case the labels may only be minutes apart), or up to a year (in which case they may be weeks apart). As a human labelling up the graph, you could always choose sensible labels to suit each occasion, even where some of these may not be data points:-
As another example, say the data points are (and I'll only do the times for clarity):
23:57, 00:03, 00:09; 00:15 .... (and then another 100 at 6 minute intervals).
A human would probably choose labels at half hour intervals (=20 labels), and start the axis labelling at 00:00. The first point plotted would still be 23:57, and all 104 results would still be plotted, but there would be an easy to follow timescale (00:00, 00:30, 01:00 ... ) printed along the axis.
With Access doing the labelling, you would likely get:
23:57, 00:32, 01:07, etc. (start at first, and then 35min gaps (18th of 624)).
which is an impossible timescale to follow!
I made a few tests:
With MS-Chart I never got quite that behavior. The value 240 minutes is possible (4 hours, 1/6th of a day), but in that case the labels are also rounded to the next 6th of a day (i.e. 0:00, 4:00, 8:00, etc.). I found no way to have the chart start with a label of 12:54, for instance. How did you do it?
Once in Chart, what are the options in "Chart / Chart Options", tab "Axes"?
What is the base chart type?
When you double-click the time-axis, tab "Scale", are there any options NOT set to Auto?
It should really work better. We might have to resort to VB coding, but I hope not!
Cheers!
With MS-Chart I never got quite that behavior. The value 240 minutes is possible (4 hours, 1/6th of a day), but in that case the labels are also rounded to the next 6th of a day (i.e. 0:00, 4:00, 8:00, etc.). I found no way to have the chart start with a label of 12:54, for instance. How did you do it?
Once in Chart, what are the options in "Chart / Chart Options", tab "Axes"?
What is the base chart type?
When you double-click the time-axis, tab "Scale", are there any options NOT set to Auto?
It should really work better. We might have to resort to VB coding, but I hope not!
Cheers!
Chart can compute "time-scales" only for line charts and XY (scatter) charts. If you have a bar chart, the time is never actually read, it is directly used for the bar labels.
Is your DATA actually always 18 points at regular intervals???
Is your DATA actually always 18 points at regular intervals???
ASKER
Harfang,
You may find the comments to Ian useful.
I'm using MSGraph (is that the same as MSChart?), and have the following X axis options:
Patterns Tab:
Lines = automatic
major tick = outside
minor tick = none
tick mark = next to axis
Scale Tab:
Value Y axis crosses = 1
Number of categories between tick mark labels = 10
number of categories between tick marks = 100
(all other boxes left unticked)
Font Tab:
Arial, Regular, 5.75
Autoscale = ticked
Number Tab:
custom, dd/mm/yyyy hh:mm
Alignment Tab:
0 degrees, context
Offset = 100
You may find the comments to Ian useful.
I'm using MSGraph (is that the same as MSChart?), and have the following X axis options:
Patterns Tab:
Lines = automatic
major tick = outside
minor tick = none
tick mark = next to axis
Scale Tab:
Value Y axis crosses = 1
Number of categories between tick mark labels = 10
number of categories between tick marks = 100
(all other boxes left unticked)
Font Tab:
Arial, Regular, 5.75
Autoscale = ticked
Number Tab:
custom, dd/mm/yyyy hh:mm
Alignment Tab:
0 degrees, context
Offset = 100
Ok, we have an answer...
When you write "Number of categories" as any option of the scale tab, it means that you have categories... :) In other words, MSGraph never computes anything. It will simply take the date/time value you provide and stick it below the value.
If you use a line-chart, you should really check "Chart / Chart Options" and try to select "time-scale". The chart will be more accurate and you will have a true axis scale management.
If you use a bar-chart or the like, it's entirely different and it might become very complex in a very short time... But we're up to it!
Cheers!
When you write "Number of categories" as any option of the scale tab, it means that you have categories... :) In other words, MSGraph never computes anything. It will simply take the date/time value you provide and stick it below the value.
If you use a line-chart, you should really check "Chart / Chart Options" and try to select "time-scale". The chart will be more accurate and you will have a true axis scale management.
If you use a bar-chart or the like, it's entirely different and it might become very complex in a very short time... But we're up to it!
Cheers!
ASKER
Harfang,
Interesting!
My graph is a simple line chart, and I've tried your suggestion of selecting time-scale rather than category ... and hey presto ... sensible labels. BUT, (thats a very big "but"), the graph itself has gone completely stupid!!
It seems the smallest unit of time selectable is Days, so my nicely curving chart with plots every 6 minutes has become a series of straight lines with all the results bunched together into days.
I think if we can change this to minutes we would be there for the example data ... except of course this selection needs to be automatic depending on the time spread of the records in each case.
Thanks
Paul
Interesting!
My graph is a simple line chart, and I've tried your suggestion of selecting time-scale rather than category ... and hey presto ... sensible labels. BUT, (thats a very big "but"), the graph itself has gone completely stupid!!
It seems the smallest unit of time selectable is Days, so my nicely curving chart with plots every 6 minutes has become a series of straight lines with all the results bunched together into days.
I think if we can change this to minutes we would be there for the example data ... except of course this selection needs to be automatic depending on the time spread of the records in each case.
Thanks
Paul
Ok, then go with "XY (scatter)". It will look exactly like Line chart if your data is sorted, but without the "base unit" feature. It will display correctly even seconds.
The downside is that it has no "month" and "year" tick marks, always an integer number of days, so that the time axis over wide time spans will again become less elegant.
Glad I could help.
The downside is that it has no "month" and "year" tick marks, always an integer number of days, so that the time axis over wide time spans will again become less elegant.
Glad I could help.
ASKER
Harfang,
We're getting close!
The best looking graph is "XY scatter data points connected by lines", and the date/time labels are looking good - but still a problem with the X axis!
There are big unplotted regions at the left and right of the plot area because the system has chosen dates to start and finish on that are outside the range of the records. Looking inside the axis properties, all the Auto boxes are ticked, but the values are strange - eg 38001.6 as the minimum (I would have expected a date/time), and on the Chart Options/Axes all the options are greyed out with X set on automatic (rather than time-scale where we left it!).
Thanks for your help
We're getting close!
The best looking graph is "XY scatter data points connected by lines", and the date/time labels are looking good - but still a problem with the X axis!
There are big unplotted regions at the left and right of the plot area because the system has chosen dates to start and finish on that are outside the range of the records. Looking inside the axis properties, all the Auto boxes are ticked, but the values are strange - eg 38001.6 as the minimum (I would have expected a date/time), and on the Chart Options/Axes all the options are greyed out with X set on automatic (rather than time-scale where we left it!).
Thanks for your help
Yes, I can explain this.
The XY scatter needs two numerical values, x and y, for each point. Thus, everything happens just as if the function CLng was used on the data.
From Access, open the Immediate Pane / Debug Window and try the following:
? CLng(Date)
? CLng(# 1 apr 4:12 #)
and of course
? CDate(38001.6)
to understand the reading.
The last major improvement of MS-Graph (an of graphics in Excel) dates back to the version 5. At that time, there was an important demand for financial graphs, so that the automatic treatment of dates for Line Charts has been added. Alas, the scientists never put enough pressure on MS to get some of the most trivial enhancements... This is why we are left with this largely unuseable system.
(end of flames)
Access will choose the previous Major Unit mark for the leftmost date/time and the next Major Unit mark for the rightmost. This allows the labels to correspond to something useable and to fall on logical times (e.g. noon).
You can force narrower margins by forcing a smaller major unit. If you need that, you can use some VB instruction like in my very first comment to adjust only that.
I would suggest dragging the borders of the plot areay so that it fills the maximal available space and leave it at that, but it's your call.
Tell me if you need more help.
The XY scatter needs two numerical values, x and y, for each point. Thus, everything happens just as if the function CLng was used on the data.
From Access, open the Immediate Pane / Debug Window and try the following:
? CLng(Date)
? CLng(# 1 apr 4:12 #)
and of course
? CDate(38001.6)
to understand the reading.
The last major improvement of MS-Graph (an of graphics in Excel) dates back to the version 5. At that time, there was an important demand for financial graphs, so that the automatic treatment of dates for Line Charts has been added. Alas, the scientists never put enough pressure on MS to get some of the most trivial enhancements... This is why we are left with this largely unuseable system.
(end of flames)
Access will choose the previous Major Unit mark for the leftmost date/time and the next Major Unit mark for the rightmost. This allows the labels to correspond to something useable and to fall on logical times (e.g. noon).
You can force narrower margins by forcing a smaller major unit. If you need that, you can use some VB instruction like in my very first comment to adjust only that.
I would suggest dragging the borders of the plot areay so that it fills the maximal available space and leave it at that, but it's your call.
Tell me if you need more help.
ASKER
Harfang,
This is looking good ... just need some ideas on code now I think.
I've experimented with the CLng function, and can produce a perfect graph by unticking the auto boxes in the X Axis panel and typing in my own worked out numerical values. I reckon I can also work out these values for any set of data (and make them part of the data), so what I need next is some wonderful code that writes the calculated numbers into the X Axis boxes at each time of plotting.
As an overview, this graph is part of a runtime package for distribution to people whose printers I won't know about, so I'm already sacrificing vast areas of my page to huge borders so that it will always fit their paper settings - so to loose yet more to blank sections within the plot area is not an attractive option.
Thanks again
This is looking good ... just need some ideas on code now I think.
I've experimented with the CLng function, and can produce a perfect graph by unticking the auto boxes in the X Axis panel and typing in my own worked out numerical values. I reckon I can also work out these values for any set of data (and make them part of the data), so what I need next is some wonderful code that writes the calculated numbers into the X Axis boxes at each time of plotting.
As an overview, this graph is part of a runtime package for distribution to people whose printers I won't know about, so I'm already sacrificing vast areas of my page to huge borders so that it will always fit their paper settings - so to loose yet more to blank sections within the plot area is not an attractive option.
Thanks again
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry, I'm being thick here!
Where do you find out what the chart is called (your example is uofChart), is it the Unbound Object Frame name (from the pop up properties you see when right clicking the chart)?
Also, where does the code go ... I'm only used to entering these as Event Procedures on forms?
Thanks again
PS Your 500 points are nearly ready!!
Where do you find out what the chart is called (your example is uofChart), is it the Unbound Object Frame name (from the pop up properties you see when right clicking the chart)?
Also, where does the code go ... I'm only used to entering these as Event Procedures on forms?
Thanks again
PS Your 500 points are nearly ready!!
The code is a "Private Sub Detail_Print", or the Print event of the Detail section. So it is just another event procedure... If you chart is on another section, use that section's print event.
And yes "uofChart" is the name of the "unbound object frame" containing the MS-Graph object, you got that right :)
Good Luck
And yes "uofChart" is the name of the "unbound object frame" containing the MS-Graph object, you got that right :)
Good Luck
ASKER
Harfang,
This technique seems to do the job ... so I'm now working on some fancy formulea to get nice looking labels and gaps for all the likely cases that might arise in our data.
Its good to be in control!
Thanks for your help, and the points are awarded.
(You only got a GOOD, because I'm otherwise giving out too many EXCELLENTS!)
Cheers
Paul
This technique seems to do the job ... so I'm now working on some fancy formulea to get nice looking labels and gaps for all the likely cases that might arise in our data.
Its good to be in control!
Thanks for your help, and the points are awarded.
(You only got a GOOD, because I'm otherwise giving out too many EXCELLENTS!)
Cheers
Paul
> You only got a GOOD
I consider that rude :)
I am used to getting only EXCELLENTS, this HURTS! :)
Good luck with your charting...
I consider that rude :)
I am used to getting only EXCELLENTS, this HURTS! :)
Good luck with your charting...
ASKER
Harfang,
I wouldn't want you to feel hurt, but I don't think I can change grades after the event!
So, here's a secret plan:
There's just one more thing to resolve on this issue - which is a separate question I'm going to post in a few minutes - and if you get that answered straightaway, not only will you get more points, but also an excellent!
I wouldn't want you to feel hurt, but I don't think I can change grades after the event!
So, here's a secret plan:
There's just one more thing to resolve on this issue - which is a separate question I'm going to post in a few minutes - and if you get that answered straightaway, not only will you get more points, but also an excellent!
To ensure that your chart prints the first record on a particular date, make sure that the date field (bound text box) appears somewhere on your form or report (if you do not want the date visible set the text box properties - Visible No).
To set the x axis, double click on the chart in design view, click on the x axis to select it, right click and select format axis. Go to scale tab and set according to your requirement.
best regards,
Ian..