I am using the below query to bring back the last item number. The problem is that sometimes the report has two or three entrys on the same day so it brings back multiple results... is there a way in ssrs to say ' if Item number is repeated put a drillthrough + on the item number? Declare @english table ( entType varchar(20), entNo varchar(20) ) Insert into @english(entNo,entType) Values (0,'Purchase') Insert into @english(entNo,entType) Values (1,'Sale') Insert into @english(entNo,entType) Values (2,'Positive Adj') Insert into @english(entNo,entType) Values (3,'Negative Adj') Insert into @english(entNo,entType) Values (4,'Transfer'); --declare @startNo varChar (20) --declare @endNo varChar (20) --declare @startDate varChar (20) --declare @endDate varChar (20) --declare @categoryCode varChar (20) --set @startNo =null --set @endNo = null --set @startDate = null --set @endDate = null --set @categoryCode = 'CUTTER' ;with rs as ( select a.[Item No_] ,a.[Entry Type] ,a.[Posting Date] ,a.[Global Dimension 1 Code] ,a.[Global Dimension 2 Code] ,a.[Item Category Code] ,b.[Description] ,b.[Description 2] ,SUM(a.[Quantity])over(partition by [Item No_]) as Quantity, RANK() OVER (partition by [Item No_] order by [Posting Date] DESC) as rn from [Ulterra Drilling Technologies$Item Ledger Entry] a join [Ulterra Drilling Technologies$Item] b on b.No_=a.[Item No_] where [Item No_] between COALESCE(@startNo,[Item No_]) and COALESCE(@endNo,[Item No_]) and a.[Item Category Code] = COALESCE(@categoryCode,a.[Item Category Code]) and [Posting Date] between COALESCE(@startDate,[Posting Date]) and COALESCE(@endDate,[Posting Date]) ) select distinct [Item No_] ,[Global Dimension 1 Code] ,[Global Dimension 2 Code] ,[Entry Type] ,[Posting Date] ,entType ,[Description] ,[Description 2] ,[Quantity] ,[Item Category Code] from rs r join @english e on e.entNo=r.[Entry Type] where [Quantity]<> '0' and rn = 1;

Hi, If you will create group on Item Number and Set the visibility then it will do that for you. If there will be single Item number then it will be single row , if multiple Item Numbers then it will group by , put + Check this http://sql-bi-dev.blogspot.com/2010/09/groups-in-ssrs-2008.html Thankshttp://sqlage.blogspot.com/