Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
The
Crystal Reports Underground News - Volume 2007.07 (July 2007)
an independent source
for Crystal Reports Information by Ken Hamady
Contents
for July 2007:
** BO
updates the Offline viewer
**
My favorite utilities
**
Canadian holidays formula
**
My library of Crystal Reports guides
**
New - Certified associates to teach your on-site classes
**
Stop struggling with your reports, let me help
**
Free options for desktop sharing
**
Finding a stray formula or parameter in your report
**
ToNumber vs Val
**
Read back issues at http://www.kenhamady.com/news.html
Business
Objects updating their off-line viewer:
Recently
Dave Sharpe (a former student) forwarded a message to me from
BO. BO has announced a BETA program for something called Crystal
Reports Viewer XI.
This is really an update of their "Offline Viewer" which allows users
to view and print reports, but ONLY if the report is saved with data.
They apparently don't want to sell a viewer that allows refresh because
that would give users an alternative to the more expensive BO server
products.
They have
even started a forum
for feedback from people in the BETA program. Or course the
first
comments were 'it needs to allow refresh'. The response from the BO
forum moderator hints that BO is (finally) considering releasing a
viewer that can refresh reports. If they do it will have to
be better
than the ones that are already out there. Read the next
article for
info on my favorites viewers.
My
Favorite 3rd-Party products for Crystal Reports:
Many of my
readers contact me to recommend a 3rd-party utility. The
most common requests are for viewers, schedulers and Email distribution
utilities - probably because these categories offer choices from
several vendors. So I have written up a short comparison of
my
favorite utilities, and when I would recommend each one. If
you are
interested in reading this, send an Email to favorites@kenhamady.com and you
will receive an automatic response with the link to my review.
Formula to
generate Canadian holiday list:
One of my
most used formulas is my "Business Days" formula which allows
you to calculate the number of business days between any two dates.
Using this formula requires that you store a list of your holidays for
each year. One of my readers (Mike Cook of Shepherd Hardware Products)
provided a formula to calculate the common U.S. holidays - even going
so far as to provide a formula to calculate Good Friday - a pretty
amazing feat. Now one of my Canadian readers (Greg Fendall at net-linx
Americas) has provided the Canadian version of the holiday formula. If
anyone would like to provide the formula for another country I will be
happy to post it. See all of these formulas, and others on
the FORMULAS page of my
site.
My
complete Library of Crystal Reports Materials:
Let a
master teacher help you understand these Crystal Topics. Each
guide comes with clear explanations and sample files to illustrate each
concept.
Expert's Guide to Formulas
($36)
Expert's Guide to Subreports, Parameters and Alerts ($28)
Expert's Guide to Totals ($24)
Expert's Guide to Cross-Tabs ($22)
Expert Techniques Vol. I ($19)
Expert Techniques Vol. II ($19)
Expert Techniques Vol. III($19)
Quick Reference to Crystal Reports in Visual Basic ($16)
Quick Reference to Crystal Reports in .NET ($14)
You will
find these on the LIBRARY page of my
site.
New
"Certified" associates for teaching Crystal Reports classes:
As many of
you know I am not a big fan of Business Objects'
certification programs. That is why I have never been
certified by
Business Objects. I also don't require that my associate
instructors
have certifications. What I do require is Crystal teaching
experience
and good references. That is why my team gets such
enthusiastic
feedback from customers and students.
Up to now,
any organization that wanted instructors with a BO
certificate would have to pay the Business Objects list price for
training. So I am happy to announce the addition of 2 new
associates
to my team who both have certification for Crystal Reports XI. Both of
these instructors were "official" instructors sent out by Business
Objects. They both taught classes for BO on a regular basis,
so you
can't get better credentials than that. Of course they also
have the
experience and references that I require.
Both of
these instructor are willing to travel. So, if you
need
someone certified to teach your next Crystal Reports class, you should
still give me a call.
Stop
struggling with your reports, let me help:
You need
information to run your business, so stop struggling with
Crystal Reports and learn how to use it. Come join one of my
small
classes (3-5 students) and learn how to make Crystal work for
you. My
Intro class makes sure you know all of the basics, including
cross-tabs, charts and even some formulas. The Advanced class
shows
you how to solve several common reporting problems as well as when to
use advanced features like running totals, subreports, parameters and
commands. Users of any version are welcome. Classes are held
in
Frederick, MD and the next round is in mid-August. See my web
site for
more details or call me at (540) 338-0194.
So what
makes my class different? How many instructors teach
you from
a book that they wrote? How many have taught more than 2,000
students? How many give you a toll-free number so you can
call them
after class with questions?
If you want
to schedule a class at your office, using your data, let me
know. That is my specialty. I have even found
several top-notch
instructors around the US and Canada that can deliver my class at your
location for a very competitive price. See my web site for
more
details.
Free
options for Desktop Sharing:
I do quite
a bit of Crystal Reports consulting by phone. I recently
begun to find it more helpful to make a remote connection to my
customer's PCs. So I did some research on the current options
for
desktop sharing and was surprised to find several FREE options.
My current
favorite is Crossloop.
It is a free program that any two people can download and install.
Within a few minutes either user can "host" a session for a remote user
- allowing the remote user to take control of the host PC.
Cross-Loop
requires no registration, includes no adware/spyware, has no
time limits, is secure and free. All session traffic is
encrypted
end-to-end. The host has to initiate the connection by giving
the
remote user a random number generated by the program. When
the remote
user starts to connect, the host must also confirm that the connection
as allowed. Once confirmed, the remote user has shared
control of the
host desktop.
If you like
Webex you can also check out the new AOL/Webex
collaboration called AIM Pro.
It includes free 2-person Webex sessions and several other nice
features. The main difference is that Webex routes all
communication
through their servers. CrossLoop uses their server only to
manage the
connection and then tries to make a peer-to-peer connection between the
PCs.
Finding a
stray formula or parameter field:
Crystal
will not allow you to delete a formula or parameter field from
a report unless that item is no longer used anywhere in the report.
Unfortunately, there are several hundred places where a formula could
be used but not visible. These include sorting, grouping,
selecting,
totaling and the dozen or so formatting formulas available for every
report object. Checking all of these hiding places is not
practical,
so here are two ways to have Crystal help you uncover where the field
is being used.
My favorite
method is to edit the formula or parameter in question and
change it's data type. For a numeric or date formula change
the
formula to "x". If it is a character formula change it to a
numeric
value like zero. Then preview the report. Every
formula that uses
this item in a calculation will now pop up with an error message, and
you can fix them one after the other.
However,
the first method won't work if the formula/parameter is just
sitting on the report or is a sort/group field. You can find
these
items by exporting the report using the "report definition"
format.
This creates a text file that lists the location of all fields on the
report canvas. You can search this text file for your formula
name and
see where it is sitting or being used. It also shows the
contents of
all formula fields used in the report. But conditional
formatting
formulas are not included in this export in version XI. Some
older
versions included some or most of the formatting formulas but I have
yet to find a version of Crystal where ALL formatting formulas were
picked up by the export.
ToNumber()
vs VAL():
I often see
the ToNumber() function used in Crystal Reports formulas.
It is used to convert numeric text into a true numeric value.
That
tells me that the person who wrote the formula probably doesn't know
the advantages of using the Val() function. ToNumber() works,
but it
will generate an error if the input string has any characters,
punctuation or symbols in it. The usual solution with
ToNumber()
requires that you test the input string first like this:
If
NumericText ({field}) then ToNumber ({field}) else 0
However,
even this doesn't work well if characters come after the
numerics. For instance if you have street addresses that
start with
numbers, or a list of part numbers like this:
11220
11220C
127
128
128P
129
2458B
All the
values above that end with a letter would return a zero with
the formula above.
The Val()
function is more forgiving and will stop when it reaches an
alpha character or symbol. It then returns the value of the
numbers up
to that point. So the list of parts above would be converted
to their
numeric values and all the trailing letters would be ignored.
I have
found only a few rare reasons for using ToNumber() instead of
Val().
1)
ToNumber() can work with Booleans and currency values in addition to
strings. Val() can only convert strings.
2)
ToNumber() can also convert the following strings, with parens or a
leading dollar sign, while Val() will return zeros for both:
ToNumber (
"(123)" ) becomes -123
ToNumber
( "$123″ ) becomes 123
Contact
Information:
Ken Hamady
525K East
Market St.
PMB 299
Leesburg,
VA 20176
(540)
338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright
2007 by Ken Hamady
All rights
reserved -
Republishing
this material requires written permission