asked
Display First value that is not null or 0 in a grouping in ssrs 2005
Using SQL Server Reporting Services 2005. Hi there here is some example data similar to that, that I am working with Reference UpdateID Reserve 1 Payment 1 111 1 null null 111 2 50 null 111 3 100 50 112 1 null null 112 2 20 null 112 3 20 20 112 3 20 100 112 4 20 null So I am grouping by Reference and then sorting the table by Reference and then UpdateID . So I want to display some info about the grouped data in my group footer. The first value I want to return in the group footer is the first 'Reserve 1' value that appears which is not null or equal to Zero. So in the case of reference 111 this value should be 50 and in the case of reference 112 this should be 20. 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... USE adventureworks GO WITH Contacts_CTE AS ( SELECT [c].[Title] , [c].[FirstName] , [c].[MiddleName] , [c].[LastName] , [c].[Suffix] , [c].[ModifiedDate] , ROW_NUMBER() OVER ( PARTITION BY lastname ORDER BY modifieddate ) AS r_n FROM [Person].[Contact] AS c ) SELECT [Contacts_CTE].[Title] , [Contacts_CTE].[FirstName] , [Contacts_CTE].[MiddleName] , [Contacts_CTE].[LastName] , [Contacts_CTE].[Suffix] , [Contacts_CTE].[ModifiedDate] , CASE WHEN [Contacts_CTE].[r_n] = 1 THEN 1 ELSE 0 END AS ShowOnReport FROM contacts_cte
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.
