Hello - I'm new to Crystal but managed to get a SQL working report of the highest top ten paid individual claims by vendor using code below...

The issue is, they want the highest top ten SUMMarized claim settlements paid by distinct vendor in descending order (and how many unique member claims are are within this sum)...

I haven't been able to get anywhere doing standard report creation with the wizard to set up "top N" and summary reports, so any help is much appreciated!

--Set up to pull top vendors for the period 1/1/2010 - 12/31/2010
--non-'HOSP' only claims
--Paid claims -- MASTER_CLAIMS table which has all reconciled claims
--VENDOR table contains vendor info

SELECT DISTINCT TOP 10
PopClaim = CASE WHEN mc.DOCUMENT IS NOT NULL Then 1 ELSE 0 END, --increment for counting claims
mc.TOTAL_PAY_AMT,
mc.BILL_AMT,
PercVenPd = ((mc.TOTAL_PAY_AMT/mc.BILL_AMT)*100),
SUM(mc.TOTAL_PAY_AMT) as TotalPaid,
v.Last_Name as VendorName,
...
FROM
MASTER_CLAIM mc
INNER JOIN VENDOR v ON mc.VENDOR_NPI = v.NPI
WHERE
mc.FIRST_DOS BETWEEN '01/01/2010' AND '12/31/2010' --date criteria
AND
mc.VENDOR_TYPE <> 'HOSP' --non-hospital only claims
AND mc.TOTAL_PAY_AMT <> 0.00

AND mc.TOTAL_PAY_AMT IS NOT NULL
AND mc.BILL_AMT <> 0.00
AND mc.BILL_AMT IS NOT NULL
...
AND mc.Document NOT LIKE 'R%' --don't include reversed claims
GROUP BY mc.TOTAL_PAY_AMT,
mc.BILL_AMT,
mc.DOCUMENT,
v.Last_Name,
...
ORDER BY mc.BILL_AMT DESC