Crystal crashes with no error message

Sunday 13 October 2019 @ 9:09 pm

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.





Table filters in the database expert

Tuesday 8 October 2019 @ 9:06 pm

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.





Updated comparison of Crystal Reports viewers (2019)

Sunday 29 September 2019 @ 8:38 am

You use Crystal Reports to create, change and run reports. But what if you have users who just need to refresh/view/print/export? Do they need copies of Crystal Reports? Do you need to configure an expensive web server?

The most cost effective method for letting a user run reports is to install a third-party client-based viewer. They are offered by nine different vendors.  Don’t get sidetracked by the official SAP “viewer” because that tool won’t refresh reports.  Every viewer in my list allows you to refresh reports.

Every September I update the features of these viewers. The comparison page provides a brief introduction to each product including what sets it apart. There is also a detailed feature matrix (xls) that shows some of the specifics for comparison, like prices. I have even included a glossary of features in case you aren’t familiar with the terminology.

There are 10 active products in this year’s review and 4 “ghost” products that are mentioned as warnings.  A ghost product has a web site but it hasn’t changed in years and no one responds to requests for information.

The active vendors are:

Crystal Corral by Groff Automation
rptView by Pursuit Technology
CR Dispatch by APB Reports
cView by Chelsea Technologies
ViewerFX by Origin Software
CrystalKiwi Viewer by CrystalKiwi
Logicity Pro by SaberLogic
Report Runner Viewer by Jeff-Net
RTag Report Viewer by RTag
DataLink Viewer by Millet SW

If you have feedback to provide on any of these products, I would love to know what you think.





Suppress column headings when there are no details on the last page

Sunday 22 September 2019 @ 10:38 pm

Sometimes there are details printing on the last page and sometimes the last page is just the Group or Report Footer.  You may not want column headings on the last page if there are no details.  I have seen and tried several methods to accomplish this but not all are reliable. For instance using “OnLastRecord” as a suppress condition will work most of the time, but will also suppress the headers when the last page still contains exactly one detail record. I have found the approach below to be the most reliable.

First you split the Details band into A and B subsections.  You suppress Details B and use Details A for your visible fields.  Then you create the following formula field and place it in Details B:

WhilePrintingRecords;
BooleanVar Ending := OnLastRecord

Last you use the following formula to suppress the Page Header, or the part of the Page Header with column headings:

WhilePrintingRecords;
BooleanVar Ending

A Boolean variable is False by default.  The variable will only be True once the report has passed the last Detail A, which means that the last record has printed.  If there is a page header after that point it will be suppressed, because the “Ending” variable is now True.





Column headings scrambled in Excel export

Monday 16 September 2019 @ 9:20 pm

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.





Real rounding vs fake rounding

Sunday 8 September 2019 @ 8:00 pm

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.





How to turn records/rows into columns

Tuesday 27 August 2019 @ 7:32 pm

A customer recently requested an unusual report layout. They wanted the field labels in the first column on the page and each record to be a new column on that page. I remembered struggling with a similar layout request years ago and pulled up an old report from 2009. What I came up with then worked fine if you only needed one set of labels on each page, meaning that the columns were so tall that there wasn’t room to fit a second set below the first one each page. In 2009 I had half-height columns which meant two sets of labels along the left side of the page. That got very complicated.

Fortunately my current project had full page columns, which meant my 2009 solution was a perfect fit. Here are the steps:
1) Create a static value formula that can be used as a group. I usually use something like this:

WhileReadingRecords; 1

2) Group on this formula, and in the group options check “repeat Group Header on each page”
3) Make the group header about 6 inches deep and type all the field labels along the left side.
4) Make your details section about 6 inches deep and put the corresponding fields in.
5) Go into the section expert for the details section and check “Format with multiple columns”.
6) When the layout tab appears set the width of the column to an appropriate number.
7) Check the layout options: “Across then down” and “Format Groups with multiple columns”.

Note – you can do something similar with a cross-tab, but the additional pages for cross-tabs are all to the right on page 1 when in preview.  And the layout isn’t as flexible with a cross-tab.





Math tweet splits internet mathematicians

Tuesday 20 August 2019 @ 12:10 pm

One of my colleagues sent me a New York Times article about a math tweet.  The tweet was of a deceptively simple math equation and asked people to calculate the result.  All of the internet mathematicians immediately divided into two camps based on their interpretation of the rules of precedence, which were intentionally ambiguous in the equation. The point of the article (and of my colleagues sharing) was the importance of using parentheses to clarify the order in which things should occur. I often add extra parens, even when the order of precedence wouldn’t require them, just to give myself a visual clue of what is supposed to happen when.

The funny thing about the original article, and an interesting follow-up article, is that experts can still be found on both sides of the divide. That is somewhat surprising given the apparent simplicity of the problem.

And thanks to Zvi Flanders of Huron Consulting for sending me the link.





Calculating the begin and end of Daylight Saving Time

Friday 16 August 2019 @ 11:32 pm

I am not a big fan of Daylight Saving Time (DST). I even hear some states and countries are talking about dropping it (yeah!). But in the meantime there are plenty of reports that need to adjust the time twice a year. This usually happens when the datetime values are stored in Greenwich Mean Time (GMT) and have to be converted to a local time. Then you need to know when DST begins and ends.

The first two formulas below calculate the beginning and ending dates of DST, based on the year of your transaction date. The third formula uses the first two formulas to make the one-hour adjustment. Substitute your GMT date fields into the first and third formula.

//DST Start
DateVar Start:= Date (Year ({@YourDateTimeGMT}) , 3, 15);
DateVar BOM:= Start - Day(Start)+7;
DateVar BOW:= BOM - DayOfWeek(BOM) + 8;
DateAdd('h', 2, BOW);

//DST End
{@DST Start} + 238 ;

//Adjusted DateTime
if {@YourDateTimeGMT} in {@DST Start} to {@DST End}
then DateAdd('h', 5, {@YourDateTimeGMT})
else DateAdd('h', 4, {@YourDateTimeGMT})





When that zero isn’t really a zero

Thursday 8 August 2019 @ 10:24 pm

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.





«« Previous Posts
Jeff-Net
Recrystallize Pro

Crystal Reports Server