I was recently working on a formula that had the crystal syntax for “is one of”.  It looked something like this:

{LastName} in [ "Smith","Jones","Thompson","Rutledge","Harris" ]

Each value goes in quotes (single or double). You separate the values with a comma and you put square brackets around the list. So I was surprised today when I saw that the formula was actually like this:

{LastName} in [ "Smith""Jones","Thompson","Rutledge","Harris" ]

Notice the comma missing between the first two values.  The report had been running for months without error messages which I didn’t understand, so I started testing.  My experiments pointed me to a syntax rule that I had read about but never used. It is for when your formula includes a literal string that you surround with double quotes, and when the literal string itself contains double quotes.  Crystal would assume the first quote is the ‘open’ and the second one is the close, even when you want the second one to be part of the visible output.   One solution is to use two consecutive double quotes within the literal. Whenever Crystal finds two consecutive double quotes within a literal surrounded by a pair of double quotes, Crystal will interpret the consecutive quotes as one literal quote and not as a closing quote.  This is much easier to explain with an example.  So if I wanted a formula to output this string:

The syntax for "is one of" uses brackets

I could write it like this inside the Crystal formula:

"The syntax for ""is one of"" uses square brackets"

The formula engine would only display one of the two consecutive double quotes in each pair.  That explains why my formula with the missing comma doesn’t generate an error.  Crystal treated the consecutive double quotes as a single literal and then combined the first two elements in the list as being the single value:

Smith"Jones

There was no error message, but the results would not have been correct.

BTW, the same principle applies to single quotes. If you put two consecutive single quotes in a string surrounded by single quotes, Crystal will ignore the first and treat the second as a literal quote.

The reason I didn’t think of this right away is that I have never used consecutive quotes in a formula. If I have a string that needs to contain single quotes I surround it with double quotes. And if the string needs to contain double quotes I surround it with single quotes. In the rare case that a string needs both I would split it into separate pieces and combine them. I find that using consecutive quote pairs makes the formula harder to follow.

MDB is the original Microsoft Access database format. It was replaced by the ACCDB format in 2007, but there still applications that use MDB files. For instance I have a handful of customers using Raiser’s Edge software for donor tracking, and this application still creates export files in MDB format.

One Raiser’s Edge customer recently contacted me about a report that kept crashing. There was no error message – Crystal would just shut down. The report had 32 tables and 31 joins. If he deleted one of the tables the report ran fine. As soon as he added the last table the report would crash.

In my testing I found that it didn’t matter which table was dropped. So it became apparent that the issue was a limit, somewhere on the number of tables or joins. But it was hard to determine where the problem lay without any error messages.

First I checked the SQL generated by Crystal Reports and that looked normal. I copied the SQL into a new SQL command but that had the same exact limit.

Next I tried the same report using different connection methods. Both OLEDB and ODBC failed in the same way as DAO.

Then I decided to see if the problem was in the MS Access engine so I copied the SQL From the report and pasted it into a new MS Access query. (I am one of those people who still use MS Access 2002.) The query would run fine in MS Access as long as I dropped one of the tables. If I added that last table the MS Access query would generate an error that said:

“Query is too complex”

I couldn’t find the official limits for MDB files but I did find a page that showed the limits for ACCDB files and it says that the number of tables in a query is limited to 32, which in some cases can be reduced even lower.  So apparently we hit the limit. And worse, when you exceed the limits for MS Access, the report can’t survive to give you an error message.  It just dies.

I had a customer recently complain about a report not exporting correctly to Excel (Data Only). The column headings worked correctly in preview but were re-arranged in the spreadsheet in a seemingly random fashion.

My first thought was to make sure that they were all the same width as the field below them and precisely aligned with the fields, but this didn’t solve the problem. Then I aligned their bottom edges but again this didn’t solve the problem.

