First Aggregate Function

Question

First Aggregate Function

paula_sql

SSCrazy

Points: 2665
More actions
August 3, 2009 at 6:55 pm

#209039

Hi There,
Can someone please tell me when you need to use the FIRST Aggregate function?
I have noticed that at times Reporting services simply adds the First function into my expression. If it doesn't add it for you, does that mean you don't need it?
If I don't use the FIRST function, does that mean I am missing data?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Whenever you put a field in a group level that is not part of the grouping you need to use an aggregate and by default SSRS uses FIRST() for non-numeric fields and SUM() for numeric fields.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

paula_sql SSCrazy Points: 2665 More actions · Answer 2

Does that mean that SSRS inserts the First function automatically for you?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Yes if it is a non-numeric field in at a group level that is not included in the grouping. If you put a numeric field there then SSRS uses SUM().

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

paula_sql SSCrazy Points: 2665 More actions · Answer 4

Does that also mean I don't ever need to worry about inserting the First Function myself, or are their times that the First function may be needed that SSRS doesn't insert it for you?

bendy1999 SSC Eights! Points: 942 More actions · Answer 5

Always bear in mind, that when you do a first(), SSRS will show the first record that comes to the Row. Be sure that you know exactly which record that will be. I always use first() very, very cautiously. Actually, only if there is no risk of showing the "wrong" record by accident.

paula_sql SSCrazy Points: 2665 More actions · Answer 6

So its better to just let SSRS insert the First function for you automatically?

bendy1999 SSC Eights! Points: 942 More actions · Answer 7

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.

I hope I'm not confusing you more than helping you 🙂

Here is the description from Microsoft on the FIRST() function -> http://msdn.microsoft.com/en-us/library/ms159861.aspx

paula_sql SSCrazy Points: 2665 More actions · Answer 8

Thank you. Your explanation is very useful and I am beginning to understand more and more about the first function.

Thanks Again!!