historical monthly/weekly/daily totals breakdown

I have a sales table( id, date , techid, amount)
id date techid amount
---
1 01/01/2005 tsmith 99.99
...
1054 08/16/2005 jborrow 29.99
I need to create historical report&graph with monthly totals (amount) for
every agent: Jan,Feb...Aug.
Jan Feb... Aug
tsmith 15898.44 69352.88 586311.55
How can I aggregate daily data in order to get historical
monthly/weekly/daily totals breakdown.
Is there any function or option (in Report designer)?
Thank you!You may want to read up on "grouping" in the RS Books Online and also look
at the "Company Sales" and the "Employee Sales Summary" sample reports of RS
2000.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"agenda9533" <agenda9533@.discussions.microsoft.com> wrote in message
news:4C7B3EAA-7FAC-400F-A80F-9586A4A1EFF1@.microsoft.com...
>I have a sales table( id, date , techid, amount)
> id date techid amount
> ---
> 1 01/01/2005 tsmith 99.99
> ...
> 1054 08/16/2005 jborrow 29.99
> I need to create historical report&graph with monthly totals (amount) for
> every agent: Jan,Feb...Aug.
> Jan Feb... Aug
> tsmith 15898.44 69352.88 586311.55
>
> How can I aggregate daily data in order to get historical
> monthly/weekly/daily totals breakdown.
> Is there any function or option (in Report designer)?
> Thank you!
>sql

Highest week of sales/usage

I'm working with Crystal Reports 10 with a Sql Server 2000 database. I've got a table which stores the details of items including the saletimes (a date/time field). I need to calculate the sales figure for the highest week of sales. In other words I need to find out what the highest amount of sales recorded in any given week. How can I do this?Quickest way would be to create a group based on date, and in the group options select "for each week" as the required grouping.
Create Sum(sigma sysmbol) fields for the data.
Suppress your details section.
In the group footer you will have Totals based on weekly aggregates.
In the Report menu, select TopN/Sort Group Expert.
Select the Tab for your weekly group (if you have more than one group that is).
In"for This Group Sort" change All to TopN.
Enter 1 in the "Where N is" field.
Remove the tick from the "Show others" checkbox.
OK that dialog.
Run you report. Should see only highest weeks.
May want to format the Date Field in your group to show start end end date of the week that finished top........

Hope this helps

dave|||re above - this is all client-side in Crystal.
would be quicker if could achieve it in SQL on server-side, but above solution is quick-ish, because Crystal's query to SQL Server will include an SQL-compliant "TopN" modifier.

dave