Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide On Location TrainingPublic ClassesIndividual TrainingIntroductory Course OutlineAdvanced Course OutlineFormula ExamplesNewsletter Back IssuesMy BlogResource LibraryConsulting ServicesSupport ServicesContact InformationLinks to third party productsBack to main pageMy Credentials



(For recent updates read my BLOG, The Crystal Reports Underground )

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