phone: (540)338-0194
email: ken@kenhamady.com


Individual Instruction by Ken Hamady

The Crystal Reports Underground News - December 2001

an independent source for Crystal Reports Information
by Ken Hamady, MS

Contents for December 2001:
** The new Protege program for individual instruction
** The Crystal Reports "HotFix" update for v8.5
** Take my class in Philadelphia, Baltimore or Leesburg
** Crystal, the Registry and INI files
** Expert on-site training or consulting
** Formula for finding the first Monday of the current month
** Using "OR" for selection when you may have Null values
** My Quick Reference to Crystal Reports in Visual Basic
** Read back issues at http://www.kenhamady.com/news.html

The new Protege program:

What should you do if you need to learn Crystal Reports but:

    You want to start training right away?
    You aren't near a public class?
    You want to cover specific topics, or work with a specific database?
    You can't take 2 days off and you need a flexible schedule?

You should enroll in my new Protege program for individuals.  This allows you to get the same training and support that I offer to my classroom students, only you work with me individually.  By using a combination of face-to-face, telephone, Email or independent study, you can cover my entire Intro or Advanced class for about the same cost as coming to my public class.  Or you can get an abbreviated course that covers just the topics you need.  Here are the options:

First, the Structured Course ($675).  This is designed to get you through either the Intro or the Advanced material.  It comes with both sets of materials, a lesson plan and 7 hours of lessons and support.  Lessons by phone are most common, but those of you in Northern Virginia can even come to my office.

Second, the Free-Style Course ($190).  This is designed for students that want to cover specific topics.  It comes with one set of materials and 2 hours of support.  Additional time is $95 per hour.  Compare this option to the new Ecourses offered by Crystal Decisions.  Their web site now offers an Ecourse on Subreports for $149.  This covers just one topic and does not include any real-time instruction.  My Free-Style course would include my entire advanced course book, including lessons and exercises on subreports, plus 2 hours of real-time instruction.

Call me for more information at (540) 338-0194 or read more details on my website.


Crystal Reports "HotFix" for v8.5:

You should be aware of a file that you can download called dbexdrvr.zip, also referred to as the Crystal v8.5 "HotFix".  This file is not really advertised because a "HotFix" is like a beta version of a maintenance release.  In other words, it has not received complete quality control testing and therefore it comes with a pretty extensive disclaimer.  You don't want to apply this update unless you already have a problem, but it helps to know what problems can be fixed by this update.  You would typically learn about this file from a call to Tech Support, or possibly from a knowledge base article on Crystal's website. 

Most of the problems affect a small group of users, but some of them have surfaced in my work.  Below, I have listed the fixes that I think would be most likely to affect you, but the full descriptions of all 80+ fixes are in the Readme.txt that is included in the file.  The Readme.txt also lists the most recent builds of 33 key Crystal files.  So, even if you don't apply the Hotfix, the Readme.txt will tell you if there are any fixes for your environment.  Most of the files fixed are export format files (files starting with U2) and database drivers (files starting with P2). 

Note that this file is a work in progress, and is updated regularly.  My guess is that it will eventually become a maintenance release.  Also note that the problems listed below only occur in certain environments, described more fully in the Readme.txt.

Export problems that might occur in certain environments:
* Suppressed columns/rows in cross-tabs resurface in Excel.
* Exports to Excel are distorted when the report exceeds 22.75 inches in width.
* Exports to Excel crash when printing to HP LaserJet 4000 series printers.
* Grouped reports exported to CSV have extra <CR><LF> between the group headers.
* On demand subreports are exported as regular subreports.

