The Crystal Reports® Underground

Millet Software has just released version 6.5 of Visual Cut, one of my favorite tools for automating Crystal Reports.  There are about 50 fixes and new features but the one that stood out was the ability to export to XLSX format.  This format supports more than a million rows in a single tab and consumes much less disk space.

Another handy new feature, pointed out by Jared Faulkner of JF Electric , is the “After_Success_SQL” command that allows you to run a SQL statement after a completed report, or for each group in a burst. This gives you the ability to update the database with tracking information for each e-mailed or exported item. Doing this before required having the CUT Light UFL (also by Millet Software) but now this is built into Visual Cut.

If you want to learn more about Visual Cut or one of the other similar tools, then you should read my annual review of Desktop Schedulers.  The review is done each March so it will be updated next month.

I have quite a few customers who use PeachTree Accounting and PeachTree comes with Crystal Reports. So I have developed a bit of a specialty creating custom reports for people who use PeachTree. In January I saw something new.  PeachTree eliminated a field in their database that has been there for ages. The field is called QtyOrdered and it shows the Quantity of each item on an order. But starting  with the new 2012 product this field doesn’t exist and you have to use another Quantity field in its place.  The challenge was that there are a handful of different “quantity” fields to pick from so it took some experimenting to find the right one (JrnlRow.Quantity).

This means that any custom reports that use the original field will generate an error.  To run the report you have to edit all formulas and features that use QtyOrdered, and replace it with the new field.  If you need help with this change, or with creating custom reports from PeachTree, please give me a call.

I always mention case sensitivity in my Crystal Reports classes. My normal comment is that CR formula comparisons are case sensitive with the exception of the selection formula, which is usually NOT case sensitive.  I even wrote about this here, last month.  So I was surprised last week in my Advanced class when our formulas ignored all case differences. I thought this might be a recent change so I went home and tested reports back to v8.5. None of the comparisons were case sensitive.

So I did a quick Google search and found a 10 year old forum thread discussing case sensitivity. One short comment in that thread explained the difference. And guess who wrote that comment and promptly forgot about it (Doh!).

So the answer is that there is a setting in
Continue Reading »
Case sensitivity in Crystal Reports – revisited.

There are many ways to deploy Crystal Reports to users. I normally lean toward the simpler and less expensive options, like locally installed viewers, or scheduled delivery of PDF output.  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.  But there are other, less expensive products out there that many users never see. These are third party products that allow you to centrally manage your report deployment from your server.

There is a page on my blog that lists and compares these products, and I update it every January.  This year the list includes:
Continue Reading »
Server based deployment options compared (2012)

Crystal limits how many values you have to choose from when you use a dynamic parameter.  The default limit is 1,000.  To show more than 1000 values you have to edit the registry on the PC.

1) Add a new registry entry under:
HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.5\Crystal Reports\DatabaseOptions\

2) Add a new key at that level called: \LOV

3) Add a string called MaxRowsetRecords
4) Set the value to whatever limit you want, or enter a zero to mean unlimited.

Note that loading large lists this way can make the parameter run for a few minutes before the user can select a value. Also note that any dynamic list that has more than 200 values will activate the parameter’s ‘batch interface” discussed in a previous post.

(And thanks to Duane Fenner, an Account Manager and CR developer at LeaseTeam, Inc. for suggesting this post.)

One of my customer sent me two screen shots. One showed a normal preview on his development machine. The other showed a preview window on the runtime machine. The second had the group tree, but the report was nothing but a long blank vertical strip. He had been struggling with it for a month and could not figure out what was causing the strange behavior at runtime.

When the preview behavior changes on a different machine, it is usually due to a printer driver problem. I also know that CR will normally
Continue Reading »
Strange printer behavior at runtime

For the past few days I have been helping a customer develop a GANTT style chart in Crystal Reports.  The challenge was that Crystal’s normal Gantt chart doesn’t allow multiple bar segments on the same row.  So I was showing them how to create a GANTT chart using formulas and specially formatted summary fields. This method requires formatting dozens of small fields with the same color condition.

I selected an initial color but was trying to think of the most efficient way to change the color in all the condition formulas on the fly. Before I had an answer the customer showed me a method I had not seen before.  He created a single formula that returned
Continue Reading »
Controlling the color of many objects from one formula

Say you have several columns of numbers that get added together like Price, Tax and Shipping. At the end of a customer group you would have three subtotals. You might decide to write a formula that adds those three subtotals together to get the combined total for the customer. It would work fine but there is usually a more efficient way to do this. Instead of writing a formula that combines the subtotals I would write a formula that combines the fields at the detail level. It would look like this:
Continue Reading »
A total of a formula instead of a formula of totals.

People who create reports are always interested in metrics. This article in Forbes suggests five metrics that you will probably never see on one of your reports – but they still got some gears turning in my head.  Thought I would share.

This week I had a customer who was having trouble getting some version 8.5 reports to run on another another computer using CR v11.  The report wouldn’t connect to a new data source consistently.  So I gave him the following steps that seemed to clear up the problem. Here they are in case someone else has a similar situation.

Any time you need to run a v8.5 report in a later version of CR the first step is to change the version of the RPT.  To do this open the old report
Continue Reading »
Running v8.5 reports in later versions of CR