I have a report with one group and a summary total.
Adding a new detail band, a big new feature added by VFP9, lead to incorrect totals/subtotals.
I am using VFP9 SP1, behavior80, but is the same with behavior90.
Steps to duplicate:
1) Run this code
create table PRINCIPALE ;
(AGENTE C(2), ;
N_DOCUMENT C(5), ;
D_DOCUMENT D, ;
TOTALE N(5), ;
CHIAVE C(3))
index on AGENTE+N_DOCUMENT tag "ORDINE01"
create table DETTAGLIO2 ;
(CHIAVE C(3), ;
D_SCADENZA D, ;
IMPORTO N(5))
index on CHIAVE+DTOS(D_SCADENZA) tag "ORDINE01"
insert into PRINCIPALE values ("A1", "DOC 1", {^2005-09-27}, 1000, "001")
insert into PRINCIPALE values ("A1", "DOC 2", {^2005-09-27}, 1000, "002")
insert into PRINCIPALE values ("A2", "DOC 3", {^2005-09-30}, 1000, "003")
insert into PRINCIPALE values ("A2", "DOC 4", {^2005-10-03}, 1000, "004")
insert into DETTAGLIO2 values ("001", {^2005-10-27}, 500)
insert into DETTAGLIO2 values ("001", {^2005-11-27}, 500)
insert into DETTAGLIO2 values ("002", {^2005-10-27}, 1000)
insert into DETTAGLIO2 values ("003", {^2005-10-31}, 200)
insert into DETTAGLIO2 values ("003", {^2005-11-30}, 200)
insert into DETTAGLIO2 values ("003", {^2005-12-31}, 200)
insert into DETTAGLIO2 values ("003", {^2006-01-31}, 200)
insert into DETTAGLIO2 values ("003", {^2006-02-28}, 200)
insert into DETTAGLIO2 values ("004", {^2005-11-03}, 300)
insert into DETTAGLIO2 values ("004", {^2005-12-03}, 700)
select PRINCIPALE
go TOP
set relation to CHIAVE into DETTAGLIO2
2) Create a report
Create a report with the fields N_DOCUMENT, D_DOCUMENT and TOTALE in the detail band.
Create a group on field AGENTE.
Create a Summary band.
In the Group footer 1:AGENTE band put the field AGENTE and the field TOTALE with Calculation type: Sum and Reset based on: Group: AGENTE.
In the summary band put the field TOTALE with Calculation type: Sum and Reset based on: Report.
Run the report.
Everything in ok, the subtotal for AGENTE A1 is 2000, the subtotal for AGENTE A2 is 2000 and the Summary total is 4000.
Now modify the report simply adding a second detail band.
In Detail 2 put "DETTAGLIO2" in the Target alias expression.
In the Detail 2:"DETTAGLIO2" band put the fields DETTAGLIO2.D_SCADENZA and DETTAGLIO2.IMPORTO.
Run the modified report.
This time the subtotal for AGENTE A1 is 5000, the subtotal for AGENTE A2 is 9000 and the Summary total is 14000.
I haven't found a way to have the correct SubTotals and summary total.
A workaround
Any ideas
The report behave like I have set "set skip to DETTAGLIO2".
And I have read in some documentation that, in fact, it mimics "set skip to".
But this is a big problem.
Adding detail bands break a good simple report...
Thanks for the attention!
Giovanni Turri
VFP9 printing issue with multiple detail bands and group subtotals and/or summary totals
Gilmorenator
This link is no more active but is still available in the cached copy of Google:
http://www.google.it/search hl=it&safe=off&rls=GGLG%2CGGLG%3A2005-51%2CGGLG%3Ait&q=site%3Awww.markagoda.ru+Calculations+reset+on+group%2C+page%2C+or+report%2C+with+multiple+detail+bands.&btnG=Cerca&meta=
Open the link, open the Cached copy and look for "Run-time activity".
Now is lost also the Cached copy mantained by Google...
Oscar in LA
Tamar
Marian Andrei
I was not able to figure it out alone ...
Thanks a lot Tamar, you have gave me the solution!
Gianni
nzracer
Please help!
Difficulties with italian field names
HonzaHornik
Alternatively, compute the totals with a query ahead of time and use another detail band to report them. See if the Solution Sample "A multiple detail band report used for calculations" helps.
Tamar
derekR
You are pointing to this article:
Multiple Detail Band Reports
Learn how to take advantage of this Visual FoxPro 9 reporting enhancement.
By Pamela Thalacker, FoxPro Advisor contributing editor
http://my.advisor.com/doc/16675
The article in question, accessible only for subscriber, does not mention the problem with group and summary total.
It discuss about other questions about multiple detail band reports.
And anyway does not offer a workaround.
Aaron Lahman
Thanks Tamar for the reply.
I have tryed without results the way of the variables. Can you help me specifically
I know that I can solve the issue computing the totals ahead, but is a solution that requires code and new fields, a user that can only modify the report cannot do it.
Remember that it is a simple report, it begin computing wrong adding detail bands.
I would want to know if this can be submitted as a bug.
No mention in help file about this behavior with group totals and summary totals.
Gianni
robc180
As I said, I haven't done this, but perhaps you can use a trick like the one we use for counting groups in a report. Use 2 report variables, as follows:
1) Total
Initial Value: 0
Value to Store: IIF(LastValue<> <a field that changes for each record in the first detail band>, <field you want to total>, 0)
Calculate: Sum
2) LastValue
Initial Value: an impossible value for the same field referenced above from the table in the first detail band.
Value to Store: the same field
That is, the Total variable is adding up the value of the field you want to sum, but doing so only when the record has changed in the first detail band. The Current variable is tracking the last value of one field of the table in the first detail band. When you're processing the second detail band, the record pointer shouldn't move in the first detail band table and nothing else should get added.
Let me know if this works for you.
Tamar