phone: (540)338-0194

Individual Instruction by Ken Hamady

The Crystal Reports Underground News - Volume 2007.11 (November 2007)
an independent source for Crystal Reports Information by Ken Hamady

Contents for November 2007:
** Crystal Reports 2008 (Version 12)
** SAP purchases BO/IBM Purchases Cognos
** Free Crystal Reports Viewer (with scheduling and Email)
** Open enrollment classes December 2007
** Free Trial of "Mass Verify"
** PDF eXPLODE fails on Vista
** *NEW* Guide to SQL Expressions, Options and Commands
** Create a formula library in Phrase Express
** Free MICR font (Turn blank security paper into check stock)
** Store a value today and use it in a different report tomorrow
** Read back issues at

Crystal Reports 2008 (Version 12):

Usually a new version of CR is a mixed bag. There are always new features and improvements but there are usually some removed features or things that get worse. So far what I have seen of Crystal Reports 12 is a handful of improvements and nothing made worse. Here are the new things you can do:

1) Add individual non-summary columns to a cross-tab:
You can now manually insert rows or columns anywhere in a cross-tab. These can do calculations using values from other cells in the cross-tab, including other manually created cells. The new values are even available for charting.

2) Make a parameter optional:
Now users can skip a parameter.  There is even a function that allows a formula to check if a parameter was skipped, so it can know what to do. This means we can do things like have users skip a date parameter and have the report use today, yesterday or any other date calculation as the default (skip) behavior.  This feature even applies to dynamic parameters so we now can say a skipped parameter means ALL, or some other default value without resorting to a union query.

3) Change a parameter value without refreshing:
You get to assign parameters to an interactive side panel. Selection parameters can be changed and applied to the saved data, which allows you to select subsets without refreshing. Formatting parameters can change the behavior of the report on the fly.

4) Add sort controls to the report:
These objects allow you to interactively change your sort fields and sort directions in preview mode.

5) Disconnect the size of the design area from the printer:
This is great for those wide reports that are designed solely for export.  We no longer need to configure a fictitious wide carriage printer to get a wide design area.

6) Change the page orientation within a single report:
This is especially handy for subreports that need to be landscape inside a main report that is portrait.

7) Create a duplicate formula or running total:
If you need a series of similar formulas (and I do all the time), just right click on the first one formula and select "duplicate". A second formula is created that is identical except for the name, which will have a number append to the formula name.  Each time you "duplicate" you get the next number in the sequence. I believe that this feature was also available in XIr2.

8) Built-in bar code font:
Right click any "bar code-able" value and have it display the field using a bar code font.

9) Auto-complete for field names in the formula editor:
Just type an open curly bracket in the formula editor to get a list of all available fields. Type the first few characters to narrow the list to field names that start with those characters.

10) Save Lock Password:
Requires the user to enter a password before saving over the original report (from within Crystal).  Allows saving under a new name without the password.

11) Search all report formulas for a field, formula or parameter.
(This should have been added long ago.)

12) Page Break after N Visible Groups.
I see what it does, but it must solve a problem I have never had to solve.

Then there are places where they fixed things that they broke in XI:
    * You can now copy (static) parameters from one report to another.
    * Date parameters now have a decent calendar control again.
    * Date parameters can now use normal date format again.

Then there are the things I haven't played with yet:
    * More powerful XML exports
    * Flash integration
    * Adobe Flex Integration
    * Xcelsius content embedded in reports

Backward Compatibility:
Version 12 reports can be opened by older versions of Crystal Reports,  all the way back to version 9.  Any new features will not appear.  Any new function used in a formula will generate an error.  Surprisingly, a cross-tab with the new manual columns will still run without error in older versions of Crystal.  The manually added columns disappear but no errors are generated.

Licensing changes:
Crystal 12 is still using Named User licensing. That means you need a valid license for every report designer, regardless of how often they use the product. See my recent blog article for more explanation on Named User Licenses.

There is only one edition of Crystal Reports 12 so everyone gets the same tools and rights to deploy applications. Thick client applications can be deployed, as always, without restrictions. Same for deploying server based applications internally. Providing server based apps to your customers requires that each customer own a copy of CR 2008.

The EULA for the CR 2008 BETA had a set of restriction taken directly from the Enterprise license. That made all consulting and training on CR12 a violation of the license.  After I pointed this out a new EULA was posted on the website.  The new license is also included with the product trial download.  I'm not sure if the new EULA made it into the boxed product or not, but you can download the updated EULA from the BO website.

The only remaining restriction is for training. The EULA says that you need BO's written permission if you use the software to deliver training. They have given me written permission with no conditions, but they have yet to explain how the process will work for others.  Stay tuned for more on this issue.

SAP purchases Business Objects / IBM Purchases Cognos:

Crystal Reports has changed hands again, this time BO was swallowed by yet a bigger entity - SAP. This is considered a friendly takeover so it will remain to be seen if/how this affects the Crystal product line. The general opinion is that it is a good fit. And SAP has decided that BO will operate as a separate unit so I don't expect any significant changes in the near term.

On a related note - IBM just purchased Cognos this week and Hyperion was recently purchased by Oracle.  So the big 3 BI vendors are now all part of something bigger.

Free Crystal Reports Viewer with, Scheduling and Email Distribution:

