SSRS - Show only top 50 rows

Question

I am using SSRS 2014 version.

I have below SSRS report and I wanted to display only first 50 rows (Supplier),

How can I do this please?

MY SQL code is,

SELECT s.[CusNo] Supplier, 
RTRIM(CAST(s.[Customer] AS VARCHAR(50)) ) AS Name,
s.[ConcessionNo] Concession, 
RTRIM(CAST(s.[ConcessionName] AS VARCHAR(50)) ) AS ConcessionName,

 sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) 
           then s.SELLINC else 0 end) ActualSales,

     sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
           then s.SELLINC else 0 end) LastYrVariance         

 (sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) then s.SELLINC else 0 end))-

 (sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */) then s.SELLINC else 0 end)) LastYrVariancePounds,

 (IsNull(sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) 
       then s.SELLINC else 0 end)-sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
       then s.SELLINC else 0 end),0)/NullIf(sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
       then s.SELLINC else 0 end),0))*100 LastYrVariancePercentage,


sum(case when s.Date 
    BETWEEN         
         convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(GetDate()) = 1 THEN CONVERT(VARCHAR(4), GetDate(), 112) - 1 ELSE CONVERT(VARCHAR(4), GetDate(), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
    AND        
         Convert(date, dateadd(wk, datediff(wk, 0, GETDATE()) - 1, 0) + 5)       
       then s.SELLINC else 0 end) YrToDateActual,


sum(case when s.Date    
    BETWEEN  
       convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
    AND
       convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
      then s.SELLINC else 0 end) LastYrToDateActual,


(sum(case when s.Date 
 BETWEEN        
         convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(GetDate()) = 1 THEN CONVERT(VARCHAR(4), GetDate(), 112) - 1 ELSE CONVERT(VARCHAR(4), GetDate(), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
    AND        
         Convert(date, dateadd(wk, datediff(wk, 0, GETDATE()) - 1, 0) + 5) 
       then s.SELLINC else 0 end))  

 -

(sum(case when s.Date 
    BETWEEN  
       convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
    AND
       convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
       then s.SELLINC else 0 end)) YrToDateVariancePounds,


((IsNull    
     (                   
        (sum(case when s.Date 
         BETWEEN        
         convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(GetDate()) = 1 THEN CONVERT(VARCHAR(4), GetDate(), 112) - 1 ELSE CONVERT(VARCHAR(4), GetDate(), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
    AND        
         Convert(date, dateadd(wk, datediff(wk, 0, GETDATE()) - 1, 0) + 5)
       then s.SELLINC else 0 end))  

 -

(sum(case when s.Date 
    BETWEEN  
       convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
    AND
       convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
       then s.SELLINC else 0 end))

      ,0)

  ) 

  /

(NullIf 
  (
     sum(case when s.Date 
        BETWEEN  
       convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
    AND
       convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
       then s.SELLINC else 0 end)          

   ,0)))*100 LastYrToDateVariancePercentage

FROM [dbo].[CustomerReports] s WHERE s.BRN = 1 or s.BRN = 2 or s.BRN = 3 or s.BRN = 4 or s.BRN = 5 or s.SELLINC is null or s.SELLINC = '0' GROUP BY s.[CusNo], s.[Customer], s.ConcessionNo, s.ConcessionName order by YrToDateActual desc

This is my complete code

Will · Answer 1 · 2017-03-10T15:12:17.277

This should work:

SELECT
*
FROM
(
SELECT
*,
RANK() OVER (PARTITION BY Supplier ORDER BY YrToDateActual DESC) AS Rank
FROM(
SELECT 
s.[CusNo] Supplier, 
RTRIM(CAST(s.[Customer] AS VARCHAR(50)) ) AS Name,
s.[ConcessionNo] Concession, 
RTRIM(CAST(s.[ConcessionName] AS VARCHAR(50)) ) AS ConcessionName,

 sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) 
           then s.SELLINC else 0 end) ActualSales,

     sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
           then s.SELLINC else 0 end) LastYrVariance         

 (sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) then s.SELLINC else 0 end))-

 (sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */) then s.SELLINC else 0 end)) LastYrVariancePounds,

 (IsNull(sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) 
       then s.SELLINC else 0 end)-sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
       then s.SELLINC else 0 end),0)/NullIf(sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
       then s.SELLINC else 0 end),0))*100 LastYrVariancePercentage,


