0

I'm trying to created a report for my asp.net application which will show the quantity of each item in combination with unit that was ordered for each day of the week. The days of the week are columns.

To be more specific: I have two table, one is the Orders table with order id, customer name, date etc... The second table is OrderItems, this table has order id as a foreign key, order Item id, item name, unit (exp: each, box , case), quantity, and price.

When a user picks a date range for the report, for example from 3/2/12 to 4/2/12, on my asp page, the report will group order items by week and will look as follows:

**week (1) starting from sunday of such date to saturday of such date**


item | unit | Sun | Mon  | Tues | Wedn | Thur | Fri | Sat | Total Price for week

item1 | bag |  3  |   0  | 12   | 8    |  45  | 1   | 4   | $1234
item4 | box |  2  |   4  |  5   | 0    |   5  | 2   | 6   | $1234


**week (2) starting from sunday of such date to saturday of such date**


item | unit | Sun | Mon  | Tues | Wedn | Thur | Fri | Sat | Total Price for week

item1 | bag |  3  |   0  | 12   | 8    |  45  | 1   | 4   | $1234
item4 | box |  2  |   4  |  5   | 0    |   5  | 2   | 6   | $2354

**week (2) starting from sunday of such date to saturday of such date**

item | unit | Sun | Mon  | Tues | Wedn | Thur | Fri | Sat | Total Price for week

item1 | bag |  3  |   0  | 12   | 8    |  45  | 1   | 4   | $1234
item4 | box |  2  |   4  |  5   | 0    |   5  | 2   | 6   | $2354

I wish I could have something to show that I have already started, but crystal isn't my strong point and I dont even know where start tackling this one. I do know how to pass parameters and a datatable that I myself pre-filtered before passing it to the report. For example filtering items by date range and customer or order id.

any help would be much appreciated

boruchsiper
  • 2,016
  • 9
  • 29
  • 53

1 Answers1

0

Create a formula for each day of the week that totals the order.

ie Sunday quantity:

if dayOfWeek(dateField) = 'Sun'
then order.quantity
else 0

Add each day formula to the detail section of the report and then summarize it for each group level. To group it by week, just group by the date field, then set the grouping option to by week. Suppress the detail, and you'll have what you are looking for.

  • I don't remember the exact name of the dayOfWeek function, but it's something like that.
SunDevilKid85
  • 16
  • 1