Can a script dynamically populate a report?

[CocoaRush]

I'd like to create a report that shows the future value of a savings account in yearly increments.

Example:

2010 | $100.00

2011 | $105.00

2012 | $110.10

The parameters of the valuation like starting balance, interest rate, and how many years to project are in a table. I'd like to have a the line items in the report be populated by the database dynamically as the report is run.

I'm thinking I could do this with the use of a script that populates a separate table then using this table to base the report from, but I don't have enough experience to know if it is the best way to do it.

Does anyone have a better approach?

[bcooney]

That sounds like a good approach. A "temp table" "reporting table" or "scratch table" are terms I've heard for such an approach. Tag each record with a unique constant such as a timestamp (captured at the start of script into a $var), so that you can be sure that you are reporting on the correct set of records.

[comment]

Tag each record with a unique constant such as a timestamp

I would think the AccountID might be a better choice?

Another option is to forgo the script and do the report directly in the account record, using either a custom function or repeating calculation fields. However, it depends on how this report is going to be used.

[bcooney]

I think AccountID might pick up "old records." Obviously, cleaning out this table after you're thru is ideal.

[comment]

Well, a timestamp might pick records created by another user for another account...

Anyway, I think one should delete related records before creating new ones. Again, it depends on how this is going to be used, but I don't see the related table as "temp" or "scratch": as long as the basic data doesn't change, the records continue to reflect the future value of the account in the projected year and there's no need to delete them.