So here is an odd bug I found. It happens every time in Crystal versions 9, 10 and 11(XI) but also happens in some reports in newer versions. The bug only affects SQL Expressions that contain a correlated sub-query. For example this expression works in both CRv8.5 and CRv12 (2008) but fails in all of the versions in between:

(SELECT Min (CAL.ONDATE)
FROM GM6.dbo.CAL CAL
WHERE CAL.ACCOUNTNO = Contact1.AccountNo)

The expression is designed to show the first appointment in the Cal table for each contact in the report. Using a SQL Expression avoids duplication I would normally get by adding the Cal table to the report directly. Run this expression in CR versions 9, 10 or 11 and you get this error:

Error in compiling SQL Expression.
The multi-part identifier Contact1.AccountNo
could not be bound.

At first I thought this error was coming from SQL. But as a test I took a v12 report that had this expression and opened it in v10. The report ran fine. But if I then try to edit the SQL expression or even check it, I get Continue Reading »
SQL expression bug affects only some reports

There are many ways to deploy Crystal Reports to users. I normally lean toward the simpler and less expensive options, like locally installed viewers, or scheduled delivery of PDF output. But there are environments where a server based option is necessary. The “official” options from SAP are Crystal (Reports) Server and BO Enterprise. You can read about these on the SAP website. But there are other, less expensive products out there that many users never see. These are third party products that allow you to centrally manage your report deployment from your server.

There is a page on my blog that lists and compares these products, and I update it every January. This year the list includes the same 7 products from last year and one new product that was just released: Continue Reading »
Server based deployment options compared (2013)

I recently needed a formula to calculate the number of days in a date range, while excluding all of the Sundays in that range. My plan was to calculate all the dates in the range, then calculate the number of Sundays in the same range, and use the difference. To get all the dates I would use:

{@EndDate} - {@StartDate}

Depending on your environment, you may need to add one day to the result. In an environment that involves overnight stays (hospital, hotel, etc) anyone who checks in one day and checks out the next day would only be counted as one day, so the formula above would be correct. But if you are talking about most other things that start on one day and end on another day (attending a class, dates of coverage, etc) you will have to add one day to the result like this: Continue Reading »
Days between (excluding Sundays)