Limit result in reports

Question

Hi. 
 I'm building a report with some different statistics of our ITShops, arranged by the individual ITShop. 
 But I do not know how to reduce the amount of results by each shop. 
 I'll try to explain, and hopefully this is understandable... 
 For example I want to return the top 10 of old orders, by ITShop. The following query, gives me the result in combination with a query listing each unique shop, and an main Databand. And in this databand I have added for a panel with an other databand, with a relation to the main databand query. 
 Then I have the following query, that is hardcoded by each shop using "UNION". 
 Example of Query: 
 declare @ret int = 10
SELECT TOP(@ret)
	pwo.DisplayOrg AS 'Product'
	,pwo.DisplayPersonOrdered 
	,pwo.OrderDate
	,ISNULL(pwo.DateHead,'1899-12-30') AS 'Last updated'
	,ito.Ident_Org AS 'ITShop' 
FROM ITShopOrg ito 
INNER join PersonWantsorg pwo on pwo.UID_OrgParentOfParent = ito.UID_ITShopOrg
	AND OrderState like N'Order%' 
	AND isnull(datehead, '1899-12-30') < Dateadd(WW, -1, GetUTCDate()) 
	AND Orderdate < Dateadd(d, -1, GetUTCDate())
	AND ito.Ident_Org = 'Shop number 1'
WHERE ito.ITShopInfo='SH'
UNION ALL
SELECT TOP(@ret)
	pwo.DisplayOrg AS 'Produkt'
	,pwo.DisplayPersonOrdered 
	,pwo.OrderDate
	,ISNULL(pwo.DateHead,'1899-12-30') AS 'Last updated'
	,ito.Ident_Org AS 'ITShop' 
FROM ITShopOrg ito 
INNER join PersonWantsorg pwo on pwo.UID_OrgParentOfParent = ito.UID_ITShopOrg
	AND OrderState like N'Order%' 
	AND isnull(datehead, '1899-12-30') < Dateadd(WW, -1, GetUTCDate()) 
	AND Orderdate < Dateadd(d, -1, GetUTCDate())
	AND ito.Ident_Org = 'Shop number 1'
WHERE ito.ITShopInfo='SH'

etc.... 
 As you can se, this is not a very dynamic way to solve what I want, as I then have to update the queries in the report each time we add/remove an ITShop. And the queries gets quite big, as the number of different shops we have is growing. 
 
 Hopefully this is understandable. Thanks! 
 
 Kim.

Andreas Finsterbusch · Accepted Answer

Something like this declare @ret int = 10
select Product, DisplayPersonOrdered, OrderDate, [Last updated], ITShop
	from 
	(
 SELECT Row_number() over(partition by ito.Ident_Org order by pwo.DateHead ) rn#
 ,pwo.DisplayOrg AS Product
 ,pwo.DisplayPersonOrdered 
 ,pwo.OrderDate
 ,ISNULL(pwo.DateHead,'1899-12-30') AS [Last updated]
 ,ito.Ident_Org AS ITShop
 FROM ITShopOrg ito 
 join PersonWantsorg pwo on pwo.UID_OrgParentOfParent = ito.UID_ITShopOrg
 AND OrderState like N'Order%' 
 AND isnull(datehead, '1899-12-30') < Dateadd(WW, -1, GetUTCDate()) 
 AND Orderdate < Dateadd(d, -1, GetUTCDate())
 WHERE ito.ITShopInfo='SH'
	) x where x.rn# <= @ret

Limit result in reports

Top Replies