Multi-Column Reports

[Devlin]

I am seeking the best way to develop a report that involves multiple columns
across and includes sub columns. The report must look something like this:

Year: 2000 | Year: 2001
Accts Crd Dbt PrvBal Bal | Crd Dbt PrvBal Bal
---------------------------------------------------------------
Acct01 12 10 8 10 | 20 10 10 20
Acct02 ....
....

In this example only two years are displayed. However, in the report it is
required that three years be displayed on one page. Furthermore, there may,
and most likely will be more than three years of records to display.
Therefore, the report must again, display three years per page, and span
multiple pages.

The best way we have come up with to do this is to use a temp table.
However, we foresee that this could be troublesome in a multi-user
environment.

Another hurdle that we face in this task is in some cases, some accts may
need to be grouped. Grouped accts are defined in a separate table from the
actual accts table. To overcome this hurdle we are using a union query to
gather the individual and group values combined. This query looks up the
credits and debits. We then programmatically look up the prev and current
balance and update the table records.

If anyone knows of a better way to tackle this challenge, any help would be
greatly appreciated!

 

[Rick Brandt]

I am seeking the best way to develop a report that involves multiple
columns across and includes sub columns. The report must look
something like this:

Year: 2000 | Year: 2001
Accts Crd Dbt PrvBal Bal | Crd Dbt PrvBal Bal
---------------------------------------------------------------
Acct01 12 10 8 10 | 20 10 10 20
Acct02 ....
...

In this example only two years are displayed. However, in the report
it is required that three years be displayed on one page.
Furthermore, there may, and most likely will be more than three years
of records to display. Therefore, the report must again, display
three years per page, and span multiple pages.

The best way we have come up with to do this is to use a temp table.
However, we foresee that this could be troublesome in a multi-user
environment.

Another hurdle that we face in this task is in some cases, some accts
may need to be grouped. Grouped accts are defined in a separate
table from the actual accts table. To overcome this hurdle we are
using a union query to gather the individual and group values
combined. This query looks up the credits and debits. We then
programmatically look up the prev and current balance and update the
table records.

If anyone knows of a better way to tackle this challenge, any help
would be greatly appreciated!

Does the report need to dynamically adjust to the number of years or will you
know that number when designing the report? Doing it dynamically would be a
problem even for one set of columns much less the sub-column issue you are
dealing with.

 

[Devlin]

The years are specified by the user. As far as it goes, the report will
always display at least three years of data. That is even if no transactions
have occurred in some of the post years.