How would you like your reports to be automatically run, exported to a PDF and delivered to your Email InBox every Monday morning at 6am? The Crystal Reports designer doesn’t provide a way to do this (unless you upgrade to CR Server or BO Enterprise). But if you look at third party products like those on my LINKS page you will find several reasonably priced or free tools that do this. Some do even more.  So every year I go through the list and publish a feature comparison on my blog.

I discovered and added one new tool this year, bringing the pool of reviewed tools up to nine. The article provides a brief introduction to each product and describes what sets that product apart. Then there is a detailed feature matrix that shows the key specifics for comparison, including prices and the install base. To clarify the matrix I have written a feature glossary to explain what each feature means. Finally there are links to the vendor websites so that you can get more information on each product.

In May I will be updating a separate article covering server based scheduling tools. If you think one person can manage all of your scheduling you are probably fine with one of these desktop tools, regardless of the number of people receiving the scheduled output. But if you plan to have multiple people scheduling reports then you may want to consider a server based tool.

All versions of Crystal Reports up to v14.2 (2016) need to use the 32-bit ODBC Administrator. But in Windows 7 and later you are taken to the 64-bit Administrator by default.  A couple of years ago I wrote about this.  Today after helping a customer I learned a few more strange things about the ODBC Administrator that can get confusing.

The user was creating DSNs but CR could only see the DSNs if he added it as a “user” DSN. If he added a new “system” DSN he could not see it. The odd part was that one existing system DSN DID show up in CR, but any new system DSN would not show up.

So the first thing I checked was that we were loading ODBCAD32.exe to add the new system DSNs. Then we recreated, reconfigured and retested the system DSNs. I also looked at the INI Files that ODBC creates and everything. So I was stumped. As I was telling them that I was giving up I checked the 64-bit ODBC shortcut I had added to my desktop. I was surprised to find Continue Reading »
Confusion over 32-bit and 64-bit ODBC

Here is a clever tip I just received from a blog reader who is also a Crystal Reports developer. He wanted to create a title with something that looks like the “text-shadow” property.  He found that using an image didn’t print as clearly as a true font so he came up with a clever way to create the shadow.  Here are the steps:

1) Put the title text into a large bold font.
2) Copy the title text object and paste a duplicate directly on top of the original.
3) Right-click on the copy and select “Move – To Back”.
4) Changed the font color of the copy to silver.
5) Nudge the copy slightly down and to the right.

There you have a clean shadowed font.  And thanks to Adam Butt of APB Reports in Trondheim, Norway.

Formula names can contain any characters you want, including numbers, symbols and spaces. But I just found a reason not to use a space as the first character of a formula name.

A customer recently tried to modify a formula in an old report, and was prompted to log into the repository. She sent me a screen shot of the messages, which said:

enter the name of your system (Central Management Server)

then:

Repository services are not available.

I figured that there was probably an old repository function in the formula.  So I asked to see the report and went through all the formulas.  There were no repository functions in any of the formulas.  And yet, when I right clicked on the first formula and tried to edit it – up popped the same message.  I was stumped until I noticed that the first formula name started with a space. So I switched to one of my own reports and renamed one formula so that it started with a space. When I right clicked on that formula and tried to edit it, voila!   I got the same messages. I ran the same test it in versions Continue Reading »
Don’t start your formula names with spaces.

I have written before about using Bar Codes in Crystal Reports, but recently two different customers have asked me about including QR codes on their reports.  For those of you who have not seen them, QR codes are the square scan codes that have small boxes in each  corner.  Here is a QR Code for my Email address:

The advantage of QR codes over traditional bar codes is that they have extra error checking built in, which means that even a slightly blurry scan, such as one done by a smartphone or tablet camera, can still be decoded and used.

There are several ways that you can incorporate QR codes into Crystal Reports.
1) If you have a single QR Code, or a small number of them, then you can use a free web based QR Code Generator to generate the image.  You can then insert the image into the report.  If you have several you could Continue Reading »
Printing QR Codes within your Crystal Reports

In most cases I find it more efficient to let Crystal generate the SQL for reports.  This makes the reports simpler for most CR users to create.  It is also easier to add additional fields, and the Visual Linking window makes the query structure easier to understand. Finally, in most cases, the performance is essentially the same since CR can generate workable SQL.

But there are plenty of cases where the only practical solution is to base the report on a SQL Command, instead of on linked tables. When I need to do a UNION of two or more queries, or when I need to apply a filter to the ‘outer’ table of an outer join, I have to resort to SQL Commands.  I am not very fast at writing SQL so I usually create a regular report first, using the correct tables, joins and fields. Then I copy the SQL that CR generates and use that as the starting point for my command.  Recently I find myself using SQL more, so I have started to pick up tricks that make SQL queries work faster.

Case #1

Last week I was working on a query that combined several fairly large tables.   The query was taking several minutes to return data and we were trying to see Continue Reading »
Making SQL commands more efficient