Then I noticed that the headings that moved to the right were all text objects that were either two rows or three rows deep, making them taller than the other headings. The tallest headings moved further to the right, which meant that it had something to do with the position of the top edge. So I made all the heading objects the same height and then aligned their top edges. At that poing they all exported to Excel in the correct order.

Note that this only affects Excel exports that are “Data Only”. Of course this is the option that I use 99% of the time.

Crystal makes it easy to reduce the number of decimals that you display for numeric values. There are two buttons on the toolbar (near the percent sign) and these allow you to either reduce or increase the decimals displayed. When you reduce decimals the remaining digits will either round up or down based on the digits no longer displayed. In other words, if you are showing 75.28 and you click the button to reduce the decimals displayed by one, the value will display as 75.3. This is because the hidden 8 causes the value to round up to the next 1/10th.

However, this is deceptive. The underlying value in the report is still 75.28. And if you were to total that column or use that value in a calculation the value used would still be 75.28. This can give unexpected results, because the visible total at the end of the report might not reflect the sum of the visible values that go into that total. For that reason, I call this ‘fake’ rounding. You will find the same type of issue in Excel spreadsheets.

Most of the time, this isn’t an issue. And many people who read financial statements understand ‘rounding errors‘. But there are cases where you need ‘real’ rounding. Real Rounding requires writing a formula that uses the Round() function. For instance, if you have to apply a tax rate to a purchase and the calculated tax amount.  You could use a formula like this:

{Sales.Charge} * {Sales.TaxRate}

But if the tax value has more than two decimals, those extra decimals aren’t real.  Most transactions have to be rounded to the nearest penny. In these cases you would need a formula like this:

Round ( {Sales.Charge} * {Sales.TaxRate} , 2 )

This rounds the tax to the nearest penny.  If you used the first formula and created a total of the tax values, the total would include all the fictional fractions of a penny.  If everything was displayed with two decimals the total tax would not match the individual tac values, because those would be using fake rounding to display only two decimals.  But if you use the second formula and then create a total of that formula the total should reflect the visible values.  The total calculation is using values that display the same number of digits as the underlying value.

Last week a customer was really befuddled. He had a formula that said:

if {@field} = 0 then ...

He could see lots of zero values but the formula didn’t work as expected.  He couldn’t figure it out so he sent it to me. The first thing I did was add a few more decimals to see if it was a rounding issue. That didn’t show anything, but the formula still insisted that the value was somehow NOT equal to zero.

So I went into the formula and multiplied the current value by one trillion. Then instead of zeros I started to see some small numbers.  I am not an expert on floating point values or database precision but I have seen this before in reports.  The solution is to round the value in the formula before comparing it to zero. In this case we rounded the value to two decimals like this:

if Round ({field}, 2) = 0 then ....

That made the formula behave as expected.  The odd part is that I have seen the same problem with two other customers in the past week. It could be just a coincidence, but I figured I would mention this and see if this is happening to more people.

Crystal formulas can use 3 different divide operators:

• Regular divide [ / ]
• Integer divide [ \ ]
• Percentage [ % ]

But all of these will fail if you follow the operators with a zero. The report will stop and Crystal will throw the error message “Division by Zero”.  The standard solution is to check and make sure the number you are dividing by is not zero before you do the calculation, something like this:

if {fieldA} = 0
then 0
else {fieldB} / {fieldA}

This way, whenever the bottom of the fraction (denominator) is a zero, the formula will print a zero and NOT try to do the calculation.

But even when customers use this formula pattern I still see the divide by zero error. Some users mistakenly check the top of the fraction (numerator) instead of the bottom (denominator).  Some do it correctly at first, but then change the denominator and forget to change the first line to match.  So I have developed the habit of using Local variables to make things easier. My normal pattern now looks like this:

Local Numbervar n:= {FieldA};//numerator
Local Numbervar d:= {FieldB}; //denominator
if d = 0 then 0 else n/d

This ensures that the value being checked is always the value on the bottom. Some other advantages of this method are:

