quote:

---

Originally posted by rcr69er

Thanks, but how would i get the report to show only mix and match items when 'True' is selected?

Sorry I'm not the greatest with SSRS!

Thanks

---

SELECT 
CASE 
When i.statusid = 90000 and sum(ist.InStock) > 0 Then 1 
When (sum(ist.InStock) > 0) then 2 -- Purple booking diary date stock
When (sum(ist.InStock) IS NULL and min(pl.ExpDelDate) IS NOT NULL) then 3 -- blue booking diary date but no stock
When (sum(ist.InStock) IS NULL or sum(ist.InStock) = 0) then 4
ELSE 5 -- green
End AS 'OrderBy'
,i2.InventoryId
,i.InventoryID
,isgi.InventorySubGroupId
,ig.InventoryGroupId
,ig.Name
,CAST(CASE 
WHEN ig.InventoryGroupId IS NOT NULL THEN 'Y'
ELSE 'N'
END AS bit) AS 'MixAndMatchItem'

FROM 
INVENTORY AS i WITH (nolock) 
JOIN INVENTORY AS i2 WITH (nolock) 
ON i.ParentID = i2.InventoryID
LEFT OUTER JOIN InventoryStock 
AS ist WITH (nolock) ON i.InventoryID = ist.InventoryId 
LEFT JOIN (
select inventoryID,statusid,ExpDelDate,sum(quantity) as Quantity,sum(qtyreceived) as qtyreceived 
from purchaseitem 
where statusid Not in (60000)	
group by inventoryID,statusid,ExpDelDate
) AS POI 
ON i.InventoryID = poi.InventoryID
JOIN InventorySubCategory AS isc WITH (nolock) 
ON i.SubCategoryID = isc.SubCategoryID 
JOIN InventoryCategory AS ic WITH (nolock) 
ON i.CategoryID = ic.CategoryID 
JOIN vwInventoryCategoryGroup AS ICG 
ON ICG.categoryId = ic.CategoryID 
JOIN SecGroup AS sg WITH (nolock) 
ON isc.GroupId = sg.GroupID 
LEFT OUTER JOIN vwMagazine As vm WITH (nolock) 
ON i2.InventoryID = vm.InventoryId 
LEFT JOIN vwLastPurchaseLockid pl 
ON i.inventoryid = pl.inventoryId
LEFT JOIN dbo.InventorySubGroupItem AS isgi WITH (NOLOCK)
ON i2.InventoryId = isgi.InventoryId
LEFT JOIN dbo.InventorySubGroup AS isg WITH (NOLOCK)
ON isgi.InventorySubGroupId = isg.InventorySubGroupId
LEFT JOIN dbo.InventoryGroup AS ig WITH (NOLOCK)
ON isg.InventoryGroupId = ig.InventoryGroupId

GROUP BY 
i.statusid
,i2.InventoryId
,i.InventoryID
,isgi.InventorySubGroupId
,ig.InventoryGroupId
,ig.Name

ORDER BY 
OrderBy
,i2.InventoryId ASC

=Fields!MixAndMatchItem.value

=IIF(Parameters!yourparam.value<>2,Parameters!yourparam.value,Fields!MixAndMatchItem.value)