calculating Grand Total in table footer from Group Totals

Hi All,
I have a report with one group. In the group footer
there is a TextBox ( name: txtTotal) that displays a total dollar
amount for that group. I would like to show a Grand Total dollar amount
in the table footer of the report. So
the Grand Total dollar amount would ideally be a sum of the group
footer
dollar amount.
In the table footer I tried to calculate the grand total in following
way:
=Sum (ReportItems!txtTotal.Value)
but I had following errors:
1. Aggregate functions can be used only on report items contained in
page
headers and footers.
2. Report item expresssions can only refer to other report items within
the
same grouping scope or a containing grouping scope.
Can anybody know how to solve this...
Any help is appreciated.
Thanks,
Munny.You are thinking in terms of the summing the control. You want to be summing
the dataset. The best thing to do in this case is do not create the
expression by hand but use the expression builder.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"munnyAnu" <dkanumolu@.gmail.com> wrote in message
news:1166455738.086451.247430@.f1g2000cwa.googlegroups.com...
> Hi All,
> I have a report with one group. In the group footer
> there is a TextBox ( name: txtTotal) that displays a total dollar
> amount for that group. I would like to show a Grand Total dollar amount
> in the table footer of the report. So
> the Grand Total dollar amount would ideally be a sum of the group
> footer
> dollar amount.
> In the table footer I tried to calculate the grand total in following
> way:
> =Sum (ReportItems!txtTotal.Value)
> but I had following errors:
> 1. Aggregate functions can be used only on report items contained in
> page
> headers and footers.
> 2. Report item expresssions can only refer to other report items within
> the
> same grouping scope or a containing grouping scope.
> Can anybody know how to solve this...
> Any help is appreciated.
>
> Thanks,
> Munny.
>|||Thanks Bruce...
But I am not clear abount using expression builder instead of wrinting
expression.
Can you please give me detailed info.
Bruce L-C [MVP] wrote:
> You are thinking in terms of the summing the control. You want to be summing
> the dataset. The best thing to do in this case is do not create the
> expression by hand but use the expression builder.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "munnyAnu" <dkanumolu@.gmail.com> wrote in message
> news:1166455738.086451.247430@.f1g2000cwa.googlegroups.com...
> > Hi All,
> >
> > I have a report with one group. In the group footer
> > there is a TextBox ( name: txtTotal) that displays a total dollar
> > amount for that group. I would like to show a Grand Total dollar amount
> > in the table footer of the report. So
> > the Grand Total dollar amount would ideally be a sum of the group
> > footer
> > dollar amount.
> >
> > In the table footer I tried to calculate the grand total in following
> > way:
> > =Sum (ReportItems!txtTotal.Value)
> >
> > but I had following errors:
> >
> > 1. Aggregate functions can be used only on report items contained in
> > page
> > headers and footers.
> >
> > 2. Report item expresssions can only refer to other report items within
> > the
> > same grouping scope or a containing grouping scope.
> >
> > Can anybody know how to solve this...
> >
> > Any help is appreciated.
> >
> >
> > Thanks,
> > Munny.
> >|||When you do a right mouse click, expression it brings up the expression
editor. Depending on whether you use RS 2000 or 2005 it is a little
different. What you want should look like this:
=sum(Fields!<your fieldname here>.Value)
Note that the above will work regardless of whether it is in a group footer
or table footer. It provides the sum based on the scope of the grouping.
Also note that it is not the name of the control, it is referencing the
fieldname of the dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"munnyAnu" <dkanumolu@.gmail.com> wrote in message
news:1166458481.696297.200910@.73g2000cwn.googlegroups.com...
> Thanks Bruce...
> But I am not clear abount using expression builder instead of wrinting
> expression.
> Can you please give me detailed info.
> Bruce L-C [MVP] wrote:
>> You are thinking in terms of the summing the control. You want to be
>> summing
>> the dataset. The best thing to do in this case is do not create the
>> expression by hand but use the expression builder.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "munnyAnu" <dkanumolu@.gmail.com> wrote in message
>> news:1166455738.086451.247430@.f1g2000cwa.googlegroups.com...
>> > Hi All,
>> >
>> > I have a report with one group. In the group footer
>> > there is a TextBox ( name: txtTotal) that displays a total dollar
>> > amount for that group. I would like to show a Grand Total dollar amount
>> > in the table footer of the report. So
>> > the Grand Total dollar amount would ideally be a sum of the group
>> > footer
>> > dollar amount.
>> >
>> > In the table footer I tried to calculate the grand total in following
>> > way:
>> > =Sum (ReportItems!txtTotal.Value)
>> >
>> > but I had following errors:
>> >
>> > 1. Aggregate functions can be used only on report items contained in
>> > page
>> > headers and footers.
>> >
>> > 2. Report item expresssions can only refer to other report items within
>> > the
>> > same grouping scope or a containing grouping scope.
>> >
>> > Can anybody know how to solve this...
>> >
>> > Any help is appreciated.
>> >
>> >
>> > Thanks,
>> > Munny.
>> >
>|||Bruce,
Thanks for your clarification.
I tried this. Bur the problem is I am getting sum of that filed value
from all records.
If I have a 5 records for Batch_1 with same total value,I dont want get
all records total.
I am displaying that "Field!total.Value" in group header.I want to get
this group header value.
Likewise if I have 3 Batches_1,2,3 with values (Field!total.value)
1200,500,100 then I want show the total of these three values (1800) in
table footer.
if you know how to solve this please let me know.
Thanks,
Muny|||The table footer should be all the records isn't it?
Why wouldn't it be the sum of the table value?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"munnyAnu" <dkanumolu@.gmail.com> wrote in message
news:1166474761.054878.176140@.n67g2000cwd.googlegroups.com...
> Bruce,
> Thanks for your clarification.
> I tried this. Bur the problem is I am getting sum of that filed value
> from all records.
> If I have a 5 records for Batch_1 with same total value,I dont want get
> all records total.
> I am displaying that "Field!total.Value" in group header.I want to get
> this group header value.
> Likewise if I have 3 Batches_1,2,3 with values (Field!total.value)
> 1200,500,100 then I want show the total of these three values (1800) in
> table footer.
> if you know how to solve this please let me know.
> Thanks,
> Muny
>