an independent source for Crystal Reports Information
by Ken Hamady, MS

Contents for December 2009:
** Surfing Gecko offers "all you can eat" viewer for $129
** Selecting multiple objects (to copy or align)
** Learn Crystal Reports (your place or mine)
** Formula editor window disappears
** Formula field tree that won't dock
** Learn the secrets of Crystal Reports
** Calculating a Distinct Sum at the top of the report (or)
** Building arrays WhileReadingRecords
** Using Crystal Reports in VB.NET Express Edition
** Other recent blog articles:
    The Joel Test: 12 Steps to Better Code
     New prices for mapping layers (zip/county)


Surfing Gecko offers "all you can eat" viewer for $129

Surfing Gecko is the company behind a popular Crystal Reports viewer known as Crystal Ease.  Crystal Ease is a simple viewer that allows your users to run, print, export and refresh Crystal Reports without having to install the full Crystal Reports designer.  It supports Crystal versions up to XI and even allows you to tweak the select expert at runtime.  But the biggest selling point for Crystal Ease is now the price, especially if you are looking at more than 10 users.  That is because they have recently started offering an unlimited license for only $149. My readers can get an additional discount of $20 for a net price of $129 for unlimited licenses.  All you have to do is make the purchase using the link in my blog article.  The only way to get a lower price is to use on of the free third-party viewers listed on my LINKS page.

And like most of the viewers that I list, you can try it out for 30 days before you purchase it.  Just use the trial download here.  As always, user feedback is very helpful so let me know if you have comments, complaints or suggestions related to any of these tools.  And if you want to discuss which tools to use in specific situations, give me a call.

Selecting multiple objects (to copy or align)

Have you ever copied a group of objects and then pasted them to another section or another report?  When you click "paste" you only see one of the objects on your cursor.  The rest appear when you click on the page.  So which object is the one on your cursor? You guess and select a spot for that object (oops), click undo, try again (oops), etc.  Wouldn't it be nice to select the "cursor" object before you copy so you know how to paste it in?

The same challenge comes up when you select a group of objects and want to align them all by their bottoms, lefts, etc.  Which one of the group sets the alignment position for the others?

Both of these are simple to control if you remember that the last object you select is the "Leader". This is because the last item you select is the one that currently has Crystal's focus.  So whichever one you clicked on last is the one that will be on your cursor when you copy and paste.  The same holds true when you do alignment.  The one you click on last is the one that shares its alignment coordinates with the others.

The trouble comes when you select a group of objects without individual clicks.  You might use the Lasso to select the group, or you might right-click on the name of a section and use "Select all section objects".  In these cases look for the one that has sizing handles. Or you simply appoint the leader object by:

1) Hold down your CTRL key and click the object you want to be the leader once, and then click it a second time.  This takes it out of the group (temporarily) and then puts it right back in.  And because you added it last it becomes the Leader.

2) Click one of the selected items in the group.  As long as you click on a selected item it becomes the leader.  But if you click anywhere else your entire group is cleared.

If you copy the group and click paste you can align the object on your cursor to where the leader object should be pasted.  And if you align a group of objects they will all take their position cue from the leader.


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 remote connection when needed.  We can review lessons, discuss questions or even troubleshoot existing reports.  For more details see the "Individual Training" page on my web site.

Or, 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.


Formula editor window disappears

If you are using a dual monitor or changing your monitor resolution, it is possible to 'lose' your Formula Editor.  This is because the window tries to remember where it was last positioned, and the last coordinates may now be outside your current monitor's visible area.  You have to reset the coordinates of the Formula Editor window to be within your current view to get it back.

To do this you will have to go into the registry, which is something that you should do very carefully, and (to be safe) after creating a restore point for your system or backing up the registry.

For Version 12, close Crystal Reports first and then follow these steps:

    * Click the Start button and select "RUN"
    * Type "Regedit" and Click "OK"
    * Find the Registry key:  HKEY_CURRENT_USERSoftwareBusiness ObjectsSuite 12.0 Crystal ReportsFormula Workshop
    * Change the "Editor Position" value to 10,10,10,10

For older versions see my blog post on this subject:


Formula field tree that won't dock

On a similar note, several of my customers have had docking problems in the Formula Editor and I have always known that the solution was in the registry. But because of the way the registry entries are named, it has been hit or miss to figure out the solution. So when I found some articles in the SAP/BO KB for solving these problems I decided to share them. They are all very similar solutions but there are some differences in the details.

