Re: Dynamic Report
- From: "Tom Ellison" <tellison@xxxxxxxxxxx>
- Date: Wed, 8 Feb 2006 14:58:03 -0600
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" <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 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
.
- Follow-Ups:
- Re: Dynamic Report
- From: Duane Hookom
- Re: Dynamic Report
- References:
- RFC: Dynamic Report
- From: Tom Ellison
- Re: Dynamic Report
- From: Duane Hookom
- RFC: Dynamic Report
- Prev by Date: Re: Angled Column Titles Labels
- Next by Date: Report help - using multiple queries to create report
- Previous by thread: Re: Dynamic Report
- Next by thread: Re: Dynamic Report
- Index(es):
Relevant Pages
|
