The Crystal Reports® Underground

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 add a registry key called ‘LOV’ in the CR path for Database options.  This needs to be done on each PC that will be running the report.   The following examples are for version 11.5 (XIr2) and version 12 (2008). Your registry path may be slightly different depending on your Operating System and your CR version.

1) Add a new registry entry under the appropriate path on your PC to the DatabaseOptions key:

HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.5\Crystal Reports\DatabaseOptions\

HKEY_CURRENT_USER\Software\Business Objects\Suite 12.0\Crystal Reports\DatabaseOptions\

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

3) Add a string to that key called MaxRowsetRecords

4) Set the value to whatever limit you want.

I have read that you can enter a zero or -1 to mean unlimited, but that hasn’t worked in my tests in CR 2008.

Also 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

Jeff-Net has released a free Edition of Report Runner viewer.   It is completely free, can be installed as many times as you like and doesn’t have any ads.  It is only missing 2 features that are included in their paid edition:

1) You can’t launch reports from the command line (batch files).
2) You can’t customize the interface

But otherwise it can do everything a report viewer should do (preview, print, export, refresh, etc).  To get your license you have to provide a company based Email address (not Hotmail/Gmail/etc.).  That’s it.

If you want to compare the features of Report Runner with the other free (and paid) viewers you can read my review of Crystal Reports Viewers, which I update every September.