Recently a customer needed to group records using part of a code field. The code field had alpha characters followed by a series of numbers. They wanted to to strip off any numbers on the end and group using only the initial string portion of the value. In the past I have done things like this using a FOR loop to check each character. This time another idea popped into my head. If you reversed the string so it started with the numbers you could put it inside the VAL() function. This would convert the numeric portion to a number and ignore the string characters that followed. You could then check the length of that number and know how many characters to take off the end of the value. To get the length of the number you would have to turn it back into a string and then use the Length function. So the calculation would nest four functions like this:

Length( Totext( Val( strReverse( {table.field} ) ) ,0, "" ) )

The only glitch was if the string ended with a zero, like “XXX1230″. In those cases the VAL() would ignore the zero (true numbers don’t start with a zero) and then my length would be off by one. To fix that I appended a “1″ to the end of each string before I ran it through the calculation. That way the value would never end with a zero.

Last, I created a local variables so that a user would only have to put their field name in on place. The end result looks like this:

//add a one to deal with a final zero
Local StringVar x := {Customer.Postal Code} & '1';
//calc the # of digits to remove
Local NumberVar y := Length(Totext(Val(strReverse(x)),0,''));
//if it is all number characters return a blank.
if y = length(x) then ""
// otherwise reduce the length by y positions.
else x [ 1 to length(x)- y ]


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.)

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.

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

Last night I had a customer with a strange problem.  Their report ran fine and looked great in preview and when printed.  But if it was exported to PDF it had a long line down the page.  This didn’t happen if it was printed to a PDF driver so the only time it was visible was when using the CR export function.

I opened the PDF and there was a vertical line on every page, even on blank pages, starting near the top.   I opened the RPT and there was no visible object anywhere near the sections at the top where the object should be sitting.  I was getting ready to start deleting objects one at a time to see if it was tied to a specific object, but first I decided to Continue Reading »
Mystery line in PDFs

I have had several instances recently where a change to a report caused Crystal to generate strange SQL.  Sometimes you can see the problem under “Database > Set Location” where you will notice two different instances of the same database in the upper window.  Or, you may notice that your SQL Query gets separated into two or three independent queries when it should all be one query.  In a few cases the SQL gets so bad that it generates an error from the database, or it generates a “Cartesian product” result, trying to return all combinations of records between the tables.

If you run into a situation like this here are the steps that I have found that help:

1) Switch to design mode so that you don’t launch a query with every change.

2) Go into “Database >Set Location” Continue Reading »
Reseting SQL Errors

Have you ever wondered why Crystal allows an export to “RPT” format?   Since the file is already in RPT format it might seem like a redundant option.  But last week I did a consult and was told that the option of exporting to RPT format was the most valuable information in the entire consult.

So what do you get when you export to RPT format?  You get an RPT file that is saved with the preview data.  While most of you won’t have any trouble creating an RPT saved with data, some users aren’t so lucky.  These are users who can only preview a report within an application like a web or server based app.  For these folks, troubleshooting a report is an endless loop of making a change in design mode, uploading the modified report, running the report through the app to see if the change worked,  then going back to design mode.  I have had Continue Reading »
Exporting to RPT format

The Distinct Count summary function in Crystal Reports is pretty handy.  It allows you to count the number of unique values in a column.  It can eliminate duplicates from the count, even when the duplicates are NOT consecutive.

One of my customers wanted to do the same type of summary in an Excel spreadsheet.  We were both surprised that there was no function like this in Excel.  But after a few Google searches I came up with a workaround that lets an Excel formula do the same thing as the Distinct Count summary function in Crystal:

=SUMPRODUCT((A1:A99<>"")/COUNTIF(A1:A99,A1:A99&""))

You replace the three sample ranges “A1: A99″ with  whatever your data range is.  I am not sure I could explain why it works, but it does.

Ido Millet of Millet Software has added another powerful feature to an already feature rich product called Visual Cut.  Users now have the ability to automatically generate an Excel Pivot Table based on an exported (Data Only) excel worksheet.  If you use pivot tables then you might want to see this feature in action.  Ido has produced a video that shows how it works.

If you want to compare the features of Visual Cut to similar products you can read my comparison.

Or, you can try Visual Cut for free by sending an Email request.  Millet Software will respond with a download link and instructions.  Please let them know that you have read about Visual Cut in my Blog.