Scope problem

Hello,
I have a single table with 1 detail and 2 groups.
The detail contains data per year, the first group is the card which sums
all years, and the second the family of the card.
Year Objective Realized
2006 100
2007 200
2008 0
TOTAL CARD 1 1000 300
TOTAL FAMILY A 3000 (!!) 300
But the "objective" column isn't a detail data. I would like to return TOTAL
FAMILY A = 1000, but it returns 3000 (it sums 1000 for each year).
I try to use scope for the sum objective of family, but it doesn't seems to
work fine...
If someone can help me...
Thanks,
Drix.Since you want 1000 it sounds like you dont want Sum(). You want
First() or Max() or Min().|||No, if I take my example, the 1000 isn't linked to a year, but to a card.
There is nothing on the year lines for objective, the value is on the line
card, and the sum in the family line.
So, I would like to sum all the objectives cards for the family.
"FatOaf" wrote:
> Since you want 1000 it sounds like you dont want Sum(). You want
> First() or Max() or Min().
>|||I understand what you want now.
Is your resultset returning a row per year and then grouping on card
and family?
and the objective is repeated for each year in the same card?
I dont think you can do what you are asking with that resultset since
the aggregate functions operate on the underlying rows in the resultset
(years) - the scoping just determines which years are included in the
aggregate.
Maybe you can try altering the resultset so each year contains only the
average objective per card or only the first year in a card contains
the objective. Then Sum() would work at any level. The SQL to do this
would be a bit hairy but possible.
Another approach would be to use UNION to have 2 row types in the
resultset - one for years and one for cards. Then display cards from
the second row type instead of by grouping.