The Crystal Reports Underground News - Volume 2007.07 (July 2007)
an independent source for Crystal Reports Information by Ken Hamady
Contents for July 2007:
** BO updates the Offline viewer
** My favorite utilities
** Canadian holidays formula
** My library of Crystal Reports guides
** New - Certified associates to teach your on-site classes
** Stop struggling with your reports, let me help
** Free options for desktop sharing
** Finding a stray formula or parameter in your report
** ToNumber vs Val
** Read back issues at http://www.kenhamady.com/news.html
Business Objects updating their off-line viewer:
Recently Dave Sharpe (a former student) forwarded a message to me from BO. BO has announced a BETA program for something called Crystal Reports Viewer XI. This is really an update of their "Offline Viewer" which allows users to view and print reports, but ONLY if the report is saved with data. They apparently don't want to sell a viewer that allows refresh because that would give users an alternative to the more expensive BO server products.
They have even started a forum for feedback from people in the BETA program. Or course the first comments were 'it needs to allow refresh'. The response from the BO forum moderator hints that BO is (finally) considering releasing a viewer that can refresh reports. If they do it will have to be better than the ones that are already out there. Read the next article for info on my favorites viewers.
Formula to generate Canadian holiday list:
One of my most used formulas is my "Business Days" formula which allows you to calculate the number of business days between any two dates. Using this formula requires that you store a list of your holidays for each year. One of my readers (Mike Cook of Shepherd Hardware Products) provided a formula to calculate the common U.S. holidays - even going so far as to provide a formula to calculate Good Friday - a pretty amazing feat. Now one of my Canadian readers (Greg Fendall at net-linx Americas) has provided the Canadian version of the holiday formula. If anyone would like to provide the formula for another country I will be happy to post it. See all of these formulas, and others on the FORMULAS page of my site.
My complete Library of Crystal Reports Materials:
Let a master teacher help you understand these Crystal Topics. Each guide comes with clear explanations and sample files to illustrate each concept.
Expert's Guide to Formulas ($36)
Expert's Guide to Subreports, Parameters and Alerts ($28)
Expert's Guide to Totals ($24)
Expert's Guide to Cross-Tabs ($22)
Expert Techniques Vol. I ($19)
Expert Techniques Vol. II ($19)
Expert Techniques Vol. III($19)
Quick Reference to Crystal Reports in Visual Basic ($16)
Quick Reference to Crystal Reports in .NET ($14)
You will find these on the LIBRARY page of my site.
New "Certified" associates for teaching Crystal Reports classes:
As many of you know I am not a big fan of Business Objects' certification programs. That is why I have never been certified by Business Objects. I also don't require that my associate instructors have certifications. What I do require is Crystal teaching experience and good references. That is why my team gets such enthusiastic feedback from customers and students.
Up to now, any organization that wanted instructors with a BO certificate would have to pay the Business Objects list price for training. So I am happy to announce the addition of 2 new associates to my team who both have certification for Crystal Reports XI. Both of these instructors were "official" instructors sent out by Business Objects. They both taught classes for BO on a regular basis, so you can't get better credentials than that. Of course they also have the experience and references that I require.
Both of these instructor are willing to travel. So, if you need someone certified to teach your next Crystal Reports class, you should still give me a call.
Stop struggling with your reports, let me help:
You need information to run your business, so stop struggling with Crystal Reports and learn how to use it. Come join one of my small classes (3-5 students) and learn how to make Crystal work for you. My Intro class makes sure you know all of the basics, including cross-tabs, charts and even some formulas. The Advanced class shows you how to solve several common reporting problems as well as when to use advanced features like running totals, subreports, parameters and commands. Users of any version are welcome. Classes are held in Frederick, MD and the next round is in mid-August. See my web site for more details or call me at (540) 338-0194.
So what makes my class different? How many instructors teach you from a book that they wrote? How many have taught more than 2,000 students? How many give you a toll-free number so you can call them after class with questions?
If you want to schedule a class at your office, using your data, let me know. That is my specialty. I have even found several top-notch instructors around the US and Canada that can deliver my class at your location for a very competitive price. See my web site for more details.
Free options for Desktop Sharing:
I do quite a bit of Crystal Reports consulting by phone. I recently begun to find it more helpful to make a remote connection to my customer's PCs. So I did some research on the current options for desktop sharing and was surprised to find several FREE options.
My current favorite is Crossloop. It is a free program that any two people can download and install. Within a few minutes either user can "host" a session for a remote user - allowing the remote user to take control of the host PC.
Cross-Loop requires no registration, includes no adware/spyware, has no time limits, is secure and free. All session traffic is encrypted end-to-end. The host has to initiate the connection by giving the remote user a random number generated by the program. When the remote user starts to connect, the host must also confirm that the connection as allowed. Once confirmed, the remote user has shared control of the host desktop.
If you like Webex you can also check out the new AOL/Webex collaboration called AIM Pro. It includes free 2-person Webex sessions and several other nice features. The main difference is that Webex routes all communication through their servers. CrossLoop uses their server only to manage the connection and then tries to make a peer-to-peer connection between the PCs.
Finding a stray formula or parameter field:
Crystal will not allow you to delete a formula or parameter field from a report unless that item is no longer used anywhere in the report. Unfortunately, there are several hundred places where a formula could be used but not visible. These include sorting, grouping, selecting, totaling and the dozen or so formatting formulas available for every report object. Checking all of these hiding places is not practical, so here are two ways to have Crystal help you uncover where the field is being used.
My favorite method is to edit the formula or parameter in question and change it's data type. For a numeric or date formula change the formula to "x". If it is a character formula change it to a numeric value like zero. Then preview the report. Every formula that uses this item in a calculation will now pop up with an error message, and you can fix them one after the other.
However, the first method won't work if the formula/parameter is just sitting on the report or is a sort/group field. You can find these items by exporting the report using the "report definition" format. This creates a text file that lists the location of all fields on the report canvas. You can search this text file for your formula name and see where it is sitting or being used. It also shows the contents of all formula fields used in the report. But conditional formatting formulas are not included in this export in version XI. Some older versions included some or most of the formatting formulas but I have yet to find a version of Crystal where ALL formatting formulas were picked up by the export.
ToNumber() vs VAL():
I often see the ToNumber() function used in Crystal Reports formulas. It is used to convert numeric text into a true numeric value. That tells me that the person who wrote the formula probably doesn't know the advantages of using the Val() function. ToNumber() works, but it will generate an error if the input string has any characters, punctuation or symbols in it. The usual solution with ToNumber() requires that you test the input string first like this:
If NumericText ({field}) then ToNumber ({field}) else 0
However, even this doesn't work well if characters come after the numerics. For instance if you have street addresses that start with numbers, or a list of part numbers like this:
11220
11220C
127
128
128P
129
2458B
All the values above that end with a letter would return a zero with the formula above.
The Val() function is more forgiving and will stop when it reaches an alpha character or symbol. It then returns the value of the numbers up to that point. So the list of parts above would be converted to their numeric values and all the trailing letters would be ignored.
I have found only a few rare reasons for using ToNumber() instead of Val().
1) ToNumber() can work with Booleans and currency values in addition to strings. Val() can only convert strings.
2) ToNumber() can also convert the following strings, with parens or a leading dollar sign, while Val() will return zeros for both:
ToNumber ( "(123)" ) becomes -123
ToNumber ( "$123″ ) becomes 123
Contact Information:
Ken Hamady
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright 2007 by Ken Hamady
All rights reserved - Republishing this material requires written permission