Reports - Graph with group data

I have a table that for the moment has two fields - a value called LEVEL against the date and time of entry called TIME.

TIME is grouped by day and by week.

I have a report that tabulates TIME and LEVEL with a daily group average for LEVEL and a weekly group average for LEVEL and this prints each week's table with the daily average and weekly average in the group footers for day and week.

I now need to insert a graph of LEVEL vs TIME in the weekly group footer. I have created the scatter graph by using a query...

SELECT data.TIME, data.LEVEL, *
FROM data
ORDER BY data.TIME;

and placed it in the weekly group footer.

The weekly table, average and graph displays as I want it, except that the graph displays all data rather than the times for the week for the group data on the page.

How do I restrict the graph to show only the data in the current group based on week?

I can think of two ways this could be done. I don't have anything similar to play with so I'm not sure the first method will work, but second should be doable.

1. In your query for the graph add a where clause that restricts the query to the dates for your group. Something like:
SELECT data.TIME, data.LEVEL, *
FROM data
WHERE DatePart("ww",data.Time) = reports!thisReport!week
ORDER BY data.TIME;

2. In the Group Header add code to the OnFormat event to modify the query for the graph. Something like this:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

Me.grpGraph.rowsource = "SELECT data.Time, data.Level, * FROM data WHERE DatePart("ww", data.Time) = me.grpWeek ORDER BY data.Time

End Sub

I generally don't work with graphs so I may not have right property in the 2nd method so you'll have to change the .rowsource part to whatever the correct property is.

You'll have to taylor either of these to your actual field names etc. but this should give you the idea.

Thanks for the tips, however I have had no success.

The query for the page data is...

SELECT data.TIME, data.LEVEL
FROM data
WHERE (((DateValue([data]![TIME]))>=DateValue([From_Date]) And (DateValue([data]![TIME]))<=DateValue([To_Date])))
ORDER BY data.TIME;

And it produces the data between the "From_date" and "To_date" that I supply. If this is used for the row source of the graph, it also produces the correct data for the graph, but with the disadvantage that it pops up the request for the dates every time the graph is encountered onthe screen or on a printout.

Every time I try to add a filter to the "on format" for the TIME footer where the graph is located, it either generates a single value, or it dispalys the last successful data set that was generated by the above query. I cannot seem to alter the row source for the graph so that it uses the same data that generated the report.

This line
FROM data WHERE DatePart("ww", data.Time) = me.grpWeek
has me confused.

OK well based on this information I would modify your query like so:

SELECT data.TIME, data.LEVEL, [From_Date], [To_Date]
FROM data
WHERE (((DateValue([data]![TIME]))>=DateValue([From_Date]) And (DateValue([data]![TIME]))<=DateValue([To_Date])))
ORDER BY data.TIME;

This allows the From and To dates to be used in the report like any other field which should fix your problem as I understand it.

The line:
FROM data WHERE DatePart("ww", data.Time) = me.grpWeek

Would only make sense if you were grouping the output data by week. In the original post you didn't specifiy your query or how the Start and End dates were being retrieved so I naturally thought they were coming from the database. In other words it was simply a bad guess on my part. <grin>