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