Connectivity problems that might occur in certain environments:
* Some ODBC Integer fields add 2 decimal places to the value.
* Some ODBC Numerics are smaller by a factor of 100.
* The SQL Designer may multiply currency fields by a factor of 100.
* Numeric 1 and 0 may not interpret correctly as Boolean True/False.
* The Date() function fails in the Selection Formula for SQL Server native driver.
* The ending date is not included when "Convert Date-Time Field to Date" is used.
* GPF when using the Lotus Notes ODBC driver and creating a date record selection.
* Stored procedures are not available when you are logged in as a non-owner.
* 3rd party ODBC drivers may GPF when the DSN name is a certain length.
* GPF when verifying a Btrieve/Pervasive database.


The Crystal Reports classes I am teaching this Winter:

Click the "Public Classes" button above for dates.
Each class is $675 per student.
Call for more information at (540) 338-0194.


Crystal, the Registry and INI files:

The settings you enter under File - Options in Crystal Reports are stored in different places, depending on your version of Crystal.

For Crystal Reports:
Versions 4.x and prior, in a file called CRW.INI
Version  5.x, in registry key  HKEY_CURRENT_USER\Software\Crystal Software
Versions 6.x - 8.x, in registry key  HKEY_CURRENT_USER\Software\Seagate Software

For Crystal Dictionary Options   (all versions) the file CRD.INI
For Crystal SQL Designer Options (all versions) the file CQW.INI

Whenever you change global settings, it is a good idea to close the program and restart the program (no need to reboot).  Crystal only stores these changes when you exit the program, so this forces Crystal to update the registry or INI file.  Otherwise, if you have to reboot before you exit Crystal, you would have to reenter any option changes that you made during the last session. 

On my website I have mentioned at least 3 changes that you should make to the global options as soon as you install Crystal Reports.  These can all be made through the program menu.  However, there are a few settings that you can't access from within the program menus.  These settings can only be changed from within the appropriate Windows Registry or the appropriate INI file: 
(Note - You should always be very careful when you open your registry):

Setting the number of records read when you "Browse" a field (500 by default).
    The key is "MaxNBrowseValues" in the DatabaseServer Folder
    The "BrowseTimeout" key can also be increased or set to '0' (unlimited time).

Setting the correct SQL syntax for ODBC Drivers not known to Crystal.
    To learn about this read the following knowledge base article:
    http://support.crystaldecisions.com/kbase/c2003023.asp

Controlling "Auto Smart Linking" for Dictionaries and the SQL designer.    
    Adding the following line to the appropriate INI file:
    AutoSmartLinkWhenEnterGL=No


Expert On-Site Training or Consulting:

I have personally taught over 1200 satisfied students in more than 30 states.  On-site training is my specialty, and I charge about half of the "List Price" for Crystal Training. 

Do I know my stuff?  Go to Crystal Reports General forum at Tek-Tips.com and you will find me listed as the top rated Crystal Reports expert out of over 23,000 members:

    http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/100/pid/149

Can I teach?  Over 1200 students would recommend me.  Ask to speak to a recent customer as a reference.  Call for more information at (540) 338-0194.


Formula for finding the first Monday of the current month:

The following formula will print the first Monday of any month, based on today's date.  You could also modify this formula to find the first Monday of a month based on any date field.  Just put your date field in place of "CurrentDate" in both places. 

WhileReadingRecords;
DateVar BOM:= CurrentDate - Day(CurrentDate)+6;
DateVar BOW:= BOM - DayOfWeek(BOM)+2


Selection rules that encounter NULL values with an OR:

If you need to check 2 fields to see if either matches a parameter value, you can use a simple selection formula like:

{?param} = {field.one}  or
{?param} = {field.two} 

However if these fields contain null values, the formula won't work, because the formula will stop reading at the null value.  That means that the report will not include records where the first field tested is null.  The solution is to use the following selection formula:

( If IsNull ({field.one})
  then False
  else {?param} = {field.one} )
     or  {?param} = {field.two} 


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 different integration methods.  It also gives syntax examples for the most important commands in the 3 most widely used integration methods.  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



Ken Hamady, MS
525K East Market St.  PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com 


Copyright 2001 by Ken Hamady
All rights reserved - Republishing this material requires written permission