Running Totals

Hi there - I hope someone can help me with this problem :
Suppose I have the following info :
Group Amt Detail
---
A
1000 1
1000 2
1000 3
B
2000 4
2000 5
2000 6
---
I can get aggregates at group level by using First(Fields!Amt.value)
and Sum(Fields!Detail.value) in the group footer.
But if I want an overall sum of Amt at the table footer, then
Sum(Fields!Amt.value) will give me a value of 9000 rather than the 3000
I want.
How do I get 3000?
Many thanks in advance for any help.
SteveB.stevebuy@.eircom.net wrote in news:1128354813.445351.92670
@.g49g2000cwa.googlegroups.com:
What does you dataset look like, and where do the 1000 and 2000 values
come from?
> Hi there - I hope someone can help me with this problem :
> Suppose I have the following info :
> Group Amt Detail
> ---
> A
> 1000 1
> 1000 2
> 1000 3
> B
> 2000 4
> 2000 5
> 2000 6
> ---
> I can get aggregates at group level by using First(Fields!Amt.value)
> and Sum(Fields!Detail.value) in the group footer.
> But if I want an overall sum of Amt at the table footer, then
> Sum(Fields!Amt.value) will give me a value of 9000 rather than the 3000
> I want.
> How do I get 3000?
> Many thanks in advance for any help.
> SteveB.
>|||When you use aggregate functions you can specify a scope. Let's say that the
name of the group you have is "table1_Group1".
So, if you want to get subtotals by groups, you add something like this in
the group footer:
=Sum(Fields!Amt.Value, "table1_Group1")
I hope this helps|||If I am understanding you correctly, the value for Amt is related to
Group and not to Detail. If that is the case, move the Amt text box to
the Group header level. It will only show up once and should sum
correctly.
hth,
sturgis|||Thanks sturgis for the reply. Thats my problem, I have already moved
both columns to the group header and deleted the detail field.
The group header has First(Fields!Amt.value) and
Sum(Fields!Detail.value)
giving me
A 1000 6
B 2000 15
The overall footer, computes : 9000 21
I want : 3000 21
Sum(First(Fields!Amt.value)) returns an error.
Thanks for the reply Asher_N.
The underlying dataset is the result of an inner join between 2 (1:N)
tables and thats why I get repetition on the Amt field