SaberLogic of Wadsworth, OH has developed a new Crystal Reports viewer. It allows reports to be refreshed, scheduled, exported, printed and even sent out via Email. It is called Logicity and is compatible with all versions of Crystal from 8.5 through XI. They have released both a free version and a PRO ($45) version with extra features.  My readers get a discount on the PRO version till 12/31/07.  See my BLOG article for details. 

Open enrollment classes December 2007:

You need information to run your business, so stop struggling with Crystal Reports and learn how to use it fully.  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.  We even include material on cross-tabs, charts and 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 mid-December 2007.  See my web site for more details or call me at (540) 338-0194.

So what makes my class different?  I have written my own course materials and have used them to teach over 2,000 satisfied students.  And, I give you a toll-free number so you can call me after class with questions.  

Or, if you want to schedule a class at your office, using your data, that is my specialty.  I have even found several other top-notch instructors around the US, UK and Canada that can deliver my class at your location for a very competitive price.  Call or visit my web site for more details.

Mass Verify now has a Trial Download:

David Martinko of Redeemed Software has added a trial download for Mass Verify, and encourages you to try it.  Mass Verify performs the "Verify Database" step for multiple reports in one step.  See my LINKS page for more info and a discount.

PDF eXPLODE fails on Vista:

Not all of the third party utilities I use are are fully compatible with Vista. Some need to have their compatibility settings adjusted to be run in XP mode. Some need to be run as Administrator. Some do not run at all. For example, PDF eXPLODE does not currently work on Vista (but they are working on it).

If you run into any compatibility issues with Vista and Crystal Reports or a Crystal utility, I would love to hear about it.

Coming! The Expert's Guide to SQL Expressions, Options and Commands

Use these SQL tools to improve performance, solve data problems and add capabilities to your queries.  This new guide, ready in January, covers all versions from 8.5 through 12.  It shows when and how you can change the reports SQL, and how to use your own SQL as the basis of a report.

In the section on SQL expressions I show 16 different SQL calculations that you can pass directly to the database.  These include string manipulations, boolean tests, data type conversions and date calculations.  Many of these demonstrate useful functions that are not listed in the SQL expression editor.  I even explain the rules for writing subqueries within a Crystal SQL Expression, something that you won't find documented anywhere else.  And each of these expression examples is shown in 4 different SQL flavors (SQL Server, Oracle, MS Access and MySQL).

If this is something that interests you let me know and I will notify you as soon as it is ready.

Create a formula library in Phrase Express:

I have been testing out a handy little utility called Phrase Express for a few months. Phrase Express lets you store a library of phrases that you can quickly paste into Email messages and other documents. I use it primarily to help with Email phrases that I type several times a day and it is great. It works with any text based application.

So I had to slap my forehead when Tim Garner (College of the Sequoias)and Ido Millet (Millet Software) suggested that I use it for storing Crystal Reports formulas.  You wouldn't believe how often I visit my own web site just to copy a formula. So I took the formulas I use most often and stored them in Phrase Express. I can now paste them directly into the formula editor with a few clicks. I could even assign hot key combinations or 'auto correct' abbreviations to call up my favorites.  You should try it if you have a list of formulas that you use regularly.

It costs only $19.95 and you can try it for free.  Technically you can continue to use it for free for 'non-commercial' use. (I guess that is for those of you who use Crystal Reports strictly for fun.)  I am even working on library of my top 20 formulas to share, so other users can import my library directly into their own Phrase Express library.

So, let me know if you try Phrase Express, what you think of it, and if you are interested in the library.

Free MICR font (Turn blank security paper into check stock):

Many of my customers use Crystal Reports to print checks. This can be a tricky endeavor, especially aligning the report output with the check stock.

One solution is to incorporate all of the check stock information into the report itself and then to print the check on "blank" security check stock paper. Printing the static lines, boxes and text is pretty straightforward. But one part of the check keeps most people from ever trying this.  That would be the MICR characters, (Magnetic Ink Character Recognition). That is the special line of numbers at the bottom of each check. Printing this line requires a special font and a special magnetic toner.

However, one of my readers,Erik Rasmussen, has successfully done this with Crystal Reports and sent me a link for a free MICR Font that you can use to print that line. The font is called GnuMICR and it was created from scratch by Eric Sandeen of Saint Paul, Minnesota. See my BLOG article for more information on using this font.

Store a value today and use it in a different report tomorrow:

Those of you who have worked with subreports know that you can pass a value from the subreport to the main report and back again. But have you ever needed to create a value in one report and use that value in a completely different report? Or maybe you need to store a value from a monthly report and use it in next month's report? Here are 2 ways to accomplish this using 3rd Party dll files:

Chelsea Technologies has just released the Share UFL that allows you to store a value to an external XML file. The value can be then retrieved in any report - even the next instance of the same report. And you can store as many values as you need. A free trial version is available and the cost to purchase is roughly $100.

A less expensive option is to use the CUT Light DLL provided by Millet Software. This has been around for years and I just remembered that it could be used to do the same thing. The main difference is that CUT Light stores your values in an INI file rather than an XML file. CUT Light also provides a dozen other useful functions in the same DLL.

For Links and syntax examples see my BLOG article.

Contact Information

Ken Hamady, MS
525K East Market St.  
PMB 299
Leesburg, VA 20176
(540) 338-0194

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