The Crystal Reports
Underground News - March 2002
an independent
source for Crystal Reports Information
by Ken Hamady, MS
Contents for March:
** Crystal v9 going into Beta testing
** Adding Functions to Crystal with UFLs
** Checking your SQL statements against the ISO standard
** Expert on-site training or consulting
** Crystal Decisions Sues Actuate for false advertising
** New add-on products for Crystal Reports
** Public classes in Leesburg, Baltimore and Philadelphia
** Page numbers for
Cross-tabs
** The effect of dashes on the VAL() function
** My Quick Reference to Crystal Reports in Visual Basic
** Read back issues at http://www.kenhamady.com/news.html
Crystal V9 goes into Beta testing in March (maybe):
This was mentioned at a user's group
meeting, but the date is soft. Crystal Decisions usually tries to
come out with a major release each year. Since v8.5 was released in
March 2001, v9 should be announced before summer. The (very brief)
description of features given at the meeting included an object
library that allowed you to re-use components in other reports.
They also hinted at the ability to use more complete SQL within the
report designer.
Adding Functions to Crystal with UFLs:
The function list in the Crystal formula
editor is open ended. You can download additional functions, or
even write your own custom functions. If you install them
correctly, Crystal will recognize them and add them to the formula
editor. Many specialized functions can be downloaded for free from
Crystal's web site, or from other sites on the web. These functions
are referred to as UFLs or User Function Libraries.
The UFL files are DLLs. The file names originally started with UFL,
but most now start with U2L, and some newer ones start with UF5 or
U25. For example, take the the function to convert a text string
into "title case", capitalizing the first letter of each word. The
file you need for this is named U2Lcaps.dll.
To use a UFL you must put it into the proper folder on your
computer. The folder you use depends on your operating system. If
you aren't sure, search for files called U2L*.dll on your PC.
Crystal installs with a few UFLs out of the box, and puts them in
the correct folder. You add more UFLs by putting them into this
same folder. On most computers the folder would be one of the
following:
c:\Windows\System
c:\Windows\Crystal
c:\WinNT\Crystal.
Here are some other handy functions that you can add with UFLs:
GetWord - Allows you to identify the Nth word in a string
NumberToDate - Converts numbers like 20,011,231 into 12/31/2001,
a true date
DayOfYear - Converts 12/31/2001 into 365
Many metric conversion functions for Meters, Liters, Grams, etc.
Knowing about UFLs will also help you troubleshoot reports. Often a
report will work on one PC, but cause errors when moved to another.
If the error is a formula error then the problem could be a UFL that
was installed on the first PC but is missing on the second. Usually
this will generate an error like "The remaining text does not appear
to be part of the formula". Some software vendors provide special
UFLs to make Crystal work better with their applications. These
include PeachTree, Platinum, and most BarCode Fonts. Moving reports
that use these functions requires locating and moving the UFL.
There is an excellent document on the Crystal Decisions Web site
that explains UFLs in more detail. It lists many UFLS, but some of
those listed are already installed with the current version of
Crystal, like Square Root. Some of the others are obsolete, like
Store/Fetch. But there are a few that you might find handy. The
document is called:
cr_ufls.pdf
It can be found by searching by file name at the following link:
http://support.crystaldecisions.com/library/
There are also two independent sites that list UFLs:
http://www.crystalinfo.net/cr_ufl.html
http://home.planet.nl/~gdeboom/Report1.html
Programmers can even write their own UFL functions in VB, C, or
Delphi. This allows you to add your own functions to the Crystal
Formula Editor. The steps to create a DLL can be found in the
Technical Reference guide that comes with the Developer Edition of
Crystal.
Test any SQL statement against the ISO Standard:
This isn't strictly about Crystal, but
most of you are in a SQL environment and might find this
interesting. The site listed below allows you to run your own SQL
statements through a filter and have it analyzed for compliance with
the ISO standards for SQL. This helps you gauge the "portability"
of your statements - the ability to run this statement in another
SQL environment. They also have a table that lists the proprietary
SQL words used by vendors such as Oracle, SQL Server, DB2 and MySQL.
http://developer.mimer.com/validator/index.htm
Expert On-Site Training or Consulting:
I have personally taught over 1300
satisfied students in more than 30 states. Training on location is
my specialty, and I charge about half of the "List Price" for
Crystal Training.
Do I know my stuff? Check out my ranking in the Crystal Reports
Forum at Tek-Tips.com. I have been consistently voted their top
Crystal Reports expert out of 23,000 members:
http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/100/pid/149
But, can I teach? Many students have said that my classes are among
the best technical classes they have taken. Ask to speak to a
recent customer as a reference. Call for more information at (540)
338-0194.
Crystal Sues Actuate:
Crystal has filed a lawsuit against
Actuate alleging false advertising based on an Actuate marketing
campaign. There are press releases on both web sites, each giving
their spin to the case.
Neither company would discuss the specific claims in question.
However, I did download a PDF from Actuate's web site that compares
Actuate to Crystal Enterprise. This document may contain some of
the claims in question. One Crystal representative described the
PDF as "ridiculous". I found it interesting. Especially, since the
first selling point mentioned in Actuate's document (reusable
components) was also the first item mentioned by the Crystal team as
an enhancement in CRv9.
The court will decide if this document, or others, qualify as false
advertising. If you want to examine the document for yourself, you
can download it from Actuate's web site. You might have to register
with your contact information to access it:
http://www.actuate.com/download/CrystalCompetitiveGuide.pdf
New Products that work with Crystal Reports:
I try to keep a list on my web site all of
the 3rd party products that work with Crystal, even those that are
sold by my competitors. In the past month I have discovered 5 more
products and have added them to my web site on the "Links" page:
Fast Planet Viewer - a standalone report viewer
Reportal - A web-based report viewer/manager that uses
the Crystal WCS
rptMart - A LAN based report manager
Report Analyzer - Report Documentation Manager
DocAssist - Report Documentation Manager for Crystal
Info/Enterprise
See my links page for more info:
http://www.kenhamady.com/bookmarks.html
Don't take just any Crystal class, learn from an Expert:
Click the "Public Classes" link above for
Dates
Classes are $675 per student.
Or, call for more information at (540) 338-0194.
Getting page numbers for Cross-tabs:
You may have noticed that when you place a
large cross-tab in the report header, there may be no page numbers
until the cross-tab is complete. This is because the page number is
in the page header/footer and these sections have to wait until the
Report Header has completely printed. So, if the cross-tab takes
up 3 pages, you won't see page numbers until page 4.
The workaround is to create a "dummy" group that contains all of the
records in the report. You can then use this group's header in
place of the report header. Here are the 3 steps:
1) Add a formula that returns a constant value, like zero, for every
record. Crystal won't allow you to group on a formula that is a
literal constant (like 'x'). So, one technique is to take any
database field that is a numeric or a date and subtract it from
itself to get a column of zeros. Make sure that the field you
select is filled in for every record.
2) Add a group on this field, and make it the primary group of the
report. This won't change the way the rest of the report works, but
it will add a Group Header to the report that only prints once. It
will behave just like a Report Header, except that it will print
between the page header and page footer on each page. You should
suppress the Group Footer for this group.
3) Move your Cross-tab from the Report Header to the new Group
Header. The cross-tab numbers will be the same since this 'Group'
contains all records. The only difference is that you have page
numbers on each page of the Cross-tab.
The effect of dashes on the VAL() function:
All of my students learn how to use the
VAL() function, which converts numeric characters into true
numerics. The VAL() function works by starting at the left of the
string, reading the digits, and stopping when it hits the first
alpha character. It then converts the numeric characters it has
found into numbers. However, if it finds a dash before it finds an
alpha character, it flips the sign of the numeric result to negative
and keeps reading the digits.
The problem comes when the dash is among the numeric characters.
For instance a string might be "Suite 12-14". The VAL() function
would convert the suite number portion into a negative value,
-1214. To prevent this, use the Replace() function within the VAL()
to eliminate the dashes and replace them with either "" (empty
string) or 'x'.
Use "" if you want the VAL() to include the numbers after the dash
in the result.
Use "x" if you want the VAL() to ignore the numbers after the dash.
Below is the syntax using the 'x':
Val( Replace ( {Customer.Address2} [ 7 to 15 ] , '-', 'x' ) )
VB Quick Reference Guide:
If you plan on incorporating Crystal
Reports into a VB application, you should get my Quick Reference
Guide. It gives an overview of the integration methods, with syntax
examples for the most important commands. It comes with some source
code examples, including a sample report viewer. It is only $16 and
can be Emailed to you within 24 hours. Follow the link below for
more details:
http://www.kenhamady.com/vbref.html
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 2002 by Ken Hamady
All rights reserved - Republishing this material requires written
permission