Contents for November 2010:
** Crystal Reports Function Libraries Compared
** Open enrollment Crystal classes in Frederick, MD
** Shortcut Keys in Crystal Reports
** Using Autosave in Crystal Reports
** Let me create your Crystal Reports
** Exports to ODBC and null dates
** My library of Crystal Reports materials
** When Crystal is too helpful

Other recent blog articles:
    Restore Characters Per Inch prompt in text exports
    Crystal Reports Service Pack 3 and saved data problems

Crystal Reports Function Libraries Compared

Crystal reports formulas are already very powerful.  But you can make them even more powerful by installing a User Function Library (UFL) to add additional functions.  This article will introduce you to several available UFLs that will give your formulas super powers to let you do new things with your reports.

Lets start with my top 10. The first three are features I use regularly.  My blog post has the full feature matrix with all 60 available functions. 

With these added functions your formulas can:

1) Carry a value from today's report to tomorrow's, or from one report to another.
2) Append a line to a text file, like creating a log.
3) Automatically copy a value to the clipboard.
4) Check the login ID of the user running the report.
5) See if a file or folder exists on your network or on the Internet.
6) Rename, copy or delete a file on your local or network driver.
7) Launch an application or run a batch file.
8) Execute a SQL statement.
9) Send an Email using information in the report.
10) Create a table of contents or an index for your report.

These UFLs are available from four sources and sometimes the same function is found in multiple UFLs. The vendors are:

Bjarke Viksoe (U2lwin32)
Millet Software (Cut Light)
Chelsea Tech (File Mgt, Text, and others)
CrystalKiwi (Table of Contents)

The surprise is that the prices for these UFLs are inverse to the number of functions they provide.  The UFL that provides the highest number of functions is completely free.  The next highest function count is only $50.  The other two vendors sell several very specific UFLs with a handful of functions each.  These are mostly priced at around $100.

Open enrollment Crystal classes in Frederick, MD

Stop struggling with Crystal Reports and learn how to use it fully.  Come join me in one of my January classes 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 reporting problems with running totals, subreports, parameters and commands.  The material is good for any version.  See my web site for course outlines and dates.

So what makes my classes different?  I have written my own course materials and have used them to teach over 2,500 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.  And I have several top-notch instructors in the 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.

Shortcut Keys in Crystal Reports

There are lots of things that can only be done effectively with a mouse.  But I started using computers before they came with mice so I can do more than most users without taking my fingers off the keyboard.  That is why I love shortcut keys and mnemonics.  Just about everyone knows Ctrl-C for copy and Ctrl-V for paste, but there are plenty of others.  There are even some that are unique to Crystal Reports, although they aren't always easy to find.  Here are three ways to find the available shortcut keys in Crystal Reports:

1) Click a main menu word like "Edit".  The menu drop-down will show you any available key shortcuts on the right of each menu option.  Not all menu items have a shortcut but many do.

2) The buttons on the main toolbars have tool-tips to explain which feature is tied to each button.  The tool-tips appear when your mouse is over each button.  Starting in v11 and v12 the tool tips will also show you the keystroke shortcut for that feature, if one exists.

3) Within the formula editor you also get tool tips when you "mouse over" a toolbar button, like the binoculars.  A tool tip will appear with a shortcut key combination (if one exists).  For instance hitting Alt-S will save the formula without exiting the editor, and Alt-C will check the current formula for syntax errors.

Similar to shortcuts are things called mnemonics.  These are the underlined letters in each menu item.  To use these you click the ALT key to activate the menu and then hit the underlined letters in the correct sequence.  For instance if you select a row of objects on the report you can click  Alt / A / L / B .  This takes you to Format / Align / Bottoms which will align the bottom of all of the objects to the one that has the focus.  Unfortunately one of my favorite mnemonics,  the one for "same size" (Alt/A/I ) hasn't worked since version 10 due to a bug.

Using Autosave in Crystal Reports

Autosave is a nice feature that has been around since v8.5.  So you may wonder why have I never mentioned it before.  I think it is because I started using CR before the autosave was introduced and I learned the hard way.  Now I have a solid habit of saving early and often, usually after any major change and especially before I refresh a new version of a report.  So when a customer complained that he couldn't find his autosave files I had to turn the feature on for the first time, just to see how it worked.  I found three things that might confuse you:

1) Once you exit Crystal Reports any autosave reports that you had are deleted.  The assumption is that you saved everything you needed before you exited.  The autosave files only remain when Crystal is open or if it crashes.  I know that this is pretty standard for autosave, but some users still expect the file to be there after they close Crystal.

2) If you do crash, and then restart CR, you won't get a notification of files that you can recover – even when they are there.  This is different from the MS Office feature and can make you think the autosave didn't work.

