How to create an Excel formula in a table footer?

Hello, I need some help with the generation of formulas when rendering to Excel.
Let's take a small table to explain the problem, with the columns quantity, price per article and total price. The last column is a calculated column which could look like:
=ReportItems!tbQuantity.value * ReportItems!tbPricePerArticle.value
So far no problems, this works fine and generates a correct formula in Excel. However, I also need a total value in the table footer, which gives me the total quantity and the total overall price. The values in the footer also need to be implemented as formulas, since the end user needs to get an Excel file which enables him to see the effects of changing the quantity and price per article.
How do I get this to work? On a table row basis this seems to work fine, but in the table footer there is no way that I can reference the calculated fields from the table row. I get a message that says that I can only reference report items inside the same grouping scope...
Any help is welcome, since this looks like a basic functionality that is either missing or needs to be implemented in a way that I haven't discovered yet.
Thanks,
VincentAggregate formulas are not currently implemented in the Excel renderer.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Vincent" <Vincent@.discussions.microsoft.com> wrote in message
news:2B8C24ED-0506-4297-9EBC-9DB8C43616B6@.microsoft.com...
> Hello, I need some help with the generation of formulas when rendering to
Excel.
> Let's take a small table to explain the problem, with the columns
quantity, price per article and total price. The last column is a calculated
column which could look like:
> =ReportItems!tbQuantity.value * ReportItems!tbPricePerArticle.value
> So far no problems, this works fine and generates a correct formula in
Excel. However, I also need a total value in the table footer, which gives
me the total quantity and the total overall price. The values in the footer
also need to be implemented as formulas, since the end user needs to get an
Excel file which enables him to see the effects of changing the quantity and
price per article.
> How do I get this to work? On a table row basis this seems to work fine,
but in the table footer there is no way that I can reference the calculated
fields from the table row. I get a message that says that I can only
reference report items inside the same grouping scope...
> Any help is welcome, since this looks like a basic functionality that is
either missing or needs to be implemented in a way that I haven't discovered
yet.
> Thanks,
> Vincent
>|||Is this planned for the next release or update (SP2)?
"Chris Hays [MSFT]" wrote:
> Aggregate formulas are not currently implemented in the Excel renderer.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Vincent" <Vincent@.discussions.microsoft.com> wrote in message
> news:2B8C24ED-0506-4297-9EBC-9DB8C43616B6@.microsoft.com...
> > Hello, I need some help with the generation of formulas when rendering to
> Excel.
> >
> > Let's take a small table to explain the problem, with the columns
> quantity, price per article and total price. The last column is a calculated
> column which could look like:
> > =ReportItems!tbQuantity.value * ReportItems!tbPricePerArticle.value
> >
> > So far no problems, this works fine and generates a correct formula in
> Excel. However, I also need a total value in the table footer, which gives
> me the total quantity and the total overall price. The values in the footer
> also need to be implemented as formulas, since the end user needs to get an
> Excel file which enables him to see the effects of changing the quantity and
> price per article.
> >
> > How do I get this to work? On a table row basis this seems to work fine,
> but in the table footer there is no way that I can reference the calculated
> fields from the table row. I get a message that says that I can only
> reference report items inside the same grouping scope...
> >
> > Any help is welcome, since this looks like a basic functionality that is
> either missing or needs to be implemented in a way that I haven't discovered
> yet.
> >
> > Thanks,
> > Vincent
> >
>
>|||No. It is on our wishlist for some future version, but there are no
immediate plans to implement it.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Vincent" <Vincent@.discussions.microsoft.com> wrote in message
news:4C49ABA5-825A-4758-AE88-5A00CD828D73@.microsoft.com...
> Is this planned for the next release or update (SP2)?
>
> "Chris Hays [MSFT]" wrote:
> > Aggregate formulas are not currently implemented in the Excel renderer.
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach of
> > children under 3.
> > "Vincent" <Vincent@.discussions.microsoft.com> wrote in message
> > news:2B8C24ED-0506-4297-9EBC-9DB8C43616B6@.microsoft.com...
> > > Hello, I need some help with the generation of formulas when rendering
to
> > Excel.
> > >
> > > Let's take a small table to explain the problem, with the columns
> > quantity, price per article and total price. The last column is a
calculated
> > column which could look like:
> > > =ReportItems!tbQuantity.value * ReportItems!tbPricePerArticle.value
> > >
> > > So far no problems, this works fine and generates a correct formula in
> > Excel. However, I also need a total value in the table footer, which
gives
> > me the total quantity and the total overall price. The values in the
footer
> > also need to be implemented as formulas, since the end user needs to get
an
> > Excel file which enables him to see the effects of changing the quantity
and
> > price per article.
> > >
> > > How do I get this to work? On a table row basis this seems to work
fine,
> > but in the table footer there is no way that I can reference the
calculated
> > fields from the table row. I get a message that says that I can only
> > reference report items inside the same grouping scope...
> > >
> > > Any help is welcome, since this looks like a basic functionality that
is
> > either missing or needs to be implemented in a way that I haven't
discovered
> > yet.
> > >
> > > Thanks,
> > > Vincent
> > >
> >
> >
> >

how to count with restrictions

Hello Gents,
need your help to write a formula for counting records with defined calue in the fileld, e.g. how many records with value "123456" in field {table.field}?
thank you very much for your help!U can.

Create a running total field. Add the field which you want to count. Give "Count" as your type of summary.

In Evaluate option,
select "Use a Formula" option. click X-2 box. Write your formula. Never reset this .

u can get the count what u want.

Hope this work.|||thanks a lot, but which will be formula in my case?|||Just simply enter

{table.fieldname} = "123456"|||thanks for prompt reply, but unfortunatelly it always returns zero...|||If it is string field, use trim function, records may be stored with spaces.

Otherwise it should work if the table contains records for that value|||Doesnt help... When i use simply count({fieldname}) - that returns total fine, but with this restriction it doesnt work...|||Sorry for asking this,

Do you have any records which meet ur condition?

I used lots of running total sums with various conditons. working fine.|||Sure, i have :-)
Actually, this field (string type) is a set of 5 kind of different words (total is ~700). I would like to know how many i have words "123456" for example.|||In addition: when i use running total with evaluation "For each record" - it returns 1. When i set type of summary to maximum - then returns only value, not the sum.|||Make sure

i) Included the exact field name in "Fields to Summarize" box
ii) Type of Summary is count

iii) Evaluate:

Check "Use a formula option" and
write the formula in formula workshop (by clicking X-2)
trim({table.fieldname}) = "123456"

iv) Reset:

check "Never" option|||Yes, all of these conditions is true.|||Ok, good

Now where have u placed your running total field?
(in which section)|||In ReportHeader.|||that's the problem.

It's running total. So will be calculated while printing records.

So place in Report Footer. So u'll get the count.|||Thats works! Thank you very much!!!