locked
"First" and "Last" Functions Failing RRS feed

  • Question

  • I'm reporing using SSRS 2005 out of VS TFS 2005.  I'm reporting on items where a date field has changed in the past week, and I'm grouping based on the item's unique ID.  In some cases, a date changes more than once in the past week, so in the detail lines I'm reporting all changes.  In the group header (named "Group"), I want to show the Work Item #, the Revision #, the Original Date, and the New Date.  So ideally, this would be my output:

    Work Item         Revision            Date                Original Date                 New Date
    101                                                                  1/2/2010                      11/17/2009             (This is the group header)
        101                   3                  11/17/2009                                                                       (Drill down detail lines)
        101                   2                  9/1/2009
        101                   1                  1/2/2010

    I don't want the MAX and the MIN, I want the FIRST and LAST.  I'm using the following functions:

    Original Date:  =FIRST(Fields!Date.Value,"Group")
    New Date:  =LAST(Fields!Date.Value,"Group")

    Sometimes this work like it should... however I have some anomalies, where both FIRST and LAST are returning what should be returned by FIRST.  So in the case above, both Original Date and New Date are returning 1/2/2010.

    Any ideas why this might be?
    Friday, November 13, 2009 8:57 PM

Answers

  • Hello Aaron,

    Please have a look on this article
    http://www.sqlservercentral.com/Forums/Topic764505-150-1.aspx

    specially this part
    "
    You can also add it by yourself. Imagine that you have an unsorted dataset that consists of two columns.

    the Customer Name and the date of the orders that customer has placed.

    you want to show each customer name and the date of the first order next to it.

    If you group on the Customer name and add =FIRST(orderdate) in the Group row, you will get the first date that appears in the dataset, not necessarily the oldest date. That would give you wrong data in the end.

    To avoid this i would use =MIN(orderdate) instead of =FIRST(orderdate).

    Don't get me wrong, FIRST() is very useful, if you understand the workings of it. It doesn't always return the first value as we expect it (like the date-example above), but always returns the first value that comes from the Dataset."

    Pleave observe your data in little detail , i am sure First and Last are behaving as they are supposed too.


    Thanks


    • Marked as answer by Aaron Jarboe Friday, November 13, 2009 9:28 PM
    Friday, November 13, 2009 9:18 PM

All replies

  • Hello Aaron,

    Please have a look on this article
    http://www.sqlservercentral.com/Forums/Topic764505-150-1.aspx

    specially this part
    "
    You can also add it by yourself. Imagine that you have an unsorted dataset that consists of two columns.

    the Customer Name and the date of the orders that customer has placed.

    you want to show each customer name and the date of the first order next to it.

    If you group on the Customer name and add =FIRST(orderdate) in the Group row, you will get the first date that appears in the dataset, not necessarily the oldest date. That would give you wrong data in the end.

    To avoid this i would use =MIN(orderdate) instead of =FIRST(orderdate).

    Don't get me wrong, FIRST() is very useful, if you understand the workings of it. It doesn't always return the first value as we expect it (like the date-example above), but always returns the first value that comes from the Dataset."

    Pleave observe your data in little detail , i am sure First and Last are behaving as they are supposed too.


    Thanks


    • Marked as answer by Aaron Jarboe Friday, November 13, 2009 9:28 PM
    Friday, November 13, 2009 9:18 PM
  • Ah - Perfect!  I simply had to add Revision to the ordering of my query.  Thanks!
    Friday, November 13, 2009 9:28 PM
  • Great. Happy it worked for you:)
    Friday, November 13, 2009 9:38 PM