RFC: Dynamic Report

[Tom Ellison]

I have the concept of a dynamic report in mind. I have done some of this in
the past, and have what I believe to be a "proof of concept" at this point.

The Problem

You can design a report to have no problems. Years ago in mono-spaced fonts
(10, 12, or 16 characters to the inch, no matter which characters, all the
same with) you could place columns across a page fearlessly. But the
true-type font has changed all this.

Don't get me wrong. As a rule, it works very well. It looks great.

But, if you're going to represent a 20 character wide column of data on a
piece of paper (where you can't scroll to see the rest of the column if it
doesn't fit) and you're going to guarantee it will always fit by making the
column wide enough, you're in for a problem.

In a text column, place the test data to see this work. For our
hypothetical 20 character column, put in the character W 20 times. Now just
make the control for the column wide enought to hold all of that.

In order to get a pretty good report on across single sheet of paper, you
may end up with a font size of 3 or 4. With a microscope, and given a very
high resolution printer, someone may actuall read this.

The next thing is that, given more usual data, you're going to have a
situation where the printed page has tiny letters and columns of print that
fill only about 1/4 of the width of the columns. I've had users tell me I
leave too much space and use too small a font, and I've never come close to
making things wide enough for 20 W's.

One of my design tools has been a dll function that gives you the width of
any text, given font and font size (plus whether italic, bold, etc.). I
have used this in queries to find the maximum width of the existing data.
Leave an additional 10, 20, or 25% just in case, and you have a fairly happy
medium, with room for even longer text values that may come later.

Now, we commonly prorate our screens so everything will fit no matter what
screen resolution the user picks. This makes the same application run on
many computers at many resolutions and still look good. I can't live
without that one!

Why not apply the same thing to reports. Here's what I propose, and have
partly accomplished:

Find the maximum width to display each column (this is a query using the dll
function giving width). Add up all the columns, adding space between the
columns as desired. Prorate the font size up or down so it will fit across
the page. At this new font size, find the width to display each column at
the newly calculated font size. Size and relocate each column, spacing them
as required. Set the font for these controls.

I have to do the same with column headings (wouldn't look any good if you
don't, eh?). I associate column headings with the controls with the data by
tags. I also use tags to identify the horixontal lines to draw across the
page - these must be located correctly as well. This can be a double tag,
keying the line to the position of the left edge of the left most data
column under which it is drawn, and the data column to the right edge of
which determines where it ends.

I've pretty much got this working. If you have totals, you will need a bit
of extra room on those columns. You can use the factor of 10, 20, or 30% I
suggested to allow for that. It could also be done by setting up a database
in which, for each report, additional parameters are assigned. Say, for
column tagged "XY" add an extra 10%. Indeed, a database of such report
design parameters seems exactly what is needed.

Now, I know you would get bloat changing a report all the time like this.
That is, you would if you saved the design changes. But they don't need to
be saved! You can override saving design changes when the report closes,
and there's no change, and no bloat.

I post this to be open for comments.

My own comment is that this would best be done page by page, rather than the
whole report. The columns on one page would not then line up with the
columns on the next page, because the data in different columns is different
on one page from the next. Each page would have its own font size, for that
matter. Pretty neat! And, while you're at it, dream on! But some time,
somewhere, this is going to become the standard for database tools! (My
prediction.) When it does, everything else is obsolete!

Tom Ellison

 

[Duane Hookom]

Tom,
Sounds like you have given this a lot of thought and time. To give users
dynamic reports, I generally allow them to create dynamic queries and
display the results in Excel. Most users feel fairly comfortable resizing
and printing "to fit". This solution is not as slick as yours but it offers
a good deal of functionality and flexibility.

 

[Tom Ellison]

Dear Duane:

Excel has a printing option to prorate the page. That's something missing
form Access. Sorely missed!

Your suggestion is brilliant, of course. The capacity in Excel, while it
doesn't do just what I want, it is useful and was an inspiration in what I
do now. An "Export to Excel" option is available on many of my reports, but
this was never a primary reason for that.

The problem with Excel reports is that, you cannot size subsections of a
report (sub-reports) independently of the columns of the rest of the report.
(Am I correct in this? The column widths you select extend the entire
length of a worksheet.) It would be terriffic if you could have multiple
sub-report sections in Excel, each with their own set of column widths.
Sounds like a nifty enhancement.

Actually, you can create this effect in Word. You set up templates for
various sections in a document, and apply them by name. A template can
apply to repeated portions of a document. If you change the template, every
section of the document to which it applies is changed. That's the way to
do this. But I have no idea how to set up such templates as an automated
feature of a report, and embed the templates within the report. Otherwise,
an export to Word could be the perfect solution for this. I would then
learn to program Word to adjust font and column widths so everything fits!

Now you've gone and done it! You've got me thinking again (I believe it has
been quite a while since I've done this!)

OK, I could embed a text "marker" in my report, which I could search in
Word, and replace with the "change to template" function. This could be the
first step in a series of steps programmed in Word to do everything I need!
Word could handle the sizing (font and column widths) and this could be
programmed. Perhaps the whole thing could be automated. Access sends the
report to Word, and initiates the custom processing there, and Word prints
the document and closes. I knew there was a reason Office is a suite of
connected programs. I think I'm going to look into this!

You see how brilliant you are, Duane? How do you come up with these gems?

Well, that's a beauty of the RFC. Get the juices flowing. Now I just need
to increase my Word programming capacities. Anyone have an idea whether the
capabilities exist there? I expect they do!

Tom Ellison

 

[Duane Hookom]

Tom,
You are the one who seems to have the ideas and motivation lately. Here's
another one for you. Rather than create an Access-Access or Access-Excel or
Excel-Word solution, consider Access-HTML. You theorized about maximum
column widths and lots of other stuff. Typical HTML tables resize based on
the data contained in the <td> and <th> tags. You can use style sheets,
vbscript,.....Have fun...

 

[Tom Ellison]

Dear Duane:

Motivation! Good one! And you probably know what is the prize I'm after.
Such a greedy guy I am!

So, in this HTML driven solution, we create such tags embedded in our
report, then open the report in, what, IE, and cause it to be printed there?
Hmmm. I can see it happening.

But, one of the steps necessary is to have it dynamically select a font size
to prorate what is essential in my thinking. I want it to prorate the font
to affect the width only, no changes vertically. Of course, I know that
characters also get taller or shorter as you change font size. But the
height of the text box would not be changed. An essential feature of my
plan is to keep the pagination unchanged for that assigned by Access.
Otherwise, such features as Keep Together will be a problem. The decisions
on pagination must be done in Access, and must remain valid through to the
finished product. It has to print as originally formatted, but with some
new capacities to adjust column widths and fonts to affect horizontal
formatting.

My concept (yesterday, after we began our exchange) was to use some
programming inside Word to affect these things. There would be some
customization done there. Word handles VBA, and I could create such VBA to
get the job done (I hope!). In my concept, that was an integral feature
toward what I want done.

Already I'm wondering if there's a way to detect, within Word, that it has
been invoked from Access to do this particular task, and to invoke the
programming I provide to Word to perform the task. That is, the task of
changing column widths and font sizes in creating templates, then assigning
those templates to the sections of the text. Sounds like some research is
in order, and a trip to the Word newsgroups.

I'm not so accomplished with what can be done using HTML in this respect.
Perhaps that's why I'm leaning toward a solution within the Office Suite.
At least here I've had some experience with what can be done.

I thank you for your extremely stimulating responses. I'm actually quite
excited by some of the inspiration you've caused. It really helps that
we've met previously and shared some experiences technically. The fact that
I know you and respect your opinions really releases the juices toward such
inspiration. I can hardly wait to have the time to begin trying some of
this. I just hope it doesn't detract too much from my posting in the
newsgroups. But I'm very primed to try it.

I think I'll go whip up a batch of peanut butter cookies, then try tackling
some of this. Brain food, you know! Sounds like an all-nighter!

Tom Ellison