Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
The
Crystal Reports Underground News - Volume 2004.09 (September 2004)
an independent source
for Crystal Reports Information by Ken Hamady
Contents
for September, 2004:
** Spam Filters and
this Newsletter
** When Crystal tries to be "helpful" (and how to avoid it)
** Public Intro and Advanced classes in Frederick
** A New Access Report Converter
** A New Report Translation Management Tool
** My Library of Crystal Reports Materials:
Expert's Guide to Formulas
Expert Techniques Volumes I and II
Quick Reference to Crystal Reports in Visual Basic
Quick Reference to Crystal Reports in VB.NET
** Improved Rounding to the nearest 5 cents
** Alternate Method for Splitting a Section
** Read back issues at http://www.kenhamady.com/news.html
Spam Filters and this Newsletter:
Several readers have
asked me why they have stopped getting the newsletter, and usually it
is due to their company spam filter. Those of you who receive the
newsletter at work should be aware that spam filters are getting more
sophisticated and aggressive, and some are starting to block
newsletters like mine. If your company has a spam filter, and you
don't want to suddenly stop getting the newsletter, you should ask to
have my Email address added to the "white list" of acceptable Email
sources. If you have any questions or problems getting my
newsletter just let me know.
When Crystal tries to be "helpful"
(and how to avoid it):
Crystal is a very
sophisticated product, but there are times when it is just too darned
helpful. New users are often confused by this 'help' so I have
put together a list of what I consider the most common unwanted help
features, and how to avoid them.
"Auto-Linking" is a prime example of Crystal trying to help out, and
usually making things worse. This feature used to be called
"Smart Linking" but I guess even Crystal finally realized the irony of
the name. My estimate, based on my own experience, is that it
guesses correctly about 30% of the time. I teach my students to
turn this feature off in the first hour of my class. Except, of
course, in version 9 where you CAN'T turn this off. In v10 you
can turn it off, but only if you dig into the registry. If you
have version 8.5 or earlier just go into File->Options. You
can turn this feature off on the database tab.
Another 'helpful' feature of Crystal is trying save every report with
data by default. Saving a report with data can be a very handy
feature, and I use it often. However, I don't think it should be
the default for every new report. Many new users, even
programmers, get confused when today's report looks just like
yesterday's, and the day before that. So I have always taught my
users to turn this off as a default under File-Options, on the
Reporting tab. You can activate it, when needed, on individual
reports.
Then there is the more subtle help. For instance if you click on
a subtotal to move it or size it, and then you open the Record Sort
Expert (Sort Records in v8.x) Crystal will instead open the Group Sort
Expert (called TopN in v8.x). Crystal assumes that if you are
sitting on a subtotal when you try to Sort, that you want to Sort using
that subtotal. Personally, I think users who want the Group Sort
Expert are smart enough to select that menu option - especially
since it is right next to the one they actually selected.
There is a similar feature that tries to help when you are sitting on
just about any field. If you open the Select Expert, Crystal adds
a new rule tab using that field. I have watched users delete this
rule several times, only to see it return the next time they open the
select expert. Crystal assumes that if you are sitting on a field
when you open the select expert, you probably want to add a new rule
for that field. The added rule is set to "is any value", so it
doesn't affect your criteria unless you complete the rule.
However, many users don't realize that they can simply ignore the rule
and that it will go away on its own.
Another feature affects cross-tabs. If you happen to have the
cross-tab selected, and then open the Group Sort Expert, you will get a
Group Sort window for the cross-tab, instead of for the report's
Groups. In version 8.x the window will at least tell you it is a
"Cross-tab TopN", but in later versions you might not realize that it
is the Group Sort for the Cross-tab Rows. Of course this only
works if you select the entire cross-tab by clicking in the upper left
(empty) corner. If you are in an individual cell of the cross-tab
this doesn't happen.
This last one is for v9 and v10 users. When you create a formula
field you get the choice of using the Formula "Expert" or the Formula
Editor. Giving you an option to use the Formula Expert puzzles
me, since the Formula Expert can be used only in reports that have
custom functions. Most reports do not. The real confusion
comes if you accidentally save an empty formula. If you try to
Edit the empty formula Crystal automatically defaults to using the
Formula Expert, which confuses most users.
Since so few reports use custom functions it makes no sense to default
to the Formula Expert. This is especially true in v10 which
doesn't even have a repository to store custom functions (unless you
install Crystal Enterprise). In both v9 and v10 you can switch from the
Expert back to the Formula Editor by clicking the correct button.
In v9 the button is obvious since it says "Use Editor" in
English. In v10, however, when you end up in the Formula Expert,
you have to find a small tool bar button that switches back to the
Formula Editor. Look for the button with the magic wand.
Public Intro and Advanced classes in
Frederick:
My specialty is
teaching at your office, with your data, but not everyone has the 3 or
4 students to make this cost effective. If you want to take one
of my classes and don't have enough people for an on-site class, come
to Frederick, MD and take my public class. The classes are always
small and informal with lots of room for discussion. For details
see:
http://www.kenhamady.com/public.html
Is it worth the trip? I have personally taught 1875 satisfied
students. And, I am still the all-time, top ranked Crystal
Reports expert at Tek-Tips.com. For more information, or to
schedule an on-site class, call me at (540) 338-0194.
My Library of Crystal Reports
Materials:
Expert's Guide to
Formulas ($36)
http://www.kenhamady.com/form00.html
Expert Techniques Vol. I ($19) and Vol. II ($19)
http://www.kenhamady.com/expert.html
Quick Reference to Crystal Reports in Visual Basic ($16)
http://www.kenhamady.com/vbref.html
Quick Reference to Crystal Reports in .NET ($14)
http://www.kenhamady.com/ntref.html
Access Report converter:
I have uncovered 2
new products this month, and the first is a tool that helps convert MS
Access reports to Crystal Reports. Milestone Software in Edmonton
has worked for 3 years to develop this conversion utility and they feel
it is ready for the market. It converts groups, sections,
database references, subreports, text, lines, boxes, images and even
some formulas. Formulas that it can't convert are commented out
for manual conversion. It works with v8.5, v9 and v10 and with
several versions of MS Access. Cost is $395 for Edition and $595
for Enterprise Edition. See the LINKS page of my site for more
information.
The one thing that they haven't made clear is the software requirements
for running the program. The web site says that you can use this
product with v8.5 (no edition specified) but that if you use it with
v10 you have to have the Advanced Developer Edition. Look for a
clarification before you buy.
Report Translation Assistant:
Another new tool this
month is geared to those who have to maintain the same report in
several different languages. Crystal Translator, by Softlang
(UK), makes the job easier by first extracting all translatable objects
(text objects as well as literal text inside formulas, parameters,
alerts, etc.). It then allows you to enter translations for each
object and stores them in a database. If you later modify the
original report and want to translate it again, the database will
remember the translations of the original objects and apply them
automatically. You only need to translate newly added text.
You can even apply these prior translations to new reports, if the text
is the same.
In addition to translating text, it also allows you to localize and
format dates, times, numbers and currencies. Note that this tool
does not help you translate the data of the report, only static
text. It also does not do any initial translations. It
remembers translations that you provide. For more information see
the LINKS page on my site.
You do not need a copy of CR, or any CR skills to translate
reports. The cost is $299 for the freelancer edition and $699 for
the professional edition.
Improved Rounding to the nearest 5
cents:
Last month I shared a
formula that allows rounding to the nearest nickel. Two days
later, one of my readers gave me an even simpler and more flexible way
to accomplish the same task. Here it is, and thanks to Mike
Barron who told me that he found this formula long ago on an Excel
forum.
Round ( {database.field} / .05 , 0 ) * .05
What I like most about this approach, other than the sheer simplicity,
is that you could replace the ".05" with any other value and the
rounding will be based on the nearest increment of that value.
Alternate Method for Splitting a
Section:
There are several
reasons why you might want to split a section into two pieces.
You might want to format one part of the section with different
properties, or you might want to prevent objects that "can grow" from
growing over the objects below them. The standard ways to split a
section both create new space below the existing section.
You can do this two ways:
1) Go into Format->Section and click the button marked INSERT at the
top of the window.
2) Go to design mode, right-click on the section name and use "Insert
Section below".
Because these methods create new space, they require that you move your
objects to the new section and resize the original section. The
alternative is to use what I call the "knife" tool to divide an
existing section space into two pieces. To use the knife you put
your cursor along the right edge of the vertical ruler. Your
cursor arrow will turn into a divide indicator with arrows pointing up
and down. If you click with this indicator on the ruler and hold
down your left mouse button, you will see a gray line across the
section that you can slide up and down. Put this line where you
want the divide to occur and release the mouse button. The
section will split into separate sections at that point. Objects
that were above or below the line will now be in separate sections.
Many people stumble across this feature by accident when they are
trying to change the size of a section. The "knife" indicator is
very similar to the section sizing indicator. So, if you are
trying to size a section by grabbing the pipe very close to the ruler,
you may accidentally split the section rather than size it. That
is why I tell my students to size sections by grabbing the pipe in the
middle, away from the ruler.
Removal
instructions:
I
have
sent you my newsletter because you are a former client, or because you
have
contacted me regarding Crystal Reports. If I am mistaken, or if
you
don't wish to receive the newsletter, please reply to this message with
the
word 'unsubscribe' in the header. I do not share these Email
addresses
with anyone else.
Contact
Information:
Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright 2004 by Ken Hamady
All rights reserved -
Republishing
this material requires written permission