Report with multiple details bands and summary issue

[Eugen Fintina]

Hello everybody,

I have a report with multiple details bands. Everything is Ok less the summary values. I don't understand why, but I get values multiplied 2 or even 3 times. The totals that I made in detailes footer and group footer are corects.

I must mention that I tried sum fields values or sum variables values with no success.

I used VFP 9.0 SP2.

I wonder if anybody tried same issues and if there is an explication or workaround.

Best regards,
Eugen

 

[Olaf Doschke]

Without any details about your settings for summary data it's impossible to help you. In general this works as expected, there is no bug in report variables.
Most important: What dod you set for "Reset value for"
As multiple detail bands are based on relations, you can go through same records twice or more times, reset on the last detail band, perhaps.
Summary band comes even after report footer, it's always a report summary, use grouping to group portions of your data.

Bye, Olaf.

 

[Eugen Fintina]

Let's say that the report must list all the invoices and payments associated with its for a customer into a time period.

The report have the folowing elements:
1. Data grouping: invoices.id

2. Detail 1
2.1. Target alias expression: "Items"
2.2. Detail band: items.quantity, items.price and value (items.quantity * items.price)
2.3. Detail footer: Total of values

2. Detail 2
2.1. Target alias expression: "Payments"
2.2. Detail band: payments.date and payments.amount
2.3. Detail footer: Total of payments amounts

3. Group footer: Unpayments (Total of values - Total of payments amounts)

4. Summary
4.1. Global total for items values. I have a variable that have:
- value to store: items.quantity * items.price
- initial value: 0
- reset value based on: I tried report, group, detail 1, detail 2 with no success
- calculation type: sum

4.2. Global total for payments amounts. I have a variable that have:
- value to store: payments.amount
- initial value: 0
- reset value based on: I tried report, group, detail 1, detail 2 with no success
- calculation type: sum

4.3. Global total of unpayments

 

[Eugen Fintina]

After days of trying to find a solution, finally I found one. Thank's to Cathy Pountney for it!!!
I reproduce it because nowadays Foxpro's resources are all less and less , but you can find original hint here:

http://cathypountney.blogspot.ro/2008/04/how-do-i-total-fields-for-one-detail.html

When multiple detail bands were introduced to Visual FoxPro it was a great addition that was long overdue. However, there's one aspect of that feature that wasn't clearly thought through and that's Calculations and Report Variables.

Let's say you create a report with multiple detail bands. Then you add a data group to the report and in the data group footer you add some fields that are defined to calculate using the SUM option. The problem that can arise is the calculation is applied to every record in both detail bands and maybe you only want to sum the records in the first detail band. Hmmm ... now what?

There's a workaround, but it takes some extra work on your part. Start by defining a Report Variable called rnDetail; Set the Value to Store to 0; Set the Initial Value to 0; Set the Reset option to End of Report; and set the Calculation option to Sum. This creates a Report Variable that the Report Engine doesn't ever change but now you have something you can manipulate.

The next step is to make sure each of the detail bands are marked to include the associated header and footer bands. In the OnEntry Expression of the Detail Header 1 band, enter the expression: _VFP.SetVar('rnDetail', 1). In the OnEntry Expression of the Detail Header 2 band, enter the expression: _VFP.SetVar('rnDetail', 2). These two expressions reset the Report Variable to 1 or 2 at the beginning of the respective detail band so now you have a way to know which detail band you are on.

The next step is to create Report Variables for each of the fields you want to sum. For example, if you want to sum the Qty and Price in the first Detail Band, create a Report Variable called rnQty and set the Value to Store to IIF(rnDetail = 1, Qty, 0). Create another Report Variable called rnPrice and set the Value to Store to IIF(rnDetail = 1, Price, 0). Leave the Calculation option of both variables at the default of None.

The final step is to add the field objects to the data group footer band. Instead of using the name of the field such as Qty or Price, use your new Report Variables of rnQty and rnPrice for the expression. Set the Calculation options to reset at the data group. Now you have some calculations in the data group footer that only contain values from the first data detail band.

This concept can be used in various situations when you need to create calculations or Report Variables that only get processed on one of many detail bands. Once you have the rnDetail Report Variable in place, you can reference it as needed. Just be sure this Report Variable is at the top of the list of Report Variables before any other Report Variables that need to access it. The Report Engine processes the Report Variables in the order they appear in the list.