Running Totals

Hi,
Any ideas for getting the running total in reporting services.
Group Amt Detail(lines)
---
A 1000
1
2
B 2000
3
4
--
Total 3000
I need to do totals on report. Any ideas how to get the Totals = 3000 ?
like in crystal report we have the running totals formula which we can get
3000.
I know how to do in the SP or creating the subreport with distinct sql
statement. But I need help in creating running totals on the reporting
services side.
ThanksHi,
Use Sum(Fields!Amt.value) in the group footer.
Jasvinder
"sarath" wrote:
> Hi,
> Any ideas for getting the running total in reporting services.
> Group Amt Detail(lines)
> ---
> A 1000
> 1
> 2
> B 2000
> 3
> 4
> --
> Total 3000
> I need to do totals on report. Any ideas how to get the Totals = 3000 ?
> like in crystal report we have the running totals formula which we can get
> 3000.
> I know how to do in the SP or creating the subreport with distinct sql
> statement. But I need help in creating running totals on the reporting
> services side.
> Thanks
>
>
>
>|||HI,
If I do -- Sum(Fields!Amt.value) I will be getting 6000. Let me put the
question in this way.
Group Amt Detail(lines)
---
A
1000 1
1000 2
B
2000 3
2000 4
--
Total 6000 -- I need to get 3000 ? Can I know how we can get this ?
Thanks
"Jasvinder" wrote:
> Hi,
> Use Sum(Fields!Amt.value) in the group footer.
> Jasvinder
> "sarath" wrote:
> > Hi,
> >
> > Any ideas for getting the running total in reporting services.
> >
> > Group Amt Detail(lines)
> > ---
> > A 1000
> > 1
> > 2
> >
> > B 2000
> > 3
> > 4
> > --
> > Total 3000
> >
> > I need to do totals on report. Any ideas how to get the Totals = 3000 ?
> > like in crystal report we have the running totals formula which we can get
> > 3000.
> >
> > I know how to do in the SP or creating the subreport with distinct sql
> > statement. But I need help in creating running totals on the reporting
> > services side.
> >
> > Thanks
> >
> >
> >
> >
> >
> >
> >
> >

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