report design advice

[Achmed]

Hello all,

I need to create a report and I'm wondering what a good way to gather the data would be. The report will summarize sales for various products in weekly periods. The data is stored in a table with approximately 500,000 records for the quarter (SaleDate, ProductCode, QuantitySold). The report will have 13 columns (1 for each week in the current quarter), and a bunch of rows (1 for each product).

Sample:
Product W1 W2 W3 W4
Prod1 100 140 300 250
Prod2 14 20 15 38

I could create 1 query for each week, and join these 13 queries together and put that on the report, but this seems messy.

The data is stored in SQL Server 2000, and I'm using Crystal Reports 10.

Any ideas?
Thanks
-Alan

 

[lbass]

Insert a crosstab with {table.saledate} as the column, {table.prodcode} as the row, and quantity sold as the summary field (sum). Select the column field (saledate) and choose "group options" and then select "a column will be printed for each week."

Your record selection statement should limit the dates to the quarter you are interested in. If you are always looking at quarters for the current year, you could use:

{table.saledate} in Calendar1stQtr //or Calendar2ndQtr, Calendar3rdQtr, Calendar4thQtr

You might also decide to use a parameter for the date range.

-LB

 

[Achmed]

LB,

Thanks for the help.

-Alan