1) If d is a subtotal or a long expression you only have to enter it in one place.
2) If you have to create a series of similar expressions, like for 12 different months, you can duplicate the first example and you only have to change the values at the top of the formula.

I have written before about databases that take selection criteria from Crystal and then use the wrong index so that valid records are missing from the results. The solution is to write the criteria so those rules don’t make it into the SQL WHERE clause. Crystal can then apply that criteria locally so it is done correctly.

But today I ran into a similar problem that didn’t have a simple solution. I was creating a report to read Elliot data. Elliot is what used to be called Macola Accounting. We were connecting to a Pervasive SQL DB using an ODBC connection. We were trying to link the Item file to a second instance of the Item file to get a list of components for manufactured items. What we found is that when we joined the component ID from instance one to the part ID in instance two, the results would not return a single match between the two tables.

Looking at the tables separately showed the matching data was there. And when I tried to filter to a single PartID the results would not find that ID. This is when I realized that we had an index problem like the one I described above.

So I looked at the index tabs in the Database Expert and noticed that this table had two red index tabs, meaning there were two fields in the primary index. The tabs were on Item Number and Sequence number. We were linking from a table where there was a component item number but there was not a component sequence number. It appears that Pervasive SQL defaults to using the primary index for ODBC joins, even if the fields you are using for the join don’t completely match the fields in the index. So the link will fail every time. I even unchecked the option “use indexes or server for speed” to see if that would help, but it didn’t have any affect.

We were lucky that the table we were using had an equivalent view. We linked this view to itself and we were finally able to find matching records. I assume this worked because this view, like most views, are not indexed.

I solved another occasional mystery today.

Crystal allows you to put your groups in order based on the summary fields that exist for that group. This feature is in the “Report” menu under the label “Group Sort”. So if you group by customer and subtotal sales for each customer you can put the customers in order based on their sum. You can rank the customers this way in either ascending or descending order.

You are allowed to use any type of summary field for group sorting, and on occasion I have used a summary that is based on a date field. This could be the first (minimum) order date of for each customer or the last (maximum) order date for each customer. I have noticed that sometimes when I rank a group based on one of these date summaries that Ascending seems to behave like Descending or vice versa. For instance I might pick Descending and I expect the groups with the latest dates to be first. Sometimes they are and sometimes they aren’t. Before today I had never taken the time to figure out what was going on.  Now I know.

If your summary is the Maximum of a date field, like the last order date for each customer, then setting the group sort to ascending or descending will behave in the expected way. Ascending will put the groups with the earliest summary dates first and descending will put the groups with the later dates first.

But if your summary is the Minimum of a date field, like the first order date for each customer, then group sorting for that field will work in reverse. Ascending will put the groups with later dates first and descending will put the groups with earliest dates first. To get the groups to go the way you want you just have to pick the opposite direction.

A couple of notes:
1) This doesn’t happen with a minimum summary of numbers or strings, just a minimum of dates.
2) You can get the same summary value as the minimum by doing the Nth smallest (with N = 1). If you use the Nth Smallest summary for group sorting it does the ascending/descending the normal way, not reversed like the minimum function.

I first wrote about this issue a decade ago when it showed up in databases like Paradox, Btrieve and Visual Dbase. But I recently saw it twice in PostGreSQL environments so I am going to write about it again.

The problem occurs when you add a new rule to the selection formula. The main symptom is that the criteria works fine when you click “use saved data” but then when you refresh you lose some valid records. Some people think they have a bad join which can also cause you to lose records, but a bad join will lose records even if you click “use saved data”.

In my experience, the issue is usually connected with an index in the database.  When Crystal Reports sends your new rule to the database, the database tries to use an index to speed things up. But some indexes don’t work correctly for filtering and will cause the report to miss some or all valid records. The solution is to prevent that rule from being sent to the database by forcing the rule to be applied locally. There are three approaches I have used:

