> I am sure people here have had to report data from multiple
> years as well as reports like Stock Ledger which mostly
> requires the current set but starts with summarized data for
> Opening Stock. What have they done?

Each problem domain can be, often subtly, different... and it
will almost certainly change over time. With that disclaimer
in mind:


My most significant application is in payroll and debtor
management. Payroll in particular gathers a large amount of
detail over a year and has quite strict annual reporting
requirements. Debtors has less volume, in comparison, and
its reporting seems to vary quite a lot - and is more likely
to span years.

My choice was to implement an archival system. At the end of
each financial year a copy of the previous year is saved as
a read-only "archive" database that the users can access if
necessary to run detail reports.

The new, each year, "production" database begins with most of
the previous year's financial detail removed - only higher
level total/summary values are kept from previous years and,
obviously, opening account balances etc.

Exactly when you do the rollover/archival process can be left
open. For example it may be some months after the end of a
financial year before most common reporting from the previous
year is complete... so for user convenience you may hold-off
the rollover until after this period has ended. In my case
I have made it all part of standard end-of-year processing.

This appoach has meant that the current production database
still has enough data to do some cross-year comparison reports
while not getting over-loaded with long redundant (old) data.
It also means that I have just one application written to
access just one database - choose production or an archive at
logon. Very little specialisation was needed to handle the
archive situation because each archive is a complete database
in itself. [Whereas writing an application to support many
interrelated (and not complete in themselves) databases, is a
very different (and more difficult) proposition.]

Yes the current production database is still getting bigger
each year, but this is happening much more slowly that it would
if it kept all financial detail.

(Obviously variations of this are feasible. A possibly useful
alternative could be to keep current and previous year in the
production database.)


I chose this solution primarily for system management reasons
rather than business-requirement reasons: Small databases are
easier to manage than large databases. Smaller backups, faster
backups, faster disaster recovery (and recovery testing),
faster database updates (archives can be updated independently
of production) and so on.


Another observation: Once a company gets to a certain size it
seems to employ financial people with great expertise (and I
must say imagination) in using Excel. I have found that I am
not often asked for cross-year reports from my application.
Instead they ask for an export (which they do from each of the
archives and production) which they pickup in Excel and do
their own analysis. Such analysis seems to change on a regular
basis, so this solution proves quite effective.

--
Geoff Worboys
Telesis Computing