I would really appreciate some help with a report builder head scratcher I am currently experiencing. I have a dataset which contains the fields Individual PlanID Outcome Review date Progress All of the fields apart from review date and progress also have associated ID numbers. individuals can have multiple plans plans can have multiple outcomes Outcomes can have multiple review dates Review dates have one progress record per outcome (but that means on different review dates they can have difference progresses) e.g. What I want to do is have a dataset that has the last recorded progress per outcome per plan for each individual. So in the example above, 3 rows should be returned. I want to be able to do this in the dataset as opposed to using expressions in a table as I need to make charts, and cut the data up in different ways that don't easily seem to be achievable when grouping within a table/matrix. Is this possible? If so, I'd really appreciate any help. I am fairly new to SSRS and this has me going round in circles! Thanks Eils

Hi, This is possible to do in your data set, you need to write a query to do this. It looks like the latest review date will return the correct data. You can use a cte to do this : with cte as ( select 'Joe Bloggs' as Individual, '001' as PlanID, 'Healthy' as Outcome, '01/01/2012' as ReviewDate, 'Not met' as Progress union all select 'Joe Bloggs' as Individual, '001' as PlanID, 'Healthy' as Outcome, '01/02/2012' as ReviewDate, 'Not met' as Progress union all select 'Joe Bloggs' as Individual, '001' as PlanID, 'Healthy' as Outcome, '01/03/2012' as ReviewDate, 'Partially met' as Progress union all select 'Joe Bloggs' as Individual, '001' as PlanID, 'Healthy' as Outcome, '01/04/2012' as ReviewDate, 'Fully met' as Progress union all select 'Joe Bloggs' as Individual, '001' as PlanID, 'Safe' as Outcome, '01/01/2012' as ReviewDate, 'Partially met' as Progress union all select 'Joe Bloggs' as Individual, '001' as PlanID, 'Safe' as Outcome, '01/02/2012' as ReviewDate, 'Partially met' as Progress union all select 'Joe Bloggs' as Individual, '001' as PlanID, 'Safe' as Outcome, '01/03/2012' as ReviewDate, 'Partially met' as Progress union all select 'Joe Bloggs' as Individual, '001' as PlanID, 'Safe' as Outcome, '01/04/2012' as ReviewDate, 'Partially met' as Progress union all select 'Joe Bloggs' as Individual, '002' as PlanID, 'Healthy' as Outcome, '01/05/2012' as ReviewDate, 'Not met' as Progress union all select 'Joe Bloggs' as Individual, '002' as PlanID, 'Healthy' as Outcome, '01/06/2012' as ReviewDate, 'Not met' as Progress ), cte1 as (select Individual,planid,Outcome,max(reviewdate) as mx from cte group by Individual,planid,Outcome)--returns the max review date select *, ( select progress from cte where cte.reviewdate=cte1.mx and cte.outcome=cte1.outcome and cte.planid=cte1.planid and cte.individual=cte1.individual) as progress from cte1 order by planid Hope this helps!

thanks Nasa1999 - this gave me a lot to think about and I went off googling CTEs (I am a fairly basic SSRS user, constructing queries in report builder but haven't got a huge amount of coding experience). However I think the problem with this approach is that the developer who constructed the system I am reporting on has provided a number of report models that provide a view onto the table spagetti in our system so I think this might rule out the CTE option. :(

I think I may have over complicated the query all you really need is to return the maximum reviewdate for each individual. If join the query below to your main query and add the extra 'mx' field to your dataset you can then filter your dataset and this will give you the same results. To filter the dataset right click on the dataset and then go to dataset properties and filters, the filter should be reviewdate field in the 'mx' field select a.*,mx from yourtable a left join (select Individual,planid,Outcome,max(reviewdate) as mx from yourtable group by Individual,planid,Outcome) as b on a.individual=b.individual and a.outcome =b.outcome and a.planid=b.planid