I am trying to create a report to show Usage trends. The Form I have allows the User to input the Date and Shift, and then the Quantity for each Product. (There is also a field on the form that calculates the day of the week on Date_Field.getday()) The User needs to be able to select the Day of the Week and select how many weeks to return, and then the report needs to output the usage for each day, with the averages.

 

Example:

 

User could Input Weekday = 1 (for Monday)

User could request the last 3 Mondays

 

Report:

 

Date            Brisket      Turkey      Chicken      

Shift: AM

11/01/2010            5            1           1

10/25/2010            5            4            2

10/18/2010            6            3            3

Average                5.33         2.67      3

 

Shift: PM            

11/01/2010            9            8           7

10/25/2010            6            5           4

10/18/2010            3            2           1

Average                6            5            4

 

Is a report like this possible? When I try to add the Day of the Week filter, it says can't filter based on a number, but I haven't been able to find a function where it will return the Name of the day of the week, just the number.

 

I also don't know how to let the user select the number of entries to return.

 

Thanks