Versions 11 and 12:
If a window in the formula editor won't dock, start with the simple option. First right-click in the middle of the window and make sure that "Allow Docking" is checked. Then double click on the window title bar and see if that puts it into place. If this doesn't work you will have to go into the registry, which is something that you should do very carefully, and after creating a restore point for your system or backing up the registry. Close Crystal Reports first and then follow these steps:

1.Open the Registry Editor and go to:
HKEY_CURRENT_USERSoftwareBusiness ObjectsSuite 12.0Crystal ReportsFormula WorkshopFormula
-or-
HKEY_CURRENT_USERSoftwareBusiness ObjectsSuite 11.0Crystal ReportsFormula WorkshopFormula

2. Then depending on which window is floating:

    * Select ToolBar-Bar1 (Field Tree)
    * Select ToolBar-Bar2 (Function Tree)
    * Select ToolBar-Bar3 (Operator Tree)

4.On the right locate DockingStyle and set it to f000.
5. Also locate MRUDockingState and set it to 0.
6.Last reopen CR and the Formula Editor and then double click on the floating window

If you are using an older version of Crystal the process is similar, but:

    * the double-click doesn't dock the window, you have to drag it
    * there is only one key to change, not two
    * and the toolbar numbers are different

See my blog article for details on older versions.


Learn the secrets of Crystal Reports

Let a master teacher help you unlock the Crystal Topics that you are struggling with.  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 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.


Putting a Distinct Sum at the top of the report

I recently was rereading some Tek-Tips.com FAQs and spotted one that I didn't remember.  It had to do with creating a combined list of detail values that can print as a single string.  I have a formula for this on my site, but it happens WhilePrintingRecords, which means you can only display the combined values at the bottom of the report.  With this method you populate an array during the WhileReadingRecords step of report processing.  Because this array is complete before printing starts it can be displayed anywhere.

It was a clever technique and I could see lots of uses so I did some experiments to find other real-world applications.  This week I found a great one.  You can use it to calculate an accurate sum of a column that has duplicate values (a 'distinct sum') and display that sum at the beginning of the report.  I always teach my advanced students to solve this problem with a running total, but this requires that the duplicates be grouped together and that the total be displayed at the end of the report.  With this new method neither of these restrictions apply.  The only restriction is the size of the array, which can't be over 1000 elements in versions 9 – 12. Go over 1000 unique values and the report will crash.

This method requires only two formulas shown below.  Put both of these formulas in your report header and replace the two fields inside the 'build' formula with the appropriate fields from your report.  The field in the "Amt" line  is the field that you want to total.  The field in the "Key" line  is the field that determines if this amount is a duplicate, like the customer ID, product ID, etc that is associated with the amount.  You don't need to change the second formula at all.

//Build
WhileReadingRecords;
Local StringVar Key := "" & {Customer.Customer Name};
Local NumberVar Amt := {Customer.Last Year's Sales};
StringVar array Keys;
NumberVar Array Amts;
if not(Key in Keys) then
( redim preserve Keys [count(Keys)+1];
redim preserve Amts[count(Keys)+1];
Keys[count(Keys)]:= Key;
Amts[count(Keys)]:= Amt );
Amts [1]


//Display
WhilePrintingRecords;
NumberVar Array Amts;
Sum (Amts)

I am still experimenting with other ways to use this method and will probably include some examples in volume 5 of my Expert Techniques series.  If you would like to understand how arrays work you should read the section on array variables in my Expert's Guide to Formulas.  Or you could call me for a private lesson on using arrays.

Using Crystal Reports in VB.NET Express Edition

I recently wrote a blog post to remind developers that you can't integrate CR into a VB.NET application built with the .NET Express Edition.  So I was surprised when a developer told me that he was using Express to run my sample apps.  These are simple apps included in my guide using CR in VB.NET.  He said VB still wouldn't let him add a the CrystalReportViewer control onto a form, but if the control was already there he could use it with no problem.  He then used the free runtime files that you can download from SAP to run the application.

So it appears that all you need to do if you use CR in VB.NET Express, is to have some source code with the viewer already added.  I haven't done any tests of this theory myself, but I would expect that some of the sample apps from SAP would work to get you started, or the very simple examples that come with my guide.

But even those may not be necessary.  The .NET source code files are simply plain text so you should be able to add the appropriate references to the file using a text editor.  Then you can reopen the file in Express.

If anyone else has tried this method, let me know.  

Other recent blog articles:

     The Joel Test: 12 Steps to Better Code
     New (lower) prices for Crystal Reports mapping layers (zip/county)
 

Contact Information