The Crystal Reports Underground News

Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide

(For recent updates read my BLOG, The Crystal Reports Underground )

The Crystal Reports Underground News - Volume 2006.03 (Mar 2006)

an independent source for Crystal Reports Information by Ken Hamady

Contents for March, 2006:

** Crystal Reports XI and Cold Fusion
** Update to my "Don't Pay Twice" Campaign
** Expert Crystal Reports Training
** Buying Older Versions of Crystal
** Mapping by County or Zip Code
** My Library of Crystal Reports Guides
** Copying Objects Within a Report
** Copying Objects to Another Report
** Read back issues at http://www.kenhamady.com/news.html

Crystal Reports XI and Cold Fusion:

Crystal Reports XI does not support Cold Fusion integration. I don't think Crystal Reports EVER officially supported Cold Fusion. But, with older versions of Crystal you could send CF commands to the Crystal print engine and run reports. Macromedia's web site has lots of info about this for v8.5 of Crystal. It apparently got tricky with Crystal v9 and v10 which supposedly didn't support the Print Engine method of integration. One Macromedia article says that you could find the DLL and move it into another folder to make it work. This seems to no longer work with Crystal XI. I was told that some ColdFusion developers have had to write C# "bridges" which allow you to control XI reports (indirectly) from ColdFusion, but I haven't found anyone who has actually done this. If anyone can fill in the gaps here, please drop me a line.

Update to my "Don't Pay Twice" Campaign:

There is a recent article in the Business Intelligence Pipeline that covers BO's attempts to restrict independent training. I thought it was a great article but that may be because a substantial part of the article quotes me on the matter. See the following page of my site for a link to this article:

http://www.kenhamady.com/licensingissues.html

One new item surfaced in this article. A BO spokesperson mentions a hitherto unknown form for requesting permission to use screenshots in "reference books". I have reviewed the request form and there is no charge involved as long as the screenshots are less than 10% of the book and you don't disparage the product. In my opinion this would be considered "fair use" and therefore doesn't require any permission, but some folks will think they are playing it safe and sign the form.

I also wonder what the "approved" courseware developers think of this, since they are paying almost $40 per copy for the right to use those same screenshots. Hopefully there is some value to them in getting the "approved" stamp.

Expert Public and On-site Training:

My specialty is teaching Crystal Reports at your office, with your data. And I charge by day, not by student. So, if you have 4 or more students you may find the cost of an on-site class less than the cost of sending the same students to a public class. Even if you have fewer students you might find a benefit to working with your own data.

And if you can't afford your own private class, come to my public classes in Frederick, MD. These are very small classes and people come from all over to attend. My March class included 5 students that came from as far away as Florida and Long Island.

For more information you can call me at (540) 338-0194.

Buying Older Versions of Crystal:

I often get people asking me where they can buy old versions of Crystal Reports because they get confusing information from the sales reps at BO. Business Objects still sells all versions of Crystal Reports back to v8.5. Versions 9 and 10 cost the same as version XI, while a copy of v8.5 cost about twice as much as the newer versions. The reason for this premium is not clear, but it may be that BO thinks the old license terms were too generous.

To buy version 7 and anything prior you have to go to Ebay. I have purchased quite a few licenses from Ebay users over the years and you can do well if you are careful and ask the right questions. I recently checked the listings and saw versions 6 through 11 all for sale in one form or another. Some versions are upgrade editions which require you to own a prior version of Crystal. Of course, this "older version" can be an OEM version or a really old version. These can often be purchased on Ebay for just a few bucks.

If you want more information on buying Crystal on Ebay you should read my newsletter from January 2002.

Mapping by County or Zip Code:

One of the topics I teach in my advanced class is how to use a map instead of a bar or pie chart to represent summary data. For instance you could show your sales by state and have the states with the highest sales be darker while the states with the lowest sales are lighter. You can even set it up to drill down on the state in the map and have Crystal display the detail for that state. (Note that some versions of Crystal do not install the mapping feature unless you do a custom install.)

This Crystal feature uses a runtime version of MapInfo Professional. This runtime allows state maps, country maps, a zip code map of Washington DC and (with a secret tweak) a county level map for the Mid-Atlantic states. If you want to do Zip Codes for any US state, or county level maps for any other region, you have to purchase additional tab files or 'layers' and install them with the MapInfo runtime. The following is an example of the cost:

