Re: Dynamic Report

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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...

--
Duane Hookom
MS Access MVP


"Tom Ellison" <tellison@xxxxxxxxxxx> wrote in message
news:OOW$fJPLGHA.648@xxxxxxxxxxxxxxxxxxxxxxx
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 work***.) 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" <duanehookom@xxxxxxxxxxxxxxxxxx> wrote in message
news:uqlmoiMLGHA.3260@xxxxxxxxxxxxxxxxxxxxxxx
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.

--
Duane Hookom
MS Access MVP


"Tom Ellison" <tellison@xxxxxxxxxxx> wrote in message
news:eL9L1AJLGHA.3460@xxxxxxxxxxxxxxxxxxxxxxx
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 *** 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








.

Quantcast