Last month I wrote an article about suppressing the page header on the last page when there are no details. This is handy if your last page is a subreport, a chart or a cross-tab. After my newsletter went out one of my readers shared her approach to the same problem. She uses the group header of a dummy group, and sets it to “repeat” on each page.

Any time you have a Group Header you can set it to repeat on each page. One feature of  a repeating GH is that it won’t appear on the last page of a group, unless that page has at least one detail record.  I wrote about that behavior in another article long ago.  So the only trick is to create a primary group that includes every record in the report. Then you set this group header to repeat on each page and it behaves just like a page header, with the exception of not printing on the last page.

So how do you create a group that includes all the records in the report? You group on a value that doesn’t change.  If you have a DB field like “company” that doesn’t change you can use it.  But you can always create a formula that isn’t tied to any data fields.  My favorite dummy group formula is:

WhileReadingRecords;
"All"

The word “All” can be any value. Just keep in mind it will appear as the overall node of the group tree so you might want it to make some sense. The WhileReadingRecords function allows the report to see this static value as a recurring value, which makes it eligible for grouping.

Once you create the formula you use it as Group 1 in the report and then go into Group Options and check “Repeat Group Header on each new page”.  If you put your column headings in this Group Header they will appear on every page, but won’t appear on the last page (unless there are details printing on that page).

And thanks to Tina Nordyke, the DBA for Advocates for Basic Legal Equality, Inc for suggesting this method.

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.

This week I worked with two different customers, helping them locate data buried in a large system with hundreds of tables. In both cases I had to use the table filter method to help me find the correct tables. For instance, when we needed to find the vendors table we looked first in the V section of the table list. However, in the system we were using all of the tables names had a 2 character prefixes for a dozen or more different modules. It wasn’t clear which module might contain this table. So I added a filter to the list of table names shown in the Database Expert. This way only table names that contain VEND somewhere in the name would be displayed. It made it easy to spot the correct table.
To add a filter to the table list you need to go into File > Options > (Database Tab).

In the middle section you will see two boxes on the right, and the top one is for table filters. It is labeled [Table Name LIKE:]

In this box you enter the characters you are looking for with a percent sign before the characters and/or after the characters. This percent sign is a wild card symbol. So since we only wanted to see tables that contained VEND anywhere in the name we put in a filter like this:

%VEND%

If I wanted all tables that have TMP on the end I could enter a filter with only one wildcard, like this:

%TMP

The only downside I have seen when using these filters, is that it is easy to forget about the filters. It then takes a bit to figure out why the table list is missing or incomplete. So don’t forget to take the filter out when you are done.