calculate product instead of sum in subtotals on report

[bubbacake]

I need to calculate the product of each of the items in each group on my report. Does anyone know how to do this?

 

[MichaelRed]

The Shadow knows, but he isn't telling which items you want the "product" to be clculated from.

I, on the other hand, would like a bit of clarification.

Mostly, the "product" in a report would be something like the sum of the product of two (or more) fields for each record in the report. This would often be the "Sum" or report total.

Your statement appears to ask for the product formed by the multiplication of the values from a single field for each record in the report.

[b[IF[/b] you mean the former, more traditional approach, this is often done by actually doing the 'product' for each record. If the 'exxtended' amount is not required for each record, you can make the text box holding the value NOT visible. Wheather the 'extended amount is visible or not, an easy way to get the report total is to just add another (almost always hidden) textbox whose controlsource is the extended amount and whose running sum is set to over report. Finally, a last text box is placed in the report footer. It's control source is set to the running sum textbox in the detail.

[b[IF[/b] you mean the latter. Please clarify that and provide a BRIEF example.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[bubbacake]

The report looks like this:
Australia Return
Jan 01 +0.25%
Feb 01 -0.25%
Mar 01 +1.23%

Total return for period: ??????

Australia Return
Jan 01 +0.25%
Feb 01 -0.25%
Mar 01 +1.23%

Total return for period: ??????
etc.

I'm trying get the total return for the period for each country which requires adding 1 to the return for each month, then multiplying or "chaining" each of those numbers. I would also like to do the same calculation for the entire report. In Excel I would use the Product function to multiply the numbers in the series

Thanks for your help.

 

[MichaelRed]

I'm still not convinced. You CAN do the calc, however if you do it just from the percentage as shown, it is 'unweighted' and therefore probably misleading -at best.

Using YOUR calc, I would get a net for the period of:

? 1.25 * (0.75) * 2.23
2.090625


But, what about the (more-or-less arbitrary FACT) that the JAn Figure is derived from a volume which is three times the Feb Volume?, and the Mar Volume might be twice the Jan Volume.

I would THINK it should then be quite different for the net? DOn't you?

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[bubbacake]

Sorry, I forgot that you then have to subtract 1 from the final result. The equation is:

((1.0025)*(.9975)*(1.0123))-1=.0123 or 1.23% which is the return for the period.
The weighting does not matter because the object is to find the total return of 1 dollar invested at the beginning of the period.
I know I can create a hidden text box that adds 1 to each return number, but how do I get the product of those numbers in the group footer and report footer?
Thanks

 

[MichaelRed]

Within the report detail section, just keep the "Product" and 'do the math' for each record.

In the Declarations section of the report code:

Dim NetProd as Single

In the OnActivate Event of the Report (probably actually in the GroupHeaer):

NetProd = 1.

In the detail

NetProd = NetProd * Return

In the Report (And / Or Group) Footer:

txtMyPct.ControlSource:

=[NetProd]

Above is certainly "rough" and almost certainly not completly accurate. You appear to understand the overall basis, so this should be sufficient to get into real trouble.

(P.S. txtMyPct refers to the "Total Return For the Period&quot

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over