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.

more ▼

asked Oct 03, 2011 at 05:10 AM in Default

avatar image

1 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

 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
more ▼

answered Oct 03, 2011 at 07:03 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 03, 2011 at 07:18 AM

avatar image

1 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 03, 2011 at 05:10 AM

Seen: 3397 times

Last Updated: Oct 03, 2011 at 06:53 AM

Copyright 2018 Redgate Software. Privacy Policy