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.

 

[paulmarr]

karkia,

It would be helpful if you included which Crystal Reports version you are using.

You need to use the Group Sort Expert to show the Top N (N is a number you specify) groups - so if N=3 you can either group all the rest under the heading "Others" or create a new heading yourself - or omit the rest all together.

You need to insert summary fields on the Revenue Fields to see the Sum per Group.

Rather than starting with an SQL statement - remember that Crystal is building the SQL as you drag and drop objects on your report. Under the Database Menu - choose Show SQL to see what Crystal is passing to the database.

I hope this helps

Cheers
paulmarr