How to sum a group footer aggregate without summing the detail fie

I have a report query that has a table called invoice that is right outer
joined (one to many) to a table called invoice_line. The invoice table
contains a field called freight_amount and the invoice_line table contains a
field called line_amount.
I am using this query in a data set in set linked to a report table object
in a Visual Studio 2003 report project for SQL Reporting Services 2000. The
detail section of the report table shows the information for invoice_line
including the invoice_line.line_amount field. There is a group section that
groups on and shows the invoice information including the
invoice.freight_amount field.
In the invoice group footer section, there is an aggregate field that uses
the First aggregate function to return the first value for the
invoice.freight_amount field from the details section. I did not use the Sum
aggregate function in the invoice group footer because I do not want to sum
the invoice.freight_amount field since this is invoice level information.
Next I need to sum this group footer invoice.freight_amount field at the
table footer level to get a total of the invoice.freight_amount for the
report. If I use the Sum aggregate function on the freight_amount field, I
get the sum of the freight_amount field values on each detail line and I only
want to sum the invoice.freight_amount field once for each invoice group so
that I don't overstate this amount for invoices that have more than one
invoice_line.
How can this be done? Any help you can provide is greatly appreciated. If
any additional information is required, please let me know and I will provide
it and thanks in advance for your help.This thread:
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=08b35bf2-fa87-4821-970b-cf490aad6b9a&sloc=en-us
Was heading towards solving my issue, but didn't quite get there in the end.|||Randy,
Did you set the Scope of your Sum to the invoice group? It would look
something like this =Sum(Fields!InvoiceFreighAmt.Value,
"NameOfTableGroupForInvoice")
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Randy Bristol" <RandyBristol@.discussions.microsoft.com> wrote in message
news:39675BE6-8A5F-4974-B3F8-BC5D6E3C8236@.microsoft.com...
>I have a report query that has a table called invoice that is right outer
> joined (one to many) to a table called invoice_line. The invoice table
> contains a field called freight_amount and the invoice_line table contains
> a
> field called line_amount.
> I am using this query in a data set in set linked to a report table object
> in a Visual Studio 2003 report project for SQL Reporting Services 2000.
> The
> detail section of the report table shows the information for invoice_line
> including the invoice_line.line_amount field. There is a group section
> that
> groups on and shows the invoice information including the
> invoice.freight_amount field.
> In the invoice group footer section, there is an aggregate field that uses
> the First aggregate function to return the first value for the
> invoice.freight_amount field from the details section. I did not use the
> Sum
> aggregate function in the invoice group footer because I do not want to
> sum
> the invoice.freight_amount field since this is invoice level information.
> Next I need to sum this group footer invoice.freight_amount field at the
> table footer level to get a total of the invoice.freight_amount for the
> report. If I use the Sum aggregate function on the freight_amount field,
> I
> get the sum of the freight_amount field values on each detail line and I
> only
> want to sum the invoice.freight_amount field once for each invoice group
> so
> that I don't overstate this amount for invoices that have more than one
> invoice_line.
> How can this be done? Any help you can provide is greatly appreciated.
> If
> any additional information is required, please let me know and I will
> provide
> it and thanks in advance for your help.|||My group is called Invoice_Group, the field that I am trying to sum is called
invoice_freightamount, and the table footer textbox that I want to contain
the sum is called total_freight_amount.
I tried each of the following two expressions in the total_freight_amount
textbox because I didn't know if the quotes were recquired around the scope:
=Sum(Fields!invoice_freightamount.Value,Invoice_Group)
=Sum(Fields!invoice_freightamount.Value,"Invoice_Group")
In both cases I got the following error:
The value expression for the textbox â'total_freight_amountâ' 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.
Can you please tell me what I am doing wrong?
"Andy Potter" wrote:
> Randy,
> Did you set the Scope of your Sum to the invoice group? It would look
> something like this =Sum(Fields!InvoiceFreighAmt.Value,
> "NameOfTableGroupForInvoice")
> --
> Andy Potter
> blog : http://sqlreportingservices.spaces.live.com
> info@.(NOSPAM)lakeclaireenterprises.com
> "Randy Bristol" <RandyBristol@.discussions.microsoft.com> wrote in message
> news:39675BE6-8A5F-4974-B3F8-BC5D6E3C8236@.microsoft.com...
> >I have a report query that has a table called invoice that is right outer
> > joined (one to many) to a table called invoice_line. The invoice table
> > contains a field called freight_amount and the invoice_line table contains
> > a
> > field called line_amount.
> >
> > I am using this query in a data set in set linked to a report table object
> > in a Visual Studio 2003 report project for SQL Reporting Services 2000.
> > The
> > detail section of the report table shows the information for invoice_line
> > including the invoice_line.line_amount field. There is a group section
> > that
> > groups on and shows the invoice information including the
> > invoice.freight_amount field.
> >
> > In the invoice group footer section, there is an aggregate field that uses
> > the First aggregate function to return the first value for the
> > invoice.freight_amount field from the details section. I did not use the
> > Sum
> > aggregate function in the invoice group footer because I do not want to
> > sum
> > the invoice.freight_amount field since this is invoice level information.
> >
> > Next I need to sum this group footer invoice.freight_amount field at the
> > table footer level to get a total of the invoice.freight_amount for the
> > report. If I use the Sum aggregate function on the freight_amount field,
> > I
> > get the sum of the freight_amount field values on each detail line and I
> > only
> > want to sum the invoice.freight_amount field once for each invoice group
> > so
> > that I don't overstate this amount for invoices that have more than one
> > invoice_line.
> >
> > How can this be done? Any help you can provide is greatly appreciated.
> > If
> > any additional information is required, please let me know and I will
> > provide
> > it and thanks in advance for your help.
>
>|||I think I understand now. You've got a group aggregation in the detail
records of your query. You display this in the footer for the table group
(using the "First" and want to come up with a report total using values of
the Group footer.
I think the simplest way to handle this would be to do it in your query.
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Randy Bristol" <RandyBristol@.discussions.microsoft.com> wrote in message
news:67339824-7CA2-4279-A081-F9776A9046DF@.microsoft.com...
> My group is called Invoice_Group, the field that I am trying to sum is
> called
> invoice_freightamount, and the table footer textbox that I want to contain
> the sum is called total_freight_amount.
> I tried each of the following two expressions in the total_freight_amount
> textbox because I didn't know if the quotes were recquired around the
> scope:
> =Sum(Fields!invoice_freightamount.Value,Invoice_Group)
> =Sum(Fields!invoice_freightamount.Value,"Invoice_Group")
> In both cases I got the following error:
> The value expression for the textbox 'total_freight_amount' 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.
> Can you please tell me what I am doing wrong?
> "Andy Potter" wrote:
>> Randy,
>> Did you set the Scope of your Sum to the invoice group? It would look
>> something like this =Sum(Fields!InvoiceFreighAmt.Value,
>> "NameOfTableGroupForInvoice")
>> --
>> Andy Potter
>> blog : http://sqlreportingservices.spaces.live.com
>> info@.(NOSPAM)lakeclaireenterprises.com
>> "Randy Bristol" <RandyBristol@.discussions.microsoft.com> wrote in message
>> news:39675BE6-8A5F-4974-B3F8-BC5D6E3C8236@.microsoft.com...
>> >I have a report query that has a table called invoice that is right
>> >outer
>> > joined (one to many) to a table called invoice_line. The invoice table
>> > contains a field called freight_amount and the invoice_line table
>> > contains
>> > a
>> > field called line_amount.
>> >
>> > I am using this query in a data set in set linked to a report table
>> > object
>> > in a Visual Studio 2003 report project for SQL Reporting Services 2000.
>> > The
>> > detail section of the report table shows the information for
>> > invoice_line
>> > including the invoice_line.line_amount field. There is a group section
>> > that
>> > groups on and shows the invoice information including the
>> > invoice.freight_amount field.
>> >
>> > In the invoice group footer section, there is an aggregate field that
>> > uses
>> > the First aggregate function to return the first value for the
>> > invoice.freight_amount field from the details section. I did not use
>> > the
>> > Sum
>> > aggregate function in the invoice group footer because I do not want to
>> > sum
>> > the invoice.freight_amount field since this is invoice level
>> > information.
>> >
>> > Next I need to sum this group footer invoice.freight_amount field at
>> > the
>> > table footer level to get a total of the invoice.freight_amount for the
>> > report. If I use the Sum aggregate function on the freight_amount
>> > field,
>> > I
>> > get the sum of the freight_amount field values on each detail line and
>> > I
>> > only
>> > want to sum the invoice.freight_amount field once for each invoice
>> > group
>> > so
>> > that I don't overstate this amount for invoices that have more than one
>> > invoice_line.
>> >
>> > How can this be done? Any help you can provide is greatly appreciated.
>> > If
>> > any additional information is required, please let me know and I will
>> > provide
>> > it and thanks in advance for your help.
>>|||Thanks for the reply, Andy. Do you know for sure that this cannot be done in
the report writer? Does anyone else know if this can be done in the report
writer?
"Andy Potter" wrote:
> I think I understand now. You've got a group aggregation in the detail
> records of your query. You display this in the footer for the table group
> (using the "First" and want to come up with a report total using values of
> the Group footer.
> I think the simplest way to handle this would be to do it in your query.
> --
> Andy Potter
> blog : http://sqlreportingservices.spaces.live.com
> info@.(NOSPAM)lakeclaireenterprises.com
> "Randy Bristol" <RandyBristol@.discussions.microsoft.com> wrote in message
> news:67339824-7CA2-4279-A081-F9776A9046DF@.microsoft.com...
> > My group is called Invoice_Group, the field that I am trying to sum is
> > called
> > invoice_freightamount, and the table footer textbox that I want to contain
> > the sum is called total_freight_amount.
> >
> > I tried each of the following two expressions in the total_freight_amount
> > textbox because I didn't know if the quotes were recquired around the
> > scope:
> >
> > =Sum(Fields!invoice_freightamount.Value,Invoice_Group)
> > =Sum(Fields!invoice_freightamount.Value,"Invoice_Group")
> >
> > In both cases I got the following error:
> >
> > The value expression for the textbox 'total_freight_amount' 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.
> >
> > Can you please tell me what I am doing wrong?
> >
> > "Andy Potter" wrote:
> >
> >> Randy,
> >>
> >> Did you set the Scope of your Sum to the invoice group? It would look
> >> something like this =Sum(Fields!InvoiceFreighAmt.Value,
> >> "NameOfTableGroupForInvoice")
> >>
> >> --
> >> Andy Potter
> >> blog : http://sqlreportingservices.spaces.live.com
> >> info@.(NOSPAM)lakeclaireenterprises.com
> >> "Randy Bristol" <RandyBristol@.discussions.microsoft.com> wrote in message
> >> news:39675BE6-8A5F-4974-B3F8-BC5D6E3C8236@.microsoft.com...
> >> >I have a report query that has a table called invoice that is right
> >> >outer
> >> > joined (one to many) to a table called invoice_line. The invoice table
> >> > contains a field called freight_amount and the invoice_line table
> >> > contains
> >> > a
> >> > field called line_amount.
> >> >
> >> > I am using this query in a data set in set linked to a report table
> >> > object
> >> > in a Visual Studio 2003 report project for SQL Reporting Services 2000.
> >> > The
> >> > detail section of the report table shows the information for
> >> > invoice_line
> >> > including the invoice_line.line_amount field. There is a group section
> >> > that
> >> > groups on and shows the invoice information including the
> >> > invoice.freight_amount field.
> >> >
> >> > In the invoice group footer section, there is an aggregate field that
> >> > uses
> >> > the First aggregate function to return the first value for the
> >> > invoice.freight_amount field from the details section. I did not use
> >> > the
> >> > Sum
> >> > aggregate function in the invoice group footer because I do not want to
> >> > sum
> >> > the invoice.freight_amount field since this is invoice level
> >> > information.
> >> >
> >> > Next I need to sum this group footer invoice.freight_amount field at
> >> > the
> >> > table footer level to get a total of the invoice.freight_amount for the
> >> > report. If I use the Sum aggregate function on the freight_amount
> >> > field,
> >> > I
> >> > get the sum of the freight_amount field values on each detail line and
> >> > I
> >> > only
> >> > want to sum the invoice.freight_amount field once for each invoice
> >> > group
> >> > so
> >> > that I don't overstate this amount for invoices that have more than one
> >> > invoice_line.
> >> >
> >> > How can this be done? Any help you can provide is greatly appreciated.
> >> > If
> >> > any additional information is required, please let me know and I will
> >> > provide
> >> > it and thanks in advance for your help.
> >>
> >>
> >>
>
>