Charting Question

[Guest]

I have a graph that i'm trying to display showing the history of a few
fields.

There are 2 tables: Person and PersonHistory... For each person there is
1..many records in person history...

So the chart would show the trend of these fields in PersonHistory over time
(week_ID is one of the records in personhistory...)

I am putting the chart on form that will enable the person to select a
person from a dropdown containing all of the rows in the person table... Also
on this form is a check box for each statistic that would let the user
determine what to show on the graph (it defaults to having all show up)...

1) How would i change the lines on the chart that were visible when someone
changes the checkbox?

2) The legend is showing "sum of <stat 1>" "sum of <stat2>" etc instead of
having the label just be "stat1", "stat2" etc... I put the fields in there by
dragging and dropping from the field list into the main box... Is there a way
to edit the legend values (i thoguht there was in excel, but don't see how to
get there).

Thanks so much, this is my first foray into the land of charts.

-Ben

 

[Duane Hookom]

Can you share the structure of the statistics table? I'm not sure if
statistics are multiple columns or or a single field an multiple records.

 

[Guest]

Here would be the values in the result set as an example

Week Stat A Stat B Stat C
1 5 5 4
2 5 6 4
3 6 6 5

and so on...

All of that is in the person history table. The chart is only showing one
person at a time, so ignore the existance of the person table...

So the two questions are that
1) i have checkboxes for a user to be able to toggle on and off stat A/B/C
in the display and i don't know how to change what's being charted (there's
actually 12 statistics, so it gets busy)

2) why is the legend showing "Sum of Stat A", "Sum of Stat B", etc instead
of just "Stat A", "Stat B", etc. The data set includes only one row per week
for the person being displayed at that time......

Thanks,
-B

 

[Duane Hookom]

I would create a normalized union query of the statistics that would place
each stat value for each employee in a separate record. Then use a
multi-select list box to select which statistics to include in the graph.
Use a crosstab query as the Row Source of the chart. This should provide
appropriate labels for the lines in the chart.

 

[Guest]

There's no method that you can call to change the fields available in the
data section of the chart?

 

[Duane Hookom]

You can always change the SQL property of a saved query and use the saved
query as the Row Source of the chart. I think the crosstab based on a
normalized query where users select from a list box would also dynamically
change the columns/fields in the report.

 

[Guest]

And then i'd have to in code set what was in the data and x-axis and y-axis
after the row source was changed?

The question here is specific to charts, how to change what's displayed in
the chart, not the underlying data.

 

[Duane Hookom]

I expect what you want to display in your chart is from data. This data can
generally be described with a SQL statement. This SQL statement can be set
as the SQL property of a saved query. The saved query can be the Row Source
property of the Chart Control. Therefore, the data you want to display is
possible.

 

[Guest]

I understand that, but if i change the data i'm assuming that i'll have to
then redefine what is shown on the chart.

And if i can redefine what is shown in the data area, legends, etc, i could
just do this without changing the source data.

I just can't find any method list associated with chart objects.

-B

 

[Guest]

I understand that, but if i change the data i'm assuming that i'll have to
then redefine what is shown on the chart.

And if i can redefine what is shown in the data area, legends, etc, i could
just do this without changing the source data.

I just can't find any method list associated with chart objects.

-B

"Duane Hookom"

 

[Duane Hookom]

You can either bind your chart to a Row Source of a table/query or you can
use code to push values into the cells of the datasheet of the graph
control.

I'm not sure what you mean by "change the data". You haven't provided an
example of what you mean by this and how changing the SQL of a saved query
would not automatically change your chart data. If you change data, the
bound chart should show the changed data.

Directly setting values in the Graph.datasheet.Range()... can be very
powerful and flexible.

 

[Guest]

I'm sorry that i'm not communicating effectively.

I don't want to change the data.

I want to change the displayed lines on the chart. This is done via the
wizard by dragging fields in the result set into the data area, hence my use
of the word data. I want to REMOVE fields from being displayed in the chart,
not actually change the result set. That way, i don't have to change the
recordsource or have multiple round trips if the user toggles multiple stats
being displayed.

Thanks,
-Ben

 

[Duane Hookom]

Reports don't allow users to change anything on an open report. Once it's
displayed, it is static.

You still haven't provided us with even a small example of the datasheet
view of a graph data before and after a user has made some kind of change to
modify the chart display.

If the user simply wants to display/graph different columns or different
rows then this is most easily done using the method that I have suggested.