sum(case when s.Date 
    BETWEEN         
         convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(GetDate()) = 1 THEN CONVERT(VARCHAR(4), GetDate(), 112) - 1 ELSE CONVERT(VARCHAR(4), GetDate(), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
    AND        
         Convert(date, dateadd(wk, datediff(wk, 0, GETDATE()) - 1, 0) + 5)       
       then s.SELLINC else 0 end) YrToDateActual,


sum(case when s.Date    
    BETWEEN  
       convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
    AND
       convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
      then s.SELLINC else 0 end) LastYrToDateActual,


(sum(case when s.Date 
 BETWEEN        
         convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(GetDate()) = 1 THEN CONVERT(VARCHAR(4), GetDate(), 112) - 1 ELSE CONVERT(VARCHAR(4), GetDate(), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
    AND        
         Convert(date, dateadd(wk, datediff(wk, 0, GETDATE()) - 1, 0) + 5) 
       then s.SELLINC else 0 end))  

 -

(sum(case when s.Date 
    BETWEEN  
       convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
    AND
       convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
       then s.SELLINC else 0 end)) YrToDateVariancePounds,


((IsNull    
     (                   
        (sum(case when s.Date 
         BETWEEN        
         convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(GetDate()) = 1 THEN CONVERT(VARCHAR(4), GetDate(), 112) - 1 ELSE CONVERT(VARCHAR(4), GetDate(), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
    AND        
         Convert(date, dateadd(wk, datediff(wk, 0, GETDATE()) - 1, 0) + 5)
       then s.SELLINC else 0 end))  

 -

(sum(case when s.Date 
    BETWEEN  
       convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
    AND
       convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
       then s.SELLINC else 0 end))

      ,0)

  ) 

  /

(NullIf 
  (
     sum(case when s.Date 
        BETWEEN  
       convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30)) / 7  * 7, '19000107'), 120)
    AND
       convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */)
       then s.SELLINC else 0 end)          

   ,0)))*100 LastYrToDateVariancePercentage    

FROM [dbo].[CustomerReports] s 
WHERE 
s.BRN = 1 or s.BRN = 2 or s.BRN = 3 or s.BRN = 4 or s.BRN = 5 or s.SELLINC is null or s.SELLINC = '0' 

GROUP BY 
s.[CusNo], 
s.[Customer], 
s.ConcessionNo, 
s.ConcessionName

) AS sub
) AS sub

WHERE
Rank <= 50

order by 
YrToDateActual desc

Thanks. I have added SQL code in my question. Could you please update my code? — user2331670, Mar 07 '17 at 16:12
i've updated my answer again, order by needs to come after the subquery, so can only order by columns that have been selected when doing it this way, there may be a better way, im not sure — Will, Mar 07 '17 at 16:45
Thanks. Now I have uploaded full code in my question. Please help. Where to Put Order by — user2331670, Mar 07 '17 at 16:57

score 0 · Answer 2 · answered Mar 07 '17 at 15:56

0

You could do this in your query. If that is not an option use the group filter to filter the dataset by top N rows.

answered Mar 07 '17 at 15:56

NewGuy

1,020
1
9
24

thanks - But when I do this, It work partially. When I add this Top N filter then report produces Top 50 rows in Supplier Column. But in Concession column (3rd from left) few values are missing. – user2331670 Mar 07 '17 at 16:08
add SELECT Top (50) s.[CusNo] Supplier, .. to your query. – NewGuy Mar 07 '17 at 16:15
Still the same issue. Few values are missing in concession column – user2331670 Mar 07 '17 at 16:19

SSRS - Show only top 50 rows

2 Answers2

Linked