Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
The
Crystal Reports Underground News - Volume 2008.01 (January 2008)
an independent source
for Crystal Reports Information by Ken Hamady
Contents
for January 2008:
** NEW
- The Expert's Guide to SQL Expressions, Options and Commands
** Things
you wont find in Crystal Reports 2008:
RDC (COM) integration
Sample MDB
VB UFL functions - maybe
** BO price
increases
** Open
enrollment classes January 2008
** The
index that ate my data
** Getting
vertical lines to stop at the bottom of a group
**
Suppressing vertical lines based on a condition
** Read
back issues at http://www.kenhamady.com/news.html
NEW - The
Expert's Guide to SQL Expressions, Options and Commands
Use
these SQL tools to improve performance, solve data problems and add
capabilities to your queries. This new guide covers all
versions
from 8.5 through 12. It shows when and how you can change the
report generated SQL, and also how to use your own SQL as the basis of
a report.
In
the section on SQL expressions I show 16 different SQL calculations
that you can pass directly to the database. These include
string
manipulations, boolean tests, data type conversions and date
calculations. Many of these demonstrate useful functions that
are
not listed in the SQL expression editor. I even explain the
rules
for writing subqueries within a Crystal SQL Expression. And
each
of these expression examples is shown in 4 different SQL flavors (SQL
Server, Oracle, MS Access and MySQL).
There are
over 40 pages of material plus sample reports. The cost is
only $26 for an immediate
download.
Things
you wont find in Crystal Reports 2008
Below
are 3 things that you don't get when you upgrade to Crystal Reports
2008. For more of what is missing in CR 2008 see the very bottom of the
"What's New" (PDF) document.
RDC
(COM) integration
If
you are planning on deploying Crystal Reports within a COM application
then you should NOT upgrade to Crystal Reports 2008. The Report Design
Component (RDC) is no longer supported starting with CR v12 (or
2008). The .NET SDK documentation and Merge Modules/MSI files
are
still available but they aren't on the CD. They are now a
separate download on the CR start page.
Sample
MDB
Crystal
Reports 12 no longer includes the sample MDB that has been provided to
users ever since I started working with Crystal 12 year ago. You still
have sample reports but they all read XML data that is stored on the BO
web site. You can download the XML data but I still prefer the MS
Access format because it can be made to behave just like the normal SQL
based data that everyone uses. It is what is used in most of the BO
Knowledgebase examples and in all of my books. Fortunately you can
still download a copy of the MDB. See my BLOG
for details.
VB
UFL functions - maybe
I
just noticed this on Friday and it may be a bug. CR 2008 fails to read
previously available Custom UFL Functions. I have tried this on two
PC's. In CRv12 the node for Visual Basic user functions (u2lcom.dll)
doesn't show up in the function tree under "Additional Functions".
However those functions do appear on that same PC in all previous
versions of Crystal.
If
you get a different behavior please let me know. In the meantime, if
your reports rely heavily on UFL functions, you may want to confirm
that your functions work in CR2008 before you lock in on the upgrade.
BO
Price Increase (everything but Crystal Reports
According
to a recent press release, Business Objects is planning a 10% price
increase for most of their product line as of January 2008.
It
doesn't affect Crystal Reports (and Xcelsius) but does affect most of
the other products and web based training. They are also
eliminating 'standard' support. The lowest level of
support
will be 'corporate'. I have read speculation that
this is
related to the SAP acquisition.
Open
enrollment classes January 2008
You
need information to run your business, so stop struggling with Crystal
Reports and learn how to use it fully. 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.
We even include material on cross-tabs, charts and 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
January 22-25. See my web site for more details or call me at
(540) 338-0194.
So
what makes my class different? I have written my own course
materials and have used them to teach over 2,000 satisfied
students. And, I give you a toll-free number so you can call
me
after class with questions.
Or,
if you want to schedule a class at your office, using your data, that
is my specialty. I have even found several other top-notch
instructors around the US, UK and Canada that can deliver my class at
your location for a very competitive price. Call or visit my
web
site for more details.
The
index that ate my data
Indexes in
the database normally improve the performance of a report. But I have
also seen indexes cause a report to misbehave.
I
recently ran into a simple report listing account numbers.
With
no criteria account "X" displayed 2 records. But when we added a
selection criteria to select account "X", only one of the records
remained. The account numbers were identical and there were no other
report features that would affect the number or records returned. When
we added the criteria and used saved data both records remained, but
when it was refreshed there was only one.
That
told me the database wasn't doing the right query and I knew where to
look first. When some databases get a criteria from Crystal they try to
optimize the filtering by using an index. But I have found that on rare
occasions that the index selected by the database engine won't include
all records. In these cases I find a Crystal function I can add into
the selection formula that doesn't change the result of the
criteria. What it does do is prevent the rule from being
passed
to the database. In most cases this makes the selection process less
efficient, but in some cases this is the only way to get the correct
data.
When
the user saw that the problem was the index he said he would change the
index and sure enough, when he changed the index from descending to
ascending, the original report worked correctly. This report read
Visual Dbase through ODBC and I have seen this same behavior several
times in PeachTree Accounting (Btrieve). So, if you are ever run into
this behavior you now know one more thing to check.
Getting
vertical lines to stop at the bottom of a group
Many
people want to have vertical lines that run through all of the detail
records in a report and so they draw lines that cross over the details,
going from header to footer. But a problem arises when the details
carry over a page break. Lines that cross over the Details section may
extend below the last Detail of the page, down to the page footer. This
is especially likely to happen when you have a deep Details section.
This is because a deep section may not fit at the bottom of the page,
leaving a large white space. Lines that cross over the Details section
will continue through this space.
The
alternative approach is to have the lines contained completely within
the Details section. This way the lines will appear only where the
details sections appear, and not between them. The tricky part is
making one short line within a single Detail section seem like a
continuous line. There are three steps to make this happen.
1) In
design mode, draw a short vertical line that is completely within the
details section.
2)
Right-click on the line and select "Size and position". Set the "Y"
value to zero. This ensures that the top of the line touches the top of
the section.
3)
Right-click on the line again and select "Format > Line". Check
the
property that says "Extend to bottom of section…". This
ensures
that the bottom of the line touches the bottom of the section.
Now each
line will touch the line above and the line below, giving the
appearance of one continuous line.
Suppressing
vertical lines based on a condition
One
of my customers asked if there was a way to do this. There isn't a way
to do this directly, but I played around and found a way to control the
visibility of vertical lines, horizontal lines and boxes. Below are the
steps for controlling all lines and boxes located between the Page
Header and Page Footer. The same method can be adapted for other
locations.
1)
Create a new Page Header below the ones you have and set it to
"Underlay". Make it just big enough to reach the top of the page footer
n preview, but be careful not to make it too big or it will generate
errors and crash crystal.
2)
Place a large empty text object into this section and set its
background color to white. The text object should be deep enough to
fill the new page header and wide enough to cover the lines and boxes
that you want to control.
3)
Place the opposite of your suppress condition in the suppress formula
of this new section. When the new PH is suppressed the lines and boxes
will be visible. When it is NOT suppressed it will cover the lines and
boxes, making them invisible.
The
funny thing is that this text object won't hide any fields, pictures or
other text - just lines and boxes. But these other objects can be
suppressed with their own formulas. Lines and boxes don't have their
own suppression formula buttons so this workaround will help.
If
you have trouble managing the lines and boxes on a page, don't forget
that I can be 'rented' for as little as 15 minutes to help out. And if
you like tips and tricks like this, don't forget to check out my Expert
Techniques materials.
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
2008 by Ken Hamady
All rights
reserved - Republishing this material requires written permission