Zip Codes for one State:
    $395 for 1 user
    $513 for 5 users
    $790 for 10 users

Counties for one State:
    $125 for 1 user
    $162 for 5 users
    $250 for 10 users

After several phone calls I finally found someone at MapInfo that could explain the process for installing these files in a Crystal environment. I have added this process as an article in the LIBRARY area of my site. While testing this I discovered the tab file for the Mid-Atlantic counties. This is installed with Crystal Reports but is not activated. To activate this file you must:

    1) Locate the file named MIGM30.EXE in the MapInfo Program directory
    2) Run this program and highlight "US County Boundaries"
    3) Click Properties
    4) Put in the SEARCH check marks for both "County" and "FIPS Code"
    5) On the right side of the window click "ADD"
    6) Select the Mid-Atlantic Geoset (midatlan.gst) and click "OK'
    7) Click OK again and then click Exit to close the program

Now you can create county maps for the entire Mid-Atlantic region using any table that has either county names or FIPS county codes. One note, I strongly recommend loading and using the FIPS codes which you can find and copy from several web sites. The reason is that the same county name can exist in several states, so the mapping engine may map the wrong county. For instance, the only way I could find to tell the map that my "Warren County" was the one in VA as opposed to the one in PA was to use the FIPS codes.

If you need someone to help you with the mapping feature of Crystal, drop me a line.

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 Techniques Vol. I ($19)
    Expert Techniques Vol. II ($19)
    Quick Reference to Crystal Reports in Visual Basic ($16)
    Quick Reference to Crystal Reports in .NET ($14)

           http:/www.kenhamady.com/tools.html

Copying Objects Within a Report:

There are 3 object types in Crystal that change automatically when they are moved or copied to a new section. Summary Fields, Cross-tabs, and Charts are all relative to the section in which they are located. So, say you have a row of 5 subtotals in Group Footer 1 and you decide to add Group 2 to the report. You might want all 5 subtotals to be calculated at the new group level as well. All you have to do is copy and paste the subtotals to the new group footer. The copies immediately become Group 2 totals which means you have 5 new summary fields in the report. You could also paste these totals into the report footer and they would become grand totals. This works for any Crystal summary field (ie when you use Insert->Summary). This does not work with running total fields or totals created with a formula.

The same principle applies to Cross-tabs and Charts. Say you have a cross-tab in the report header where it summarizes all records in the report. If you were to drag this object to a Group Header or a Group Footer it would now appear with each group, and would summarize the records within that group. Instead of moving the object you could copy and past it into the Group Header. Now you would have the cross-tab at both levels.

If you want to copy and past any one of these objects to another section, quickest way to do this is to hold down your CTRL key and then drag the object to the new section. The CTRL key tells Crystal to keep the original copy where it is and drag a duplicate copy to the new location. Unfortunately, this method doesn't work with a group of objects. You can, however, select a group of objects and use normal copy/paste to copy them as a group.

Copying Objects to Another Report:

Crystal can have multiple reports open at the same time. This allows you to copy and past most objects from one report to another. This is particularly helpful with formulas and parameters. Say you have 10 formulas in report A and you want to move them to report B. Here are the steps:

    1) Open up your field explorer and select those formulas as a group.
    2) Drag the whole batch onto any empty section of the current report.
    3) Use your CTRL key to select the whole group of formulas.
    4) Right click on any of the formulas in the group and pick COPY
    5) Switch reports and PASTE them into any empty section of the new report.
    6) These formulas are now listed in the field explorer of the new report.

Even if you delete the field objects from the new report's section, the fields will still be listed in the field explorer. This is much simpler then adding new formulas one at a time, naming them, and then pasting the text of each formula into the formula editor.

And, you can use this method with parameter fields as well, allowing you to move an entire group of parameters quickly from one report to another. The parameters automatically come in with all of their default values and any other properties.   One exception to this last feature is Crystal Reports XI which (for some unknown reason) has lost the ability to copy and paste a parameter field from one report to another. And thanks to Jim Johncox of Epic Systems Corporation who pointed this out to me.

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 2006 by Ken Hamady
All rights reserved - Republishing this material requires written permission