The Crystal Reports Underground News Volume 2010.03
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for March 2010:
** Desktop Based Scheduling Engines
** Changes coming for the SAP/BO certification exams
** Advertising on Ken Hamady.com
** Finding all uses of a variable
** Reporting on the Database Schema
** Learn Crystal Reports (without needing a bailout)
** Superscript numbers in a Crystal Report
** Vanishing data in Btrieve
** My library of Crystal Reports materials
** Screen Problems in the Standard Report Wizard
Desktop Based Scheduling Engines
How would you like your reports to be automatically run, exported to a PDF and delivered to your Email InBox every Monday morning at 6am? The Crystal Reports designer doesn't provide a way to do this (unless you upgrade to CR Server or BO Enterprise). But if you look at the third party products on my LINKS page you will find several reasonably priced (or even free) tools that do this. Some do even more, like Emailing each page to a different person.
My blog now has a post that compares desktop scheduling tools, providing you with their core features and prices so that you can narrow down your search. My next newsletter will cover the more expensive server-based scheduling tools, but if you think one person can manage all the scheduling you are probably fine with one of the desktop tools – regardless of the number of recipients.
The tools being compared are:
Crystal Delivery by Groff Automation
Logicity Pro by Saber Logic
EasyView by Easy Street Software
Report Scheduler Pro 9.0 by Crystal Desk
CView Manager by Chelsea Technologies
Visual Cut by Millet Software
Report Runner Batch by Jeff-Net
My blog provides a brief rundown of what each product does, and also provides a feature comparison matrix (PDF) that compares specific features, and shows the pricing and the install base.
If you have tried one of these products, or are currently using one, I would love to get your feedback – on or off the record.
Changes coming for the SAP/BO certification exams:
Most of my readers know that I don't think much of the various Crystal Reports certifications that have been offered over the years. But for those of you find these valuable, you should know that SAP is changing the Business Objects certification exams to align them with the SAP exams. You can read more about it on this blog post by Kenneth Schieffer.
The summary is that there will be only one 3-hour exam for each product line (Crystal Reports, BO Enterprise and Web Intelligence). Passing the exam now makes you an "SAP Certified Application Associate. And if you are only partially through the current exam process you have until April to complete the exam under the old scheme. Otherwise you have to start over with the new scheme.
Thanks to Gordon Portanier of Crystalize in Canada for pointing this article out to me.
Advertising on Ken Hamady.com
I have decided to sell some ad space on some of my more popular pages. There is a flat monthly fee for each group of pages, based on their visitor history. So if you have a product that you would like to promote to my visitors, give me a call.
Finding all uses of a variable
Starting with v11.5 Crystal has made it easy to find all uses of a field within the formulas of a report. Just right click on the field (database, formula, parameter, etc) and select "Find in formulas". Even for those of you using older versions of Crystal there is a workaround in my Expert Techniques vol. 1.
But finding where a variable is used is a bit trickier. Unlike fields, there is (currently) no central list of all the variables used in a report. But I just noticed a feature that must have been in the formula editor for at least 2 years. Open a formula and click on the binoculars on the tool bar. This allows you to search a formula for text and even do global replaces within the current formula. Now there is an option in the list that says "All Formulas". This will allow you to search all formulas for the existence of any string. It will list all of the occurrences in the bottom of the window allowing you to click on each occurrence to open that formula in edit mode.
There are two limitations. It doesn't include formulas in the subreports. These have to be checked individually. It also doesn't allow you to do a global replace in one stroke across formulas like it does within a single formula. A global replace has to be done in each formula. But even with those limitations it is much better than the old method of exporting to "Report Definition" and searching through the text file.
Reporting on the Database Schema
Most databases are designed with special tables or views that are designed to store the structure of the database. Each database platform is different but the list below should be a start. To get a list of tables you can use a command object that says the following:
Oracle:
SELECT table_name FROM all_tablesSQL Server:
select name from sys.objects where type = 'u'MySQL: write a SQL command that says simply:
SHOW TABLESor you could use:
SELECT * FROM INFORMATION_SCHEMA.TABLES
For more on MySQL System tables you can use this link.
MS Access :
SELECT * FROM MSysObjects where type = 11) You have to set the Access database options to "show System tables"
2) Then in CR options check off "show system tables".
For more on MS Access system tables you can check out this link.
Older databases like early versions of Dbase, FoxPro or Btrieve store each table as a separate file in the Windows file system. Since you can use CR to read the files in a Windows folder you can generate a list of tables using the files. You can also report on the file size and the last time it was changed, which are not as easy in the others.
And thanks to William Chadbourne of OIT-DEP in Maine for correcting my original Oracle command.
Learn Crystal Reports (without needing a bailout)
Even with budgets tight there is no need to miss out on Crystal Reports training. You still need information to do your job, so stop struggling with Crystal Reports and learn what it can do. The most cost-effective way to be taught Crystal Reports is in my individual training program. It is ideal for people who:
Can't take 2 days off for a regular class.
Want to learn in their own database.
Need to cover only a few topics
Want to learn from someone who has taught 2,500 satisfied students.
Remember, the cost of a typical 2-day Crystal class is enough to buy 7 hours of individual instruction time with me. And this is one-on-one, hands-on training - not a webinar. Start with a purchase of only 2 hours and get my course material with exercises for free. Do as much of the work as you want on your own, then use your prepaid time to work with me by phone and shared desktop. I can review lessons, answer questions, or even help you create reports. For more details see the "Individual Training" page on my web site.
And, if you want to schedule a class at your office, using your data, I can save you money there as well. Along with myself I have discovered some top-notch instructors all over the US, UK and Canada. All of us can deliver a class at a very competitive price. Call for more details.
Superscript numbers in a Crystal Report
One of my clients just sent me a formula that turns the numbers from 1 to 20 into superscript characters. You could do the same thing with subscript characters if you wanted to, but you would need to look up all the Unicode values in a chart like this one. Note that you must display this formula using a Unicode font (they usually say "Unicode" in the font name) or the characters will space and size inconsistently.
select totext({Number.Field},0)
case "1" : ChrW (185)
case "2" : Chrw (178)
case "3" : Chrw (179)
case "4" : ChrW (8308)
case "5" : ChrW (8309)
case "6" : ChrW (8310)
case "7" : ChrW (8311)
case "8" : ChrW (8312)
case "9" : ChrW (8313)
case "10" : ChrW (185) + Chrw (8304)
case "11" : ChrW (185) + ChrW (185)
case "12" : Chrw (185) + Chrw (178)
case "13" : Chrw (185) + Chrw (179)
case "14" : ChrW (185) + ChrW (8308)
case "15" : ChrW (185) + ChrW (8309)
case "16" : ChrW (185) + ChrW (8310)
case "17" : ChrW (185) + ChrW (8311)
case "18" : ChrW (185) + ChrW (8312)
case "19" : ChrW (185) + ChrW (8313)
case "20" : ChrW (185) + Chrw (8304)And thanks to Melody Mulligan of Crystal Clear Solutions for the tip.
Vanishing data in Btrieve
Last week a customer called me after they did an upgrade of PeachTree Accounting. It seems that the records in the report that used to sort were now appearing in random order. As soon as I added the sort back in and refreshed the report, all the records disappeared. Fortunately I had seen this before because I work quite a bit with PeachTree and PeachTree uses Btrieve as a database engine.
This week I had another Btrieve customer (not PeachTree) who wanted to add a parameter to select a part number. We added the rule in the selection formula and, again, all the records disappeared. Same problem same solution.
Apparently when Crystal sends a query to a Btrieve engine, Btrieve tries to 'help out' by tapping into it's indexes. This can happen when you add a rule to the selection formula, or when you sort or group the data. In most environments indexes are helpful but for some reason Crystal and Btrieve indexes don't always play nicely together. When this happens you will see incomplete data returned, or more likely no data returned. You then have to change the request coming from Crystal so it doesn't include any of the things that will invoke the indexes. There are two solutions:
1) Go into File – Report Options and look for the property "Use indexes or server for speed". If it is checked then take out the check mark. This is supposed to tell the database to ignore the indexes. It should work in most cases, but can slow down some reports.
2) An alternative is to take the field you added (the one that caused the problem) and replace it with a formula that uses a Crystal function. For instance if the original field is a number I usually put it inside the Round() function like this:
Round ( {MyNumber.Field} )If it is a character field I use the ToText() function like this
Totext ( {MyText.Field} )Surprisingly, the ToText function doesn't complain when used with a character field. It doesn't actually change the value of the field, but ToText(), like Round(), is processed in Crystal after the data is returned. That means you can filter, sort or group on this formula and the indexes never get involved.
This is just one of the tricks I have learned reporting from Btrieve databases like PeachTree. If you have a PeachTree or Btrieve challenge, give me a call.
My complete library of Crystal Reports materials
Do you struggle with subreports? Are you curious about cross-tabs? Why not let me explain these Crystal Topics to you with one of my Expert's Guides. Each guide comes with clear explanations and sample files to illustrate the concepts.
Expert's Guide to Formulas ($36)
Expert's Guide to Subreports, Parameters and Alerts ($28)
Expert's Guide to SQL Expressions, Options and Commands ($26)
Expert's Guide to Totals ($24)
Expert's Guide to Cross-Tabs ($22)
Expert Techniques Vol. 1 - 4 ($19 each)
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.
Screen Problems in the Standard Report Wizard
I like using the "report wizard" to create the first draft of a new report. It was even better back in v8.5 when you had the report expert and could loop through the steps several times. But even now I find the wizard helpful. Not only can you quickly assemble a simple report, but it automatically gives you a nice evenly spaced column layout. And, it does a few things for you that you are very likely to do anyway – like adding a page number or today's date.
Yesterday I was working with a customer in version 11 (XI) and we noticed a strange behavior in the wizard. We went one step further than we wanted to and hit the 'back' button in the wizard to go back a step. We ended up losing part of the wizard window on the right and couldn't get it back. I ended up clicking "Finish" just so we could get out and we did the last steps through the normal menu.
I found out that this is a known issue in some builds of XI. There is an article on the SAP/BO support site but fixing it involves a registry edit. If you have this issue and want to resolve it you can go to the NOTES section of the BO support site and look for article # 1246536 entitled:
"Standard Crystal Report creation wizard screen shrinks from right side when the 'Back' button is clicked".
Removal Instructions
I have sent you my newsletter because you or your company are one of my consulting or training customers. If you don't wish to receive the newsletter you can use the link below.
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 2009 by Ken Hamady
All rights reserved - Republishing this material requires written permission
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 2010 by Ken Hamady
All rights reserved - Republishing this material requires written permission