Constant Errors on Simple Sums... Why?

There seems to be a concept I'm not grasping. I don't understand why
I can't get a simple sum...
I have a singe table report based on a single dataset and all I want
to do is summarize some financials at 4 group levels.
I get both of these errors for every Sum expression in a report:
"A value expression used for the report parameter
'=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
function. Aggregate functions cannot be used in report parameter
expressions."
"The field expression for the data set ?DatasetName' 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."
I have only 2 parameters in the report, fiscalyear(int) and
fiscalperiod(int).
Half of my Sums aggregate YTD figures that do not reference either of
the parameters.
The other half aggregate MTD figures that are dependent on the
fiscalperiod parameter.
I've tried putting the expression directly into the appropriate text
field, and I have tried making the Sum expressions their own fields
and dropping those fields into the table. Nothing works.
Can anyone explain?
Thanks,
JodyHave you added groups to the table on the form? You need groups. Go to the
footer for the group. Use the expression builder to put the appropriate
values. If you are approaching it correctly it should be very straight
forward.
HTH,
Bruce L-C
"JodyT" <datagal@.msn.com> wrote in message
news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> There seems to be a concept I'm not grasping. I don't understand why
> I can't get a simple sum...
> I have a singe table report based on a single dataset and all I want
> to do is summarize some financials at 4 group levels.
> I get both of these errors for every Sum expression in a report:
> "A value expression used for the report parameter
> '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> function. Aggregate functions cannot be used in report parameter
> expressions."
> "The field expression for the data set 'DatasetName' 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."
> I have only 2 parameters in the report, fiscalyear(int) and
> fiscalperiod(int).
> Half of my Sums aggregate YTD figures that do not reference either of
> the parameters.
> The other half aggregate MTD figures that are dependent on the
> fiscalperiod parameter.
> I've tried putting the expression directly into the appropriate text
> field, and I have tried making the Sum expressions their own fields
> and dropping those fields into the table. Nothing works.
> Can anyone explain?
> Thanks,
> Jody|||All of the groups are there..
After some experimentation, I found that you don't actually have to
specify the scope in an aggregate in a table, and that helped.
Another part of the problem is that I'm getting inconsistent results
from my Preview pane and from the Debug preview window. The Debug is
generally right and the Preview if often wrong, even after I do a
rebuild.
Things are going better, but still far from what I had hoped for.
Thanks,
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<#nIqvFJhEHA.644@.tk2msftngp13.phx.gbl>...
> Have you added groups to the table on the form? You need groups. Go to the
> footer for the group. Use the expression builder to put the appropriate
> values. If you are approaching it correctly it should be very straight
> forward.
> HTH,
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> > There seems to be a concept I'm not grasping. I don't understand why
> > I can't get a simple sum...
> >
> > I have a singe table report based on a single dataset and all I want
> > to do is summarize some financials at 4 group levels.
> > I get both of these errors for every Sum expression in a report:
> >
> > "A value expression used for the report parameter
> > '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> > function. Aggregate functions cannot be used in report parameter
> > expressions."
> >
> > "The field expression for the data set 'DatasetName' 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."
> >
> > I have only 2 parameters in the report, fiscalyear(int) and
> > fiscalperiod(int).
> > Half of my Sums aggregate YTD figures that do not reference either of
> > the parameters.
> > The other half aggregate MTD figures that are dependent on the
> > fiscalperiod parameter.
> >
> > I've tried putting the expression directly into the appropriate text
> > field, and I have tried making the Sum expressions their own fields
> > and dropping those fields into the table. Nothing works.
> >
> > Can anyone explain?
> >
> > Thanks,
> > Jody|||The groups are all there.
After some monkeying around I discovered that you shouldn't specify
the scope for an aggregate in a table. Tha helped.
Another problem is that I get different results on the VS Preview Pane
and the Debug preview window. The window is right, but the pane if
often wrong, even with a rebuild
Thanks,
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<#nIqvFJhEHA.644@.tk2msftngp13.phx.gbl>...
> Have you added groups to the table on the form? You need groups. Go to the
> footer for the group. Use the expression builder to put the appropriate
> values. If you are approaching it correctly it should be very straight
> forward.
> HTH,
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> > There seems to be a concept I'm not grasping. I don't understand why
> > I can't get a simple sum...
> >
> > I have a singe table report based on a single dataset and all I want
> > to do is summarize some financials at 4 group levels.
> > I get both of these errors for every Sum expression in a report:
> >
> > "A value expression used for the report parameter
> > '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> > function. Aggregate functions cannot be used in report parameter
> > expressions."
> >
> > "The field expression for the data set 'DatasetName' 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."
> >
> > I have only 2 parameters in the report, fiscalyear(int) and
> > fiscalperiod(int).
> > Half of my Sums aggregate YTD figures that do not reference either of
> > the parameters.
> > The other half aggregate MTD figures that are dependent on the
> > fiscalperiod parameter.
> >
> > I've tried putting the expression directly into the appropriate text
> > field, and I have tried making the Sum expressions their own fields
> > and dropping those fields into the table. Nothing works.
> >
> > Can anyone explain?
> >
> > Thanks,
> > Jody|||The groups are all there.
After some monkeying around I discovered that you shouldn't specify
the scope for an aggregate in a table. That helped.
Another problem is that I get different results on the VS Preview Pane
and the Debug preview window. The window is right, but the pane if
often wrong, even with a rebuild
Thanks,
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<#nIqvFJhEHA.644@.tk2msftngp13.phx.gbl>...
> Have you added groups to the table on the form? You need groups. Go to the
> footer for the group. Use the expression builder to put the appropriate
> values. If you are approaching it correctly it should be very straight
> forward.
> HTH,
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408171120.48b2b30b@.posting.google.com...
> > There seems to be a concept I'm not grasping. I don't understand why
> > I can't get a simple sum...
> >
> > I have a singe table report based on a single dataset and all I want
> > to do is summarize some financials at 4 group levels.
> > I get both of these errors for every Sum expression in a report:
> >
> > "A value expression used for the report parameter
> > '=Sum(Fields!Number.Value ,"GroupName")' includes an aggregate
> > function. Aggregate functions cannot be used in report parameter
> > expressions."
> >
> > "The field expression for the data set 'DatasetName' 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."
> >
> > I have only 2 parameters in the report, fiscalyear(int) and
> > fiscalperiod(int).
> > Half of my Sums aggregate YTD figures that do not reference either of
> > the parameters.
> > The other half aggregate MTD figures that are dependent on the
> > fiscalperiod parameter.
> >
> > I've tried putting the expression directly into the appropriate text
> > field, and I have tried making the Sum expressions their own fields
> > and dropping those fields into the table. Nothing works.
> >
> > Can anyone explain?
> >
> > Thanks,
> > Jody