1) Starting with CR 2008 (v12) you can put this rule into the “Saved Data” selection formula. This selection formula is always applied locally so the database usually doesn’t see it. Crystal will apply this selection formula to the records that are sent back by the database.

2) The first option may not work in call cases, and of course it won’t work if you use an older version of Crystal that doesn’t have that feature.  In that case you have to use the method I wrote about in 2007. You write the selection formula in a way that forces Crystal to apply that rule locally.  If the report is SQL based you want to prevent Crystal’s SQL generator from converting that rule into the SQL WHERE clause.  The most reliable way I have found is by putting the database field inside a Crystal function. For instance if your problem rule uses  a numeric field in the selection formula like this:

{table.Amount} > 50

I would write it as

Round({table.Amount},2) > 50

Or if your selection formula is:

{Transaction.Code} = “ABC”

I would write it as

Left({Transaction.Code},3) = “ABC”

Applying a function to a database field in the selection formula will usually prevent that rule from being converted into the SQL.  In some cases I write the rule as a separate formula field called {@Criteria} and then reference {@Criteria} in the selection formula by adding a line to the selection formula like:

…. and {@Criteria}

Note that this is the exact opposite of what you normally want to do. Normally we want ALL of the record selection formula to be sent to the database or convert into the SQL WHERE clause.  It makes your query more efficient.  When I have a slow report one of the first things I check is for functions in the selection formula.  But it doesn’t help to be efficient if the database can’t handle the rule correctly.

3) I recently had one case where neither of the above options solved the problem. No matter how I wrote the criteria formula, as soon as it was part of the selection formula I would lose records.  So we gave up on selection and used suppression instead.  This is a last resort.  You don’t use the {@Criteria} formula in the selection formula at all.  Without the rule the report will include some records that you don’t want. You then suppress the unwanted records with a suppression formula like this:

not {@Criteria}

When you use this method you also have to make sure these records aren’t included in any totals. This means writing a formula that only includes the records that you want and then doing all totals on these formulas. For instance I could write a formula like this:

if  {@Criteria}
then {table.Amount}
else 0

If I total this formula it won’t matter that I have suppressed records in the report. I don’t see them and they aren’t in the totals.

You can create Crystal Reports that directly read CSV files using the Access/Excel(DAO) connection.  Just keep in mind that CSV files don’t always make an ideal data source. Like spreadsheets, CSV files don’t have set data types for each column. This can cause data type ambiguity which might cause you to lose some data. And in some cases the report will read the first record of the CSV file as the column headings, removing the first record of data from the dataset.

But here CSV files have one advantage over XLS files. CSV files allow you to introduce a Schema.ini file to define the data type for each column in the CSV. This is something you can’t do with spreadsheets. The schema.ini file is a simple text file that sits in the same folder as your CSV. There are many attributes available in schema.ini, but you only need to use the attributes that you need.  The other attributes will be set based on defaults stored in the registry.  Here are the most common problems I find that can be solved with a schema.ini file.

1. The CSV is reading the first row of data as column headings
2. Columns read as the wrong data type
3. Character column is read as numeric and shows only the numeric values
4. The columns are parsed using the wrong character

Here is an example of a schema.ini that defines two different CSV files in the same folder:

[sample1.csv]
Format=CSVDelimited
ColNameHeader=False

[sample2.csv]
Format=CSVDelimited
ColNameHeader=False
Col1=OrderDate date
Col2=Amount long
Col3=CustID text
Col4=CustName text
Col5=CustCategory text

As you can see, a single INI file can define multiple CSV files when they are in the same folder. Each file gets it’s own section of the INI file. Both files are set to be read as comma delimited.  Both files are set to NOT treat the first row as column headings. In the first file we allow the driver to name the fields (usually A, B, C, etc) and determine the data type automatically. In the second file we name each column and assign each a data type.

So if you are reporting on a CSV file and running into issues, using a Schema.ini file may help solve the problem.