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

alangmcc gravatar 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

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(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

alangmcc gravatar 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 2261 times

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