Trying to use a group by statement in an aggregate in SSRS Report Builder 2008

Question

I have a report in SSRS 2008 that has two row groupings. The lowest level of detail is grouped by legalcase, and then there is a summary row that is grouped by outside counsel name. The query in the dataset is actually returning records at a lower detail than these two groupings, it is returning individual bill payments on legal cases. So there are many bill payments to each legal case, and many legal cases to each outside counsel. The final column I'm trying to display is an outside counsel evaluation score, which there is 1 of for each legal case. The score displays fine for the detail rows, but when I try to do an aggregate (sum or average), it is not aggregating correctly. It is counting the score for each bill payment, instead of doing it by legal case. So for example, if a legal case had one outside counsel score of 10, and 3 bill payments, it would allocate 3 10's for this legal case, instead of just 1. Is there a way that I can define a group by statement in my aggregate statement so that it groups the outside counsel scores by legal case, not by bill payment?

Answer 1

Your groupings are by level. So if you have a parent group, you should choose the option 'add group header'. Then you will have data on a new row. Then you could do your calculation there instead and it will group the aggregate data correctly.

EG:

This won't work:

Single Line:  Group1Data, DetailData, Sum(DetailData)

It would just repeat DetailData twice as the detail level is the same as the group

This should work if your grouping is done correctly:

Header Line:  Group1Data, Sum(DetailData)
Detail Line:  (blank)       , DetailData

You could have MANY header lines, one for each case for example. But the detail underneath is defined by the group definition and will only display for that group each time. This can work on multiple levels of groupings as well.

Answer 2

Since that value is copied, the aggregate you would use is First().