Group by Product, Store--HOW?

[karkia]

Hi,
I have 2 dimension tables, STORE and PRODUCT. The REVENUE fact contains the revenue for each product sold from every store. I need to extract just 3 stores with the highest revenue. My report should look as follows-
PRODUCT1
SN STORE_NAME REVENUE %ofGRANDTOTAL
-- ---------- ------- --------
1 Store1 5,000 10.00%
2 Store5 4,000 8.00%
3 Store9 2,000 4.00% (% of GrandTotal, not Total)
------- ------
TOTAL:11,000 22.00%
GRAND TOTAL for PRODUCT1: 50,000 (includes revenue from the remaining stores which dont fall in top3)

Similarly, for PRODUCT2,.....,PRODUCTn.

I am using CR-11. When i design the report, it is being grouped by PRODUCT, but under each PRODUCT, the revenue for (say Store1) is not aggregated. An equivalent SQL would be-

SELECT ....,SUM(REVENUE)
FROM .....
WHERE <conditions>
GROUP BY PRODUCT,STORE
ORDER BY SUM(REVENUE)-->so store with most sale for each product appears at the top..and the top 3 for each can be displayed in the report.

Also, how to generate ranks for Serial# (SN) field?

Please suggest.

 

[skrandel]

You might get a better result in a Crystal forum.

Steve Krandel
Symantec