Archive for January, 2013



SQL expression bug affects only some reports

Saturday 26 January 2013 @ 12:14 am

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




Server based deployment options compared (2013)

Sunday 20 January 2013 @ 11:09 pm

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)




Days between (excluding Sundays)

Friday 11 January 2013 @ 5:09 am

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)




Comparing Pentaho to JasperSoft

Thursday 10 January 2013 @ 4:04 pm

I think it was in 1996 that the Crystal Reports part of my business took the lead over the R&R Report Writer part. Every since then I have been looking out for the next tool to take the lead.  So I like to stay exposed to the alternatives to the Crystal Reports product line.  With that in mind I found an interesting article comparing Pentaho and JasperSoft as alternatives to the Business Objects and Oracle product lines. These are not so much competitors to stand-alone Crystal Reports, but are more comparable to the server level tools like CR Server or BO Enterprise.  Both Pentaho and JasperSoft  offer a free ‘open source’  version as well as a commercial version, which makes them appealing to small businesses.   Of course the cost of the software is only part of the equation and you have to factor in the time it takes to install and configure these tools, and the overhead to maintain them.  I find that very few of my customers need  ‘full stack’ solutions like these.  I just like to stay informed.




Jeff-Net

Recrystallize Pro

The Expert Series