Crystal Reports Training by Ken Hamady, MS, Reporting
and Training Nationwide
The
Crystal Reports Underground News - Volume 2004.05 (May 2004)
an independent source
for Crystal Reports Information by Ken Hamady
Contents for May, 2004:
**
Broadcast License Restrictions removed from CRv10
** New Shareware Viewer/Manager "Crystal Delivery"
** Public Intro and Advanced classes in Frederick
** Why you should turn off "Field Clipping" in any version of Crystal
** 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
** How to use "OR" and still pass a WHERE clause to your SQL statement
** Visual Cut Exports to specific Excel tabs:
** "Keep Group Together" in v10:
** Read back issues at http://www.kenhamady.com/news.html
Broadcast License Restrictions removed
from CRv10:
I mentioned this in a previous
newsletter but I recently confirmed my suspicion with the folks at
BO. The license for Crystal Reports v10 does not contain any
restrictions on the automated distribution of static report
output. No more CBL or Broadcast License as long as you are using
Crystal Reports without Crystal Enterprise. However, if you are
using Crystal Enterprise there are still some restrictions on automated
distributions from within the Enterprise environment.
What does that mean to you? You can freely use third party tools
to automatically Email reports to any number of users. Or, you
can automatically schedule exports that upload to a web site. You
no longer have to worry about the Crystal Broadcast License (CBL)
fees. However, those of you using CRv8.x or CRv9.x may still have
to deal with the CBL. The last time I checked, the license to
automatically distribute v8.x reports still cost $100,000.
Automated distribution under v9.x requires that you have a copy of the
Advanced Edition, which costs less than $2,000. Prior versions up
through and including CRv7 did not include any restrictions on
automated distribution.
New Shareware Viewer/Manager "Crystal
Delivery":
My site has something on it that
you won't find anywhere else in the world. A complete list of
third party products that integrate with Crystal Reports. You
certainly won't find these products listed on the Business Objects web
site. So, if you need a viewer, a scheduler, or a bar-code font,
the place to start is on the LINKS page of my web site.
While many of them are inexpensive, a few items are free, including one
brand new entry. There is a new report manager called Crystal
Delivery written by Kevin Groff. Kevin previously wrote Crystal
Passage, which was released as an "Open Source" project.
Unfortunately no other developers were interested in contributing to
that project. That is why Kevin decided to release Crystal
Delivery as normal freeware.
If you want to try out Crystal Delivery, or Crystal Passage, please see
the LINKS page on my web site.
Public Intro and Advanced classes in
Frederick, July 6 - 9:
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 in early July. 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 over 1750
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.
Why you should turn off Field Clipping
in all versions of Crystal:
If you have taken my Crystal
Reports class you should already know about Crystal's "field clipping"
feature. I also wrote about it in this newsletter several
years ago. However, I am still amazed at how many users don't
know about the dangers of this feature.
The story of "Field Clipping" goes back to version 6 of Crystal.
It didn't have a name back then. It was just an annoying feature
that you noticed whenever your numeric or currency column wasn't wide
enough to show all of the digits. Crystal would simply chop off
the digits that didn't fit. There was no indication that digits
were missing, so you could spend hours trying to get totals to foot,
only to find out that one total was off by a million or two. I
suppose I wasn't the only person to complain.
The solution came with Version 7. You could finally set Crystal
to show pound signs when a number was too wide, instead of just
dropping a digit or two. But the strange thing was that instead
of just providing the overflow indicator automatically, Crystal added a
new feature called "Field Clipping" so that you could turn it on or off
for any numeric or currency field. Even stranger was the fact
that the default setting was "ON" for field clipping.
Now, I have yet to meet a user who would ever use this feature, let
alone want it as their default. And yet, even with Version 10,
the default for both currency fields and numerics is that you have
field clipping - unless you go down 3 layers into the options menu and
turn it off. Here are the keystrokes to make your new
reports show an overflow indicator by default:
File > Options
Fields Tab
Number Button (you should also do the Currency
Button)
Number Tab
Customize Button
Take out the check mark in the lower left for "Field
Clipping".
This change will affect only new reports that are created from this
point forward. Any existing reports will still have field
clipping until you change the numeric and currency fields
individually. To change an individual field right-click on the
field and select "Format Field". Then click the "Customize"
button and take out the checkmark for "Field Clipping".
Then, if you feel so moved, go to the Business Objects web site and
send them a comment asking them to change the default for this feature
to "OFF" in CRv11.
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
How to use "OR" and still pass a WHERE
clause to your SQL statement:
Ido Millet (author of Visual Cut
and Data Link Viewer) pointed me to an interesting discussion in the
Tek-Tips forum. Ido was experimenting with ways to help Crystal
generate an optimal WHERE clause in a SQL statement. The
challenge was a parameter field with an "ALL" option, which meant that
there was an "OR" in the selection formula. What they discovered
was that reversing the clauses around the "OR" would cause Crystal to
generate a different SQL statement. You can read the full thread
in Tek-Tips (Thread149-813765), but here is the short version:
If you have a parameter with an option to select 'ALL' you might use
the following selection formula:
{?Parameter} = "ALL" OR {?Parameter} = {Database.Field}
This will work fine in Crystal, but will not pass a WHERE clause to SQL
when you select a specific value. This makes for very inefficient
SQL. However, you can reverse the clauses and use:
{?Parameter} = {Database.Field} OR {?Parameter} = "ALL"
Now, when you select a specific value in the parameter (something other
than "ALL") you will get a WHERE clause in your SQL Statement. Of
course, if you select "ALL" in either case there is no need for a WHERE
clause because you are asking for all records. Thanks to Ido for
this tip.
Visual Cut Exports to specific Excel
tabs:
And speaking of Ido, he has added
some new functionality to Visual Cut that some of you might find
useful. You can now export the groups of a report to separate
tabs in an Excel spreadsheet. These could be separate tabs in a
new spreadsheet or they could be additional tabs added to an existing
spreadsheet. For a full explanation of this feature you can
download the User's manual for Visual Cut. See the LINKS page of
my site for more information, or contact me if you would like to
request a trial version of Visual Cut.
Export Limitations for Version 10:
When you export a Crystal report
to another format, you may find that certain features, like lines and
boxes, are not supported in that format. The supported features
vary with each export format, and are improved with each update of the
Export DLL files. Crystal usually publishes an "Export
Limitations" matrix of the features supported for each export
format. They have just released the matrix for version 10.
This matrix is a spreadsheet that shows 75 common formatting features
and which of the features are supported in each export format.
See the LINKS page of my web site (toward the bottom) for a link to
this document. I also have links to older versions of this matrix
on the same page.
Keep Group Together in v10:
In versions of Crystal up through
9, users have noticed a quirk in the way Crystal applies the "Keep
Group Together" option. Namely, when the first group of the
report spans more than one page, the first group will end up starting
on page two rather than on page one. The first page will contain
only the Report Header, and blank space. This has been fixed in
v10 by the addition of a Report Option named "Respect Keep Group
Together on First Page". This property is (wisely) set to
"off" by default for any new report. This means that using "Keep
Group Together" will NOT create an extra page at the beginning of the
report. If, for some odd reason, you prefer the original behavior
you can activate the property for any report.
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