Display First value that is not null or 0 in a grouping in ssrs 2005

Question

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.

Answer 1

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

Answer 2

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.

Display First value that is not null or 0 in a grouping in ssrs 2005

Follow this question

Related Questions