The Crystal Reports® Underground

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
Continue Reading »
Vanishing data in Btrieve

It appears that SAP will release a new version of Crystal Reports this year.  This isn’t a big surprise given that it has been two years since the last version.  There are no details yet but George Peck mentioned in a recent article that the SAP Users Group might give a preview in the next month or so.   Anyone have any details to share?

And thanks to Gordon Portanier of Crystalize in Canada for giving me the heads up.

There are many ways to deploy Crystal Reports to users.  I normally lean toward the simpler and less expensive options, like locally installed viewers.  But there are environments where a server based option is necessary.  The ‘official’ options from SAP are Crystal Reports Server and BO Enterprise.  You can read about these on the SAP website under the heading “SAP Business Objects portfolio”.  But there are other products out there that most users never see. These are third party products that also allow you to centrally manage your report deployment from your server.  The list includes:

Ripplestone – Traditional portal
rePORTAL - Traditional portal
ReCrystallize Pro – Web Launch page generator.
Universal Report Server (URS) – Server-based scheduling engine
Report Launch – Bridge between BO server products and server based applications.

The products vary widely and are aimed at
Continue Reading »
Server based options for Deploying Crystal Reports

I have come to depend on Crystal’s Guidelines for aligning objects. I find them much more useful then the “Snap to Grid” feature, although I do use both from time to time. But there are often cases where the object needs to be somewhere in between. It can be frustrating to try and position an object while it jumps up or down because it is trying to snap to a grid point or a guideline.

If you run into this problem there is a handy solution. Hold down the “ALT” key while you are moving or sizing the object. The “ALT” key temporarily turns off both snap to grid and guidelines, without making any permanent changes to your settings.

Normally if there is an unlinked table in one of my reports it is there by mistake.  However, I have recently been reminded of some of the neat things you can do with unlinked tables, as long as you are careful.  The official name for having unlinked tables is a ‘cross join’.  The data that is returned from a cross join is known as a Cartesian product, which means every possible combination of records between the two tables. That is why you have to be careful.

So say you have a customer table with 100 customers in it and you use that table to create a report listing just the customer name.  Without adding any filters you would get all 100 records in the report.  But then you add a second table that doesn’t link directly to the first, something like the master list of products.  If this table has 25 products in it then
Continue Reading »
Using unlinked tables (cross joins)

I had surgery on Friday 12/18.  Nothing serious, but I knew that I wouldn’t be able to shovel any snow for a week or so after my surgery. So I intentionally scheduled the procedure before winter snow was likely to be serious.  Heavy snow isn’t common in Virginia in mid-December   Well, as many of you know, this area was hit with a record setting snowfall that started Friday night.  Here is a shot of the snow piled up on my deck.  I think the table (or the “birthday cake” according to my kids) is about 18″ deep.  Fortunately the driveway is  ‘only’ at about 13″.

==========
Added 2/6/2010:

We got another one, and this one is literally twice as deep as the one above.  It may be the all time record snowstorm for many areas here.  I shoveled  three times today while it was snowing and was able to bring the driveway down from 22 inches to about 4 inches but it was still coming down when I quit.

Our deck is even deeper than last time, with a 27″  birthday cake.  The funny part is the hand rail you see behind it.  This is only a few inches wide yet it has a 20″ wedge of snow balanced on it, making little walls on 2 sides of the deck.

Have you ever noticed that some parameters include a small toolbar below the List of Values (LOV) drop down?  This toolbar has two scroll arrows on either side of a small drop down list and two buttons with funnels on them.  The toolbar doesn’t show up very often so it took me quite a while to figure out where it comes from and what it is supposed to do.  So this article is for anyone else out there who is puzzled.

This is a called the batch interface.  It is included in Crystal versions 11 and 12 and appears automatically whenever there are more than 200 values in the parameter list (LOV).  To make the long list easier to navigate Crystal automatically batches the values into
Continue Reading »
Using the parameter “batch” interface toolbar

If you need to pull a single character out of a character string, or a single item out of an array, you can use a subscript to identify the position you want like this:

{Customer.Customer Name} [3]

This example starts counting from the left to identify the third character or element.  Over the years I have had people tell me you could use a negative number
Continue Reading »
Using negative subscripts

If you need to buy an older version of Crystal Reports, all the way back to v8.5, you can now download them directly from BO (for $595).  This link includes all versions from 8.5 forward.

If you want to save a few bucks you can check on Ebay, where I regularly find older versions for sale.  Just make sure you know what you are purchasing.  In 2002 I wrote an article about what to look for when buying CR on Ebay.  That article still applies, although some of the product codes listed might be different in newer versions.

Or check with me.  I sometimes know of extra copies that are available.

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.   And now my readers can get an additional discount of $20 for
Continue Reading »
Surfing Gecko offers “all you can eat” viewer for $129