The Crystal Reports Underground News - Volume 2004.09 (September 2004)
an independent source for Crystal Reports Information by Ken Hamady
Contents for September, 2004:
** Spam Filters and this Newsletter
** When Crystal tries to be "helpful" (and how to avoid it)
** Public Intro and Advanced classes in Frederick
** A New Access Report Converter
** A New Report Translation Management Tool
** 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
** Improved Rounding to the nearest 5 cents
** Alternate Method for Splitting a Section
** Read back issues at http://www.kenhamady.com/news.html
Spam Filters and this Newsletter:
Several readers have asked me why they have stopped getting the newsletter, and usually it is due to their company spam filter. Those of you who receive the newsletter at work should be aware that spam filters are getting more sophisticated and aggressive, and some are starting to block newsletters like mine. If your company has a spam filter, and you don't want to suddenly stop getting the newsletter, you should ask to have my Email address added to the "white list" of acceptable Email sources. If you have any questions or problems getting my newsletter just let me know.
When Crystal tries to be "helpful" (and how to avoid it):
Crystal is a very sophisticated product, but there are times when it is just too darned helpful. New users are often confused by this 'help' so I have put together a list of what I consider the most common unwanted help features, and how to avoid them.
"Auto-Linking" is a prime example of Crystal trying to help out, and usually making things worse. This feature used to be called "Smart Linking" but I guess even Crystal finally realized the irony of the name. My estimate, based on my own experience, is that it guesses correctly about 30% of the time. I teach my students to turn this feature off in the first hour of my class. Except, of course, in version 9 where you CAN'T turn this off. In v10 you can turn it off, but only if you dig into the registry. If you have version 8.5 or earlier just go into File->Options. You can turn this feature off on the database tab.
Another 'helpful' feature of Crystal is trying save every report with data by default. Saving a report with data can be a very handy feature, and I use it often. However, I don't think it should be the default for every new report. Many new users, even programmers, get confused when today's report looks just like yesterday's, and the day before that. So I have always taught my users to turn this off as a default under File-Options, on the Reporting tab. You can activate it, when needed, on individual reports.
Then there is the more subtle help. For instance if you click on a subtotal to move it or size it, and then you open the Record Sort Expert (Sort Records in v8.x) Crystal will instead open the Group Sort Expert (called TopN in v8.x). Crystal assumes that if you are sitting on a subtotal when you try to Sort, that you want to Sort using that subtotal. Personally, I think users who want the Group Sort Expert are smart enough to select that menu option - especially since it is right next to the one they actually selected.
There is a similar feature that tries to help when you are sitting on just about any field. If you open the Select Expert, Crystal adds a new rule tab using that field. I have watched users delete this rule several times, only to see it return the next time they open the select expert. Crystal assumes that if you are sitting on a field when you open the select expert, you probably want to add a new rule for that field. The added rule is set to "is any value", so it doesn't affect your criteria unless you complete the rule. However, many users don't realize that they can simply ignore the rule and that it will go away on its own.
Another feature affects cross-tabs. If you happen to have the cross-tab selected, and then open the Group Sort Expert, you will get a Group Sort window for the cross-tab, instead of for the report's Groups. In version 8.x the window will at least tell you it is a "Cross-tab TopN", but in later versions you might not realize that it is the Group Sort for the Cross-tab Rows. Of course this only works if you select the entire cross-tab by clicking in the upper left (empty) corner. If you are in an individual cell of the cross-tab this doesn't happen.
This last one is for v9 and v10 users. When you create a formula field you get the choice of using the Formula "Expert" or the Formula Editor. Giving you an option to use the Formula Expert puzzles me, since the Formula Expert can be used only in reports that have custom functions. Most reports do not. The real confusion comes if you accidentally save an empty formula. If you try to Edit the empty formula Crystal automatically defaults to using the Formula Expert, which confuses most users.
Since so few reports use custom functions it makes no sense to default to the Formula Expert. This is especially true in v10 which doesn't even have a repository to store custom functions (unless you install Crystal Enterprise). In both v9 and v10 you can switch from the Expert back to the Formula Editor by clicking the correct button. In v9 the button is obvious since it says "Use Editor" in English. In v10, however, when you end up in the Formula Expert, you have to find a small tool bar button that switches back to the Formula Editor. Look for the button with the magic wand.
Public Intro and Advanced classes in Frederick:
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. 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 1875 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.
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
Access Report converter:
I have uncovered 2 new products this month, and the first is a tool that helps convert MS Access reports to Crystal Reports. Milestone Software in Edmonton has worked for 3 years to develop this conversion utility and they feel it is ready for the market. It converts groups, sections, database references, subreports, text, lines, boxes, images and even some formulas. Formulas that it can't convert are commented out for manual conversion. It works with v8.5, v9 and v10 and with several versions of MS Access. Cost is $395 for Edition and $595 for Enterprise Edition. See the LINKS page of my site for more information.
The one thing that they haven't made clear is the software requirements for running the program. The web site says that you can use this product with v8.5 (no edition specified) but that if you use it with v10 you have to have the Advanced Developer Edition. Look for a clarification before you buy.
Report Translation Assistant:
Another new tool this month is geared to those who have to maintain the same report in several different languages. Crystal Translator, by Softlang (UK), makes the job easier by first extracting all translatable objects (text objects as well as literal text inside formulas, parameters, alerts, etc.). It then allows you to enter translations for each object and stores them in a database. If you later modify the original report and want to translate it again, the database will remember the translations of the original objects and apply them automatically. You only need to translate newly added text. You can even apply these prior translations to new reports, if the text is the same.
In addition to translating text, it also allows you to localize and format dates, times, numbers and currencies. Note that this tool does not help you translate the data of the report, only static text. It also does not do any initial translations. It remembers translations that you provide. For more information see the LINKS page on my site.
You do not need a copy of CR, or any CR skills to translate reports. The cost is $299 for the freelancer edition and $699 for the professional edition.
Improved Rounding to the nearest 5 cents:
Last month I shared a formula that allows rounding to the nearest nickel. Two days later, one of my readers gave me an even simpler and more flexible way to accomplish the same task. Here it is, and thanks to Mike Barron who told me that he found this formula long ago on an Excel forum.
Round ( {database.field} / .05 , 0 ) * .05
What I like most about this approach, other than the sheer simplicity, is that you could replace the ".05" with any other value and the rounding will be based on the nearest increment of that value.
Alternate Method for Splitting a Section:
There are several reasons why you might want to split a section into two pieces. You might want to format one part of the section with different properties, or you might want to prevent objects that "can grow" from growing over the objects below them. The standard ways to split a section both create new space below the existing section. You can do this two ways:
1) Go into Format->Section and click the button marked INSERT at the top of the window.
2) Go to design mode, right-click on the section name and use "Insert Section below".
Because these methods create new space, they require that you move your objects to the new section and resize the original section. The alternative is to use what I call the "knife" tool to divide an existing section space into two pieces. To use the knife you put your cursor along the right edge of the vertical ruler. Your cursor arrow will turn into a divide indicator with arrows pointing up and down. If you click with this indicator on the ruler and hold down your left mouse button, you will see a gray line across the section that you can slide up and down. Put this line where you want the divide to occur and release the mouse button. The section will split into separate sections at that point. Objects that were above or below the line will now be in separate sections.
Many people stumble across this feature by accident when they are trying to change the size of a section. The "knife" indicator is very similar to the section sizing indicator. So, if you are trying to size a section by grabbing the pipe very close to the ruler, you may accidentally split the section rather than size it. That is why I tell my students to size sections by grabbing the pipe in the middle, away from the ruler.
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