I have written before about copying objects, both within a single report or from one report to another. Below are some new tips for copying values instead of objects.

Copying literal values from preview mode:
Often when validating or troubleshooting a report I want to take a specific item from the preview screen (an invoice number, customer name, etc) and look it up within the application. I have learned that you can copy a value directly from the preview screen and paste that value into another application. Or, I may need a specific value from preview to use as a literal value in a formula. All you have to do is select the value you want and then copy it and paste it into the formula. This can be especially helpful when the value is long or difficult to type correctly.  All three copy methods work (Ctrl-C, right-click or the edit menu).

The value that gets copied to the clipboard will match what you see in preview. Numbers and date values will be pasted in the same format as they appear in preview. Fields that are not large enough to display the entire field value will still copy the entire value of that field. So truncated text or large numbers that show as pound signs will both paste the full value.

Copying object names from design mode:
Often when writing a formula I find that I need to insert a database field or formula field that is sitting on the report. Rather than trying to remember the name of the correct field, it is possible to copy the name of the object while in design mode. Just select the field you want and copy it using one of the three methods mentioned above.  When you copy a field in design mode you are copying the name of the field.

You can then paste the name into the formula editor and put curly brackets around it.  Crystal should recognize the field. If it is a formula field your pasted value will include the @ symbol. I have found that the most efficient method is to first type the open curly bracket. This opens the auto-complete list with names of all available fields. Then you past in the copied field name and Crystal selects the correct field from the list. When you hit enter it adds the closing bracket automatically.

This is especially helpful in complex reports when you have many tables and fields, or when the field names are particularly long and hard to remember.

I write many formulas, and the more formulas there are in a report the more import it is to name them well. About 10 years ago I wrote an article with tips for naming formulas. I will often come back and rename formulas when I find that the original names aren’t clear enough. One of the main points of that old article was “don’t be afraid to rename formulas”.

But there is one thing I didn’t mention in that article. You get a slightly different behavior if you rename formulas while you are inside the formula editor as opposed to if you rename formulas in the Field Explorer. The difference has to do with the formulas being listed in alphabetical order. When you rename a formula in the Field Explorer that field gets repositioned immediately based on the new name. But, when you rename a field in the Formula Editor the field doesn’t get repositioned immediately. The formulas won’t be resorted until the next time you modify and save a formula’s contents.

Say I have 50 formulas in my report and I want to rename 12 of them from “Payables 01”, “Payables 02”, etc. to “AP01”, “AP02”, etc. If I am in the Field Explorer and rename “Payables 01” to “AP01” that formula (and my cursor) would be immediately moved to the “A” section of the formula list. To rename “Payables 02” I would have to pick up the mouse and scroll back to the “P” section of the list to find it. That formula would also move immediately to the top of the list, and so on. But, by renaming these fields in the Formula Editor I can rename the first formula, hit “Enter” and simply use the down arrow to move to the next field. All 12 fields stay together during the renaming process and they don’t get alphabetized right away. When I am ready to alphabetize the list again I select any formula and add a space to any line and then hit “Save”. The list gets alphabetized and the 12 renamed fields move to their new positions.

Note – you can’t just click save. You have to modify a formula and then save it. That is why I typically add a space and click save.

So next time you want to rename a group of formulas you can use this trick to save a bit of scrolling.

A customer recently asked for help with a formula. She was creating an index at the end of a report to show page numbers for each item within the report. Her formula used nested FOR loops to put the elements of the array in alphabetical order. It was similar to this handy formula that I was given once to do a “bubble sort”:

WhilePrintingRecords;
stringvar array MyValues;
numbervar i;
numbervar j;
stringvar temp;
FOR i:=1 to ubound(MyValues)-1 DO (
FOR j:=1 to ubound(MyValues)-i DO (
if MyValues[j] > MyValues[j+1] then (
temp := MyValues[j];
MyValues[j] := MyValues[j+1];
MyValues[j+1] := temp;
)));
join(MyValues,",")

The problem we had was that the formula would work fine for small arrays, but would generate an error on a larger array. The error would say:

“A loop was evaluated more than the maximum number of times allowed”

The maximum number of loops in a single formula evaluation is 100K, so usually this error means that you wrote an infinite loop. But in this case it was legitimate.  Because an array can be up to 1000 elements, and we are doing one loop within another, this formula could theoretically generate up to one million loops. A correctly done bubble sort for 1000 elements should max out at about 500K, well over the limit.

To stay under the limit on longer reports, we separated the two loops. With the formula above we wait until the end of the report and do the sorting all at once. Instead I added a sorting loop to the formula that adds each element to the array. This way we sort the array as the elements are added. And because this formula evaluates each element separately, the maximum number of loops at one evaluation would be just the size of the array.  Problem solved.

But in doing my research on this I learned something new.  I learned that 100K loop limit isn’t a hard limit. You can override this limit to make it both smaller and larger as needed. All you have to do is start the formula with a line like this:

option loop 200000;

This would double the limit from the default of 100k to 200K, making more nested loops possible.  Crystal let me set the number at one trillion without complaining, although I didn’t actually try to create a loop that ran that many times. I still prefer to avoid nesting loops when possible, but this might solve a problem some day.

I received a call this week from a potential customer who said he wanted me to help him modify some reports. But then he started talking about iNet Clear Reports. I told him that I did Crystal Reports and had never heard of Clear. He said that Clear was “just like” Crystal and he was convinced that I could help him.

While we were talking I did a quick web search and found an image of the Clear design environment. I was surprised at how much it looked like the CR design environment. I also saw that it was previously named “Crystal Clear” which I remember reading about years ago.  So I decided to remote into his PC and see what the tool was like.

As soon as I started exploring a report I found that most things were pretty much where I expected them. For example, the report sections were named the same way and had very similar properties. The field explorer had the same nodes. The formulas were written in either “Crystal Syntax” or “Basic Syntax”. In the end I was able to make most of the required changes to the report, including adding a SQL Expression and fixing a formula. My learning curve was pretty mild.

After we were done I downloaded the iNet Clear Report Designer (Windows) and played around with it. It is clearly modeled on Crystal Reports, with many features copied exactly. There were many differences but they were both positive and negative. For instance there is no way to modify the report while in preview mode. And some simple changes generated odd errors.

On the other hand, the Clear designer can be run on Linux.  I liked the ability to add SQL for a dynamic parameter right in the parameter window. I also liked that the Field Explorer shows the data type for all field types, including parameters, formulas and SQL Expressions.

I still prefer Crystal Reports, but I plan to study the features of iNet Clear Reports and eventually add it to my comparison of reporting tools. And since it is so similar it will probably end up listed on my consulting page.