The Crystal Reports® Underground

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 v12 (2008) need to use the 32-bit ODBC Administrator. But in Windows 7 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.

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

I recently wrote about a post about controlling the color of multiple objects from one formula.   One of my newsletter readers took the idea and ran with it and designed a custom function for his repository:

========================================
Just got the ‘Underground News’. The tip on creating formulas for set colors is fantastic – never occurred to me either. Opens up all kinds of possibilities – kind of CSS for Crystal Reports. So now I can
Continue Reading »
More on controlling colors from formulas

One of my customers recently asked me how quickly I thought people were going to the newer versions of CR.  I didn’t really have any basis to answer him, so I was pleased last week when one of my LinkedIn groups started a survey, asking people to select their primary version of CR.  After nearly 100 responses I was surprised to see the persistence of version 11 (XI) which is shows up as the primary version in use.  This is a full 7 years after XI was released.

Only then did I remember that I ask my newsletter subscribers to list their version when they sign up.  So I did my own analysis based on the 1200 users that answered this question in the past 5 years.  I used the same 5 categories used in the LinkedIn survey.  You would think that since I am only counting new subscribers each year, that the data would skew slightly toward newer versions.  But version XI is still a clear winner, even when looking at the new subscribers so far this year.

Also note that as version 14 starts to appear, its increase seems to come at the expense of version 12 more than version 11(XI).  I currently don’t have an explanation for the persistence of version XI (I prefer v12/CR 2008) but if I come up with a plausible explanation I will share it.

Sometimes there are text blocks on your reports that contain more then one line of text (notes, memo fields, comments, etc.).  When you format any multi-line text field, you have a tab of options called  ‘paragraph’ formatting. The first 3 settings on this tab allow you to determine how far the text block should be indented. Your indent options are “Left”, “Right” and “First Line”. This can be misleading because “Left” and “Right” are calculated from the left and right edge of the object, while “First Line” is calculated from the indent set in the “Left” position.

Take this example: You want the first line of text to be indented 1.0 from left while all other lines in the block are indented .5 from left. You might try putting 1.0 in ”First Line” and .5 in Left indent.  What you would get is an indent of 1.5 on the first line.  This is because it adds the 1.0 in ”First Line” to the .5 in “Left”. If instead you put .5 in both places, then the first line will indent .5 + .5 or 1.0 inches.

And what if you want a ‘hanging indent’ where the first line is not indented but the rest of the paragraph is indented .5? For this you would put in .5 for the “Left” indent and -.5 (a negative number) for the “First Line”. The negative number added to the positive nets to zero so that the first line has no indentation, while the rest does.

Got this last week:

“Ken, thank you for this comparison. I wish I had had it before I selected my previous Crystal Viewer…. I purchased quite a few licenses of a product and intended to schedule reports on it. The scheduling aspect never worked and no matter how many messages and emails I sent to the support people, no one EVER called me back or seemed to care that I was having problems. They literally ignored me.”

This vendor tells me that they responded to the Emails they received, and at this point the customer has moved on. However, if this customer had contacted me at the time, then their would have been no question about a response from the vendor. I review
Continue Reading »
Vendor Responsiveness (good and bad).