infomania
Programmer
- Oct 27, 2002
- 148
I have a report where I want to list the details for the Top 80% (by sales) of items, a summary line for the next 10% and again for the next 10%. Briefly, the SQL looks like this (to return all records):
SELECT
item
, descr1
, count(distinct ordNbr) as orders
, sum(sales) as sales
FROM proddta.HIST_SALES0203
WHERE invDate between {?start} and {?stop}
GROUP BY item, descr1
ORDER BY sales DESC
I've tried using TOP PERCENT, but this is not sensitive to the cumulative sum of sales (returning only the TOP PERCENT of records). My only answer, at this point is to manually inspect the data each time I run the report and then use TOP N queries to split the data.
I am using Crystal Reports 9 as a reporting tool.
Any ideas??? Thanks.
SELECT
item
, descr1
, count(distinct ordNbr) as orders
, sum(sales) as sales
FROM proddta.HIST_SALES0203
WHERE invDate between {?start} and {?stop}
GROUP BY item, descr1
ORDER BY sales DESC
I've tried using TOP PERCENT, but this is not sensitive to the cumulative sum of sales (returning only the TOP PERCENT of records). My only answer, at this point is to manually inspect the data each time I run the report and then use TOP N queries to split the data.
I am using Crystal Reports 9 as a reporting tool.
Any ideas??? Thanks.