Using SQL Server Reporting Services 2005.
Hi there here is some example data similar to that, that I am working with
So I am grouping by Reference and then sorting the table by Reference and then UpdateID .
The other value I need is the latest payment entry for 'Payment 1' that is not null or 0, therefore reference 111 should return 50 and reference 112 should return 100 (Note this is the last value entered and not the highest value that we are looking for.)
Thanks in advance for any guidance, Al.
Personally I would do this in the TSQL that builds the data set. Use a CASE statement to put values into a column. Use 1 for the rows you want to single out and 0 for those you dont...
answered Oct 03, 2011 at 07:03 AM
Thank you very much for your quick reply, that would work for a single data line entry - but my problem is i need this information grouped and all the data to also be displayed.
So what I want to show is the first reserve that was placed on a given refernce which can have several entries and updates associated with it - this is where the UpdateID comes in.
So I want to display in a group so I can show the very first entry that was input for Reserve 1 that was not null or 0 for a given Reference.
I am familar with the case statement but I dont know how i could get this to work inside a group in TSQL as i would need to inspect all References which are identical and analyse each Reserve 1 field for each entry in order of UpdateID.
Hope this makes sense. Thanks again, Al.
Ps.I had tried the first and last aggregates in ssrs 2005 - but these dont ignore nulls or 0.
answered Oct 03, 2011 at 07:18 AM