How to evaluate sum on change of group?

I have a data set that is grouped based on 2 fields, but the value of the set
that I want to add by Group 1 is the same data that repeats for the first
group.
Example:
Value Grp1 Grp2
================= 100 1 1
100 1 2
100 1 3
200 2 1
200 2 2
200 2 3
I want to get a total of Value, but only evaluate the total when Grp1
changes. Currently, when I use the Sum function, it adds all values, giving
me a total of 900. I need a way to take the value of '100' when Grp1 = 100,
and add it to the value of '200' when Grp1 = 2 to give me a total of 300.
In Crystal Reports, there was a method to evaluate a sum only on the change
of a particular group. Is there some similar method in Reporting Services to
achieve this?It is available in SSRS as well but you need to try it and see how far you
can use this solutions. it goes like this.
= RunningValue(Fields!field1.Value, Sum, <groupname>) so it evaluates to tat
particular group or the scope.
Amarnath
"Ben Shaffer" wrote:
> I have a data set that is grouped based on 2 fields, but the value of the set
> that I want to add by Group 1 is the same data that repeats for the first
> group.
> Example:
> Value Grp1 Grp2
> =================> 100 1 1
> 100 1 2
> 100 1 3
> 200 2 1
> 200 2 2
> 200 2 3
>
> I want to get a total of Value, but only evaluate the total when Grp1
> changes. Currently, when I use the Sum function, it adds all values, giving
> me a total of 900. I need a way to take the value of '100' when Grp1 = 100,
> and add it to the value of '200' when Grp1 = 2 to give me a total of 300.
> In Crystal Reports, there was a method to evaluate a sum only on the change
> of a particular group. Is there some similar method in Reporting Services to
> achieve this?
>|||Ben,
You could also use grouping on the report. You could put a group sum in
the group header or footer, and then a grand total or a count of the
groups in the table footer.
When you use a normal sum function in a group, it sums only the group.
-Josh
Ben Shaffer wrote:
> I have a data set that is grouped based on 2 fields, but the value of the set
> that I want to add by Group 1 is the same data that repeats for the first
> group.
> Example:
> Value Grp1 Grp2
> =================> 100 1 1
> 100 1 2
> 100 1 3
> 200 2 1
> 200 2 2
> 200 2 3
>
> I want to get a total of Value, but only evaluate the total when Grp1
> changes. Currently, when I use the Sum function, it adds all values, giving
> me a total of 900. I need a way to take the value of '100' when Grp1 = 100,
> and add it to the value of '200' when Grp1 = 2 to give me a total of 300.
> In Crystal Reports, there was a method to evaluate a sum only on the change
> of a particular group. Is there some similar method in Reporting Services to
> achieve this?|||Thanks for the reply. I've tried using the scope parameter of the aggregate
function, but get report compilation errors when I do so. What you've
suggested with the "RunningValue" function is essentially the same as using
the Sum function.
I oversimplified my data set to really show my problem. Here's a slightly
different version:
field1 Grp1 Grp2
=================100 1 1
100 1 1
100 1 1
100 1 2
100 1 3
On the footer for group 2, I can just show the most recent value of field1.
i.e. when grp2 changes, I just display =Fields!field1.Value instead of a sum
to get the value that I need to display.
However, when I try to create a Sum of the 1st field on the footer for group
1, I get the total of all values of field1.
i.e. =Sum(Fields!field1.Value) on the footer for group 1 yields a value of
500. What I want to get is for the sum to evaluate only when group 2
changes, to yield a value of 300.
I have tried using the scope parameter to the sum function to do this.
i.e. =Sum(Fields!field1.Value, "Grp2")
When I try this, I get a report compile error telling me that:
"The value expression for textbox 'x' has a scope parameter that is not
valid for an aggregate function. The scope parameter must be set to a string
constant that is equal to either the name of a containing group, the name of
a containing data region, or the name of a data set."
What I understand of the error message is that I can't set the scope of the
sum function to be based on a group that group 1 contains. That it has to be
set to a group that contains group 1 instead.
Am I using scope incorrectly? If this is the way that scope functions, then
the "scope" of the aggregate function must control when the running total
resets to zero, rather than controlling when the running total gets evaluated
(which is the functionality that I *need*).
It doesn't make sense that I wouldn't have this capability with MS Reporting
Services, as lesser reporting tools (such as Crystal and R&R) all provided
this kind of functionality with running totals in reports.
Further suggestions would be hugely appreciated.
"Amarnath" wrote:
> It is available in SSRS as well but you need to try it and see how far you
> can use this solutions. it goes like this.
> = RunningValue(Fields!field1.Value, Sum, <groupname>) so it evaluates to tat
> particular group or the scope.
> Amarnath
> "Ben Shaffer" wrote:
> > I have a data set that is grouped based on 2 fields, but the value of the set
> > that I want to add by Group 1 is the same data that repeats for the first
> > group.
> > Example:
> > Value Grp1 Grp2
> > =================> > 100 1 1
> > 100 1 2
> > 100 1 3
> > 200 2 1
> > 200 2 2
> > 200 2 3
> >
> >
> > I want to get a total of Value, but only evaluate the total when Grp1
> > changes. Currently, when I use the Sum function, it adds all values, giving
> > me a total of 900. I need a way to take the value of '100' when Grp1 = 100,
> > and add it to the value of '200' when Grp1 = 2 to give me a total of 300.
> >
> > In Crystal Reports, there was a method to evaluate a sum only on the change
> > of a particular group. Is there some similar method in Reporting Services to
> > achieve this?
> >|||Thanks for your reply, but I've already done this. I've better explained my
situation in a reply to Amarnath above. Further help in regard to that post
would be greatly appreciated. Thanks :)
"Josh" wrote:
> Ben,
> You could also use grouping on the report. You could put a group sum in
> the group header or footer, and then a grand total or a count of the
> groups in the table footer.
> When you use a normal sum function in a group, it sums only the group.
> -Josh|||This might be a dumb question, but I have to ask...
You said:
However, when I try to create a Sum of the 1st field on the footer for
group
1, I get the total of all values of field1.
i.e. =Sum(Fields!field1.Value) on the footer for group 1 yields a value
of
500. What I want to get is for the sum to evaluate only when group 2
changes, to yield a value of 300.
You are trying to sum Group 2 to get a sum of 300, but you are summing
in the footer of group 1 and getting 500. Can't you just sum in the
group 2 footer? That would give you 300, and you would only get the sum
(group footer) every time group 2 changes...
-Josh
Ben Shaffer wrote:
> Thanks for the reply. I've tried using the scope parameter of the aggregate
> function, but get report compilation errors when I do so. What you've
> suggested with the "RunningValue" function is essentially the same as using
> the Sum function.
> I oversimplified my data set to really show my problem. Here's a slightly
> different version:
> field1 Grp1 Grp2
> =================> 100 1 1
> 100 1 1
> 100 1 1
> 100 1 2
> 100 1 3
> On the footer for group 2, I can just show the most recent value of field1.
> i.e. when grp2 changes, I just display =Fields!field1.Value instead of a sum
> to get the value that I need to display.
> However, when I try to create a Sum of the 1st field on the footer for group
> 1, I get the total of all values of field1.
> i.e. =Sum(Fields!field1.Value) on the footer for group 1 yields a value of
> 500. What I want to get is for the sum to evaluate only when group 2
> changes, to yield a value of 300.
> I have tried using the scope parameter to the sum function to do this.
> i.e. =Sum(Fields!field1.Value, "Grp2")
> When I try this, I get a report compile error telling me that:
> "The value expression for textbox 'x' has a scope parameter that is not
> valid for an aggregate function. The scope parameter must be set to a string
> constant that is equal to either the name of a containing group, the name of
> a containing data region, or the name of a data set."
> What I understand of the error message is that I can't set the scope of the
> sum function to be based on a group that group 1 contains. That it has to be
> set to a group that contains group 1 instead.
> Am I using scope incorrectly? If this is the way that scope functions, then
> the "scope" of the aggregate function must control when the running total
> resets to zero, rather than controlling when the running total gets evaluated
> (which is the functionality that I *need*).
> It doesn't make sense that I wouldn't have this capability with MS Reporting
> Services, as lesser reporting tools (such as Crystal and R&R) all provided
> this kind of functionality with running totals in reports.
> Further suggestions would be hugely appreciated.
> "Amarnath" wrote:
> > It is available in SSRS as well but you need to try it and see how far you
> > can use this solutions. it goes like this.
> >
> > = RunningValue(Fields!field1.Value, Sum, <groupname>) so it evaluates to tat
> > particular group or the scope.
> >
> > Amarnath
> >
> > "Ben Shaffer" wrote:
> >
> > > I have a data set that is grouped based on 2 fields, but the value of the set
> > > that I want to add by Group 1 is the same data that repeats for the first
> > > group.
> > > Example:
> > > Value Grp1 Grp2
> > > =================> > > 100 1 1
> > > 100 1 2
> > > 100 1 3
> > > 200 2 1
> > > 200 2 2
> > > 200 2 3
> > >
> > >
> > > I want to get a total of Value, but only evaluate the total when Grp1
> > > changes. Currently, when I use the Sum function, it adds all values, giving
> > > me a total of 900. I need a way to take the value of '100' when Grp1 = 100,
> > > and add it to the value of '200' when Grp1 = 2 to give me a total of 300.
> > >
> > > In Crystal Reports, there was a method to evaluate a sum only on the change
> > > of a particular group. Is there some similar method in Reporting Services to
> > > achieve this?
> > >