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

Running total for Cell Reference in SQL Reports

Hi,

I'm trying to do running total of the calculated field. Here is the detail of my RDL:

In a table, I have "textbox34" with the formula =count(Fields!ADF_NO.Value) in it and "textbox30" with the formula =first(Fields!TOTSTKS2.Value, "ProdServSales") - reportitems!TOTSTKS.value in it. I have divided "textbox 34" with "textbox30", and placed the results in "textbox36". Now, I want to do a cummulative total (Running total) of "textbox36", using the formula =runningvalue(reportitems!textbox36.Value, sum, nothing) in "textbox14". Its giving me an error saying:

"[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox14' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers."

Any suggestions? I appreciate your help on this.

Thanks

Perm

Hello Perm,

Could you try to remove the ReportItems from your RunningValue calculation? Something like this, but you'll have to take out the 'reportitems!TOTSTKS.value' and replace it with the field it's pulling from.

=runningvalue(count(Fields!ADF_NO.Value) / (first(Fields!TOTSTKS2.Value, "ProdServSales") - reportitems!TOTSTKS.value)), sum, nothing)

Jarret

|||

Hi Jarret,

Thanks for your reply.

Value in "reportitems!TOTSTKS.value" is also a runningvalue. It is: runningvalue(Fields!PSR_NO.Value, countdistinct, nothing)

When I try putting this in my query, I get an error saying that "An aggregate function cannot be nested inside another aggregate function".

Is it possible in VBasic...through Report Properties--> Code? Because, all I'm trying to do is to do a cummulative total (RunningTotal) of textbox36.

I appreciate your help on this.

Perm

Running sum

I'm not quite sure if i'm aiming for the impossible, but:
In a matrix, i have time (weeks) as columns, items as rows and qty as detail
and i would like to have a running sum for the columns.
This problems arise because i need to columns to be dynamic.
Any suggestions would be more than welcome
Example:
Records:
Item 1, week 1, qty 5
Item 1,week 2, qty 3
Item 1, week 3, qty 4
Matrix
week 1 week 2 week 3
Item 1 5 8 12I seem to have found the solution (RunningValue(Expression, Function,
Scope)).
Sorry for the inconvenience
"Claus Aaberg" <caa@.phaseone.com> wrote in message
news:%23TPAKwKcEHA.368@.TK2MSFTNGP10.phx.gbl...
> I'm not quite sure if i'm aiming for the impossible, but:
> In a matrix, i have time (weeks) as columns, items as rows and qty as
detail
> and i would like to have a running sum for the columns.
> This problems arise because i need to columns to be dynamic.
> Any suggestions would be more than welcome
> Example:
> Records:
> Item 1, week 1, qty 5
> Item 1,week 2, qty 3
> Item 1, week 3, qty 4
> Matrix
> week 1 week 2 week 3
> Item 1 5 8 12
>sql