[b]Report to Capture all Table Values outside of Parameter[/b] 1

[elansienna]

I have been tasked with developing a report that should show the maximum and average number of users for each hour of the day for each day of the week dating back to the beginning of the year. The enduser will enter a Monday's day which drives the weekly list for the report. For example the report should look like the following after the enduser enters Monday, Feb. 19:

History-Work Week Monday Tuesday
Hour MAX AVG MAX AVG Feb-19 MAX AVG Feb-20
0 346 228 309 159 188 337 288 34
1 319
2 214
3 136

The data source is an excel spreadsheet with the following columns Date (date field),Day of Wk(number field), Hour (number field),User Count (number field) and I'm using Crystal Reports XI.

I have two questions:
1. Does anyone have a formula for determining the maximum and average based on the all the values in the spreadsheet and,

2. I can use a parameter to show a week's worth of data based on the enduser's choosing, but doesn't that limit the calculation on the formula to just the result set? The calculation should date back to the first of year.

I'm sorry for the length of this message, but I wanted to share as much as possible to provide a clearer picture of what I'm attempting to do. I appreciate your feedback. Thanks.

 

[Madawc]

Crystal has built-in functions for maximum and average. If you're not already familiar with Crystal totals, the basics are explained at FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[elansienna]

Thanks, but I need to know whether I can capture the maximum or minimum of a number field dating back to the beginning of the year if there is a parameter on the report where the user chooses a particular week to view on the report. The report will show the week, but the maximum and minimum is caculated not for that week but for example, all the values for a particular hour, day dating back to the first of year, not the parameter date.

 

[Madawc]

You could select data for the entire year, but suppress output except for the week.

Or put a subreport for the year in the report header and pass back the value using Shared Variables.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[elansienna]

Madawc,

I was thinking about the second option. Thanks for giving me a couple of ways to go with this! Also, thanks for the quick response.

elansienna