3) To recover your files you have to know where they are.  Searching for the exact file name you were working on won't work because Crystal Reports changes the name (i.e. from orders.rpt to orders.autosave.rpt).  I found this article helpful because it gave the path to the folder where these files are stored.  I could simply copy and paste this path into Windows Explorer (and putting in my user name).

C:\Documents and Settings\(your user name)\Local Settings\temp

I recommend that you make a shortcut to this folder, because on Vista or Win 7 you won't be able to start at C: and step down through these folders to get to the temp folder.  Some of the folders in the path are restricted.  And, if you use the default Windows view options, then these folders won't even appear because they are hidden system folders.  But if you paste the path above into Windows Explorer you will go directly there.

Let me create your Crystal Reports

There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert?  Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done".  And since I am also a teacher I am happy to explain to you how the techniques work together.

I can also review existing reports that break, or run slowly, or seem overly complex.  Let me have a look at them and see if there is a more elegant solution.

Exports to ODBC and null dates

Several recent projects have had me creating and automating tasks using Crystal Reports, often using the ODBC Export option.  ODBC is normally used to pull data out of the database, but the ODBC export option in CR allows you to export data back into your database.  This may sound dangerous, but Crystal will stop you if you try to overwrite any existing tables.

But recently I found a pretty serious bug in this export. The export ran fine but one of the date columns was largely empty.  Two thirds of the dates in the column were Null, even though the original column was 99% complete.  All the other columns were fine (including another date column) and a large chunk of the faulty column was still fine.  But after nearly an hour of experiments, nothing I tried would create complete data for that field.

I could see no pattern so I brought the file to my own PC and tested different versions of CR and a different ODBC database as a target.  I got the exact same result.  I finally discovered the problem when I tried sorting the data.  An ascending sort gave the exact reverse result of a descending sort, which pointed to a specific record in the middle.  That single record had a Null date.  Apparently when an ODBC export encounters a Null date value in a column, it not only exports that record's value as a Null, but exports null values from that point on.  When I filtered out Null values in the selection formula, the export was fine.  Another option is to sort them to the end of the report.

With further tests I confirmed that:

1) It only affects DateTime fields (not numbers or strings)
2) I tried exports to 3 ODBC drivers so it likely affects all of them.
3) I tried reports reading both SQL Server (ODBC) and Access (DAO) data, so it likely affects any data source.
4) It happens in CR versions 8.5,  10 and 12, so it probably happens in all versions of CR.

If anyone sees a different behavior please let me know.

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.

When Crystal is too helpful

The goal of Crystal Reports is to make it easier for you to create your reports.  But there are several features that in my opinion just get in the way and slow things down.   So I have put together a list of these features and how to avoid them.

"Auto Smart Linking" is a prime example of Crystal trying to help out, and usually making things worse.  This feature takes a guess at how tables should be linked, but my estimate is that it is correct about 30% of the time.  So I typically teach my students to turn this feature off. Except, of course, in version 9 where you CAN'T turn it off.  In version 10 you can turn it off, but only if you dig into the registry and make some tweaks.  For all other versions you can go into File->Options and uncheck this feature at the bottom of the database tab.

Another feature is the default setting to save every new report with data.  Don't get me wrong - saving reports with data is very handy at times, but not every new report you create.  New users sometimes think that a report that opens in preview has just been run. Then they wonder why the data hasn't changed. So I usually teach my students to turn this off as global default.  You can turn the global default off under File->Options, on the "Reporting" tab.  You can activate it within an individual report by going into File->Report Options.

Then there is the more subtle help.  Say you click on a field and then you open up the Select Expert.  Crystal assumes that whatever field you are sitting on when you open the select expert should start a new rule so a new tab is added for you.  I have watched users delete the same rule, over and over again, only to see it return the next time they open the select expert.  These users don't realize that this new rule will stop being added when the cursor is moved somewhere else.  In the meantime it can be ignored because it is set to "any value".

A similar 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 the normal one for groups.  But this one only happens if you have selected the cross-tab object in the upper left (empty) corner.  If you are in an individual cell of the cross-tab you will get the normal Group Sort Expert.

This last one is for v9 and v10 users.  When you create a new formula field in these versions you get the choice of using the Formula "Expert" or the normal Formula Editor.  The Formula Expert is useless for most users.  It can only be used in reports that have custom functions which are also useless for most users.  But if you ever save an empty formula – even temporarily – when you go back to edit this formula you will end up in the Formula Expert by default.  The first time I ended up in the Formula Expert I had no idea how to get out of it.  I had to delete and then recreate the formula.  I have since learned that you can switch back to the Formula Editor by clicking a button.  In v9 the button is a bit more 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 with the magic wand on it.

Feel free to share your own favorite 'helpful' features in the comments.

Other recent blog posts:

Restoring the "Characters Per Inch" prompt for text exports
Crystal Reports Service Pack 3 causing problems with saved data