I had an interesting request the other day – how do you show the top 10 (or 20) percent of vendor payments (or invoices) for a particular date range?
This is something that has to be done in a several steps – first you have to get your data, then summarize of it, then calculate the top X percent of the summary results.
Since the request was to vary the percentage and also the type of data looked at (invoices or payments), I would recommend making both of those parameters.
Below is sample code to make this work for Dynamics GP data. You can wrap this up in a stored procedure and add whatever other fields are needed to be able to use it in a report.
In my example I set the date parameters to be a pretty narrow range, to ensure the data returns quickly, but this will work for whatever date range you want to use and crossing years is no problem. You can also set the @top_pct parameter to be 100 to see all of the summary data. The @doc_type parameter can be either ‘invoices’ or ‘payments’.
declare @from datetime = '1/1/2016' declare @to datetime = '2/29/2016' declare @top_pct int = 10 declare @doc_type char(10) = 'invoices' -- or 'payments' select top (@top_pct) percent s.VENDORID Vendor_ID, v.VENDNAME Vendor_Name, s.Total from (select d.VENDORID, sum(d.DOCAMNT) Total from (select VENDORID, DOCAMNT from PM20000 where DOCDATE between @from and @to and VOIDED = 0 and DOCTYPE = (case @doc_type when 'invoices' then 1 when 'payments' then 6 end) union all select VENDORID, DOCAMNT from PM30200 where DOCDATE between @from and @to and VOIDED = 0 and DOCTYPE = (case @doc_type when 'invoices' then 1 when 'payments' then 6 end) ) d --data group by d.VENDORID) s --summary left outer join PM00200 v --vendor master on s.VENDORID = v.VENDORID order by s.Total desc