Creating a report in an outline format.

I have a table that is set up to act much like an Outline. This Outline can
possess up to five levels. Visually, this table looks much like this:

01 Header
0101 Subheading 1
0102 Subheading 2

02 Header 2
0201 Subheading 1
020101 Sub-subheading 1
020102 Sub-subheading 2
020103 Sub-subheading 3

03 Header 3

Now, I have designed a second table that can link its records to be assigned
to line items in this previous Outline table. These records can then be
given further financial information that we now want to be able to report in
a logical, clear manner.

Obviously, this means we want our reports to look as much like an outline as
possible. We have tried two methods… grouping on levels of the outline, and
creating one subreport for each level of the outline. We have run into the
following issues:

When grouping on levels of the outline, each level of the outline is grouped
on with it’s own subtotal line. For example, there would be a subtotal line
for the value of level one, level two, and a third for level three.
Therefore, even if a given record is only assigned to outline item such as 01
or 02, these subtotal lines appear for the all the unused levels below it,
repeating data unnecessarily. Additionally, because all this grouping takes
place in a single report, no indentation is available, making the report hard
to look at.

When using subreports, problems still arise. Each level in the outline now
has a corresponding level in the report, and the reports are linked, each one
to a subreport for the more detailed level of the outline. The problem is,
if there is no record assigned to the higher level of the outline (for
example, 03), then no records will be displayed for the lower records
assigned below it (for example, 0301, 0302, and 0303), because the subreports
cannot show data when the report above them has no data.

Any ideas or suggestions would be helpful. Thank you!

Dustin

Dustin Ventin wrote:

I have a table that is set up to act much like an Outline. This Outline can
possess up to five levels. Visually, this table looks much like this:

01 Header
0101 Subheading 1
0102 Subheading 2

02 Header 2
0201 Subheading 1
020101 Sub-subheading 1
020102 Sub-subheading 2
020103 Sub-subheading 3

03 Header 3

Now, I have designed a second table that can link its records to be assigned
to line items in this previous Outline table. These records can then be
given further financial information that we now want to be able to report in
a logical, clear manner.

Obviously, this means we want our reports to look as much like an outline as
possible. We have tried two methods… grouping on levels of the outline, and
creating one subreport for each level of the outline. We have run into the
following issues:

When grouping on levels of the outline, each level of the outline is grouped
on with it’s own subtotal line. For example, there would be a subtotal line
for the value of level one, level two, and a third for level three.
Therefore, even if a given record is only assigned to outline item such as 01
or 02, these subtotal lines appear for the all the unused levels below it,
repeating data unnecessarily. Additionally, because all this grouping takes
place in a single report, no indentation is available, making the report hard
to look at.


I think you can come close if you group on the expressions:

=Left(Level,2)
=Left(Level,4)
=Left(Level,6)
=Left(Level,8)

To get the indenting, use code something like this in the
Detail section's Format event:

Me.Level.Left = 100 * Len(Me.Level)
Me.Descr.Left = Me.Level.Left + Me.Level.Width + 200

I don't understand what you want with the "subtotal line".
If you are using the group footer section's to aggregate
something, then the above groupings may or may not allow for
simple Sum expressions.

--
Marsh
MVP [MS Access]

Thank you, I'll see if this helps.

Dustin

"Marshall Barton" wrote:

Dustin Ventin wrote:

I have a table that is set up to act much like an Outline. This Outline can
possess up to five levels. Visually, this table looks much like this:

01 Header
0101 Subheading 1
0102 Subheading 2

02 Header 2
0201 Subheading 1
020101 Sub-subheading 1
020102 Sub-subheading 2
020103 Sub-subheading 3

03 Header 3

Now, I have designed a second table that can link its records to be assigned
to line items in this previous Outline table. These records can then be
given further financial information that we now want to be able to report in
a logical, clear manner.

Obviously, this means we want our reports to look as much like an outline as
possible. We have tried two methods… grouping on levels of the outline, and
creating one subreport for each level of the outline. We have run into the
following issues:

When grouping on levels of the outline, each level of the outline is grouped
on with it’s own subtotal line. For example, there would be a subtotal line
for the value of level one, level two, and a third for level three.
Therefore, even if a given record is only assigned to outline item such as 01
or 02, these subtotal lines appear for the all the unused levels below it,
repeating data unnecessarily. Additionally, because all this grouping takes
place in a single report, no indentation is available, making the report hard
to look at.


I think you can come close if you group on the expressions:

=Left(Level,2)
=Left(Level,4)
=Left(Level,6)
=Left(Level,8)

To get the indenting, use code something like this in the
Detail section's Format event:

Me.Level.Left = 100 * Len(Me.Level)
Me.Descr.Left = Me.Level.Left + Me.Level.Width + 200

I don't understand what you want with the "subtotal line".
If you are using the group footer section's to aggregate
something, then the above groupings may or may not allow for
simple Sum expressions.

--
Marsh
MVP [MS Access]