The Crystal Reports® Underground

rePORTAL Software has just released v5 of rePORTAL, and they are offering a limited time 20% discount through June 15.

rePORTAL is a a web portal and scheduler that distributes reports from Crystal Reports as well as SSRS and FRx.  You can read about the current version in my comparison of server-based schedulers.  Within the next week the comparison will be updated for the new version.  The new features include:

• Native support for 64 bit operating systems
• Support output to Fax and SMS
• Enhanced support for Crystal export options
• The ability to launch batch and command files before or after scheduled events
• A customizable user interface to highlight favorite items

One of my customers has reports scheduled to run every weekday. The rule is that all month long it should pull the dates for the current month. But once a month, at the beginning of the month, they want to run the entire previous month. So I wrote their selection formula to say “If today is the first of the month, then run LastFullMonth, otherwise run MonthToDate:

If Day (currentDate) = 1
then {DateField} in LastFullMonth
else {DateField} in MonthToDate

This worked fine for several months until we got to April.  Because the report only
Continue Reading »
Calculating the first weekday of the month

The folks at R-Tag.com have just released a new tool called CR Data Source which is designed to do just one thing. It lets you perform a “Set Location” on a folder full of reports, all in one stroke. The tool does just this one task, but then again it only costs $29, so it will probably be a popular choice for certain users. If you look at my review of RPT management utilities you will only find one other tool that does this, and that tool does lots of other things and costs about $400.

The current limitations are:

• It only works with ODBC/OLEDB
• It can only be used on reports that only have a single data source.

But even with these restrictions, the tool will still work on most reports.  And, if the tool catches on I am sure the folks at R-tag would be open to suggested improvements.  As always, if you try this tool please let me know what you thought.

If you have been considering purchasing Find it EZ then you might want to do it in the next few weeks. The developers of Find it EZ are offering my readers a 25% discount on purchases through 6/20/2012. Use this link to get the discount.

Find it EZ is a source code scanning tool that I have included in my comparison of RPT management utilities. It allows you to search your entire application, including your RPT files, for the use of specific tables, fields and text.

The current promotion is to highlight the release of version 4.0 which adds new features like team collaboration and
Continue Reading »
Discount on purchases of Find it EZ

Every fall I do a review of custom functions libraries that you can add into Crystal Reports. These are formula functions that are added into your formula editor and allow your formulas to do all sorts of wonderful things, like interacting with other programs and pulling in information from unusual sources.  It looks like I will be adding a new one in the near future.

Pursuit Technology, a consulting group in the UK, has recently released a function that allows your report to authenticate into your Twitter account and send a ‘tweet’ notification directly from a report formula.  The content of the tweet can be calculated in the report formula.  This can be handy if you use twitter as
Continue Reading »
Custom Function lets your report send a ‘tweet’

Exporting Crystal Reports to Excel can be tricky. I have written several articles on techniques to make it easier. But it gets even trickier when a subreport is involved.

Without a subreport I have found that you can get a clean export by using the “Data Only” option. But this doesn’t work with a subreport. I tried two different methods with the “Data Only” export that would not work.
Continue Reading »
Exporting to Excel when you have a subreport.

A customer recently contacted me with a strange problem caused by one specific report.   The report was generating SQL that caused the database to slow.  The could see the SQL statement in the log, but they were having a hard time determining which report was generating that particular SQL statement (they have lots of reports).   They asked if there was a tool that could search through the SQL generated by a group of reports to find a specific SQL phrase.

I was doubtful, because this SQL is not stored in the RPT file.  It is generated on the fly.  So to do this, a tool would need to open the report and connect to the specific data connection so that the correct SQL could be generated.  The only tool that came to mind was Find it EZ.  I wrote another post a few months back about how Find it EZ could search for tables and fields  in SQL Expressions and commands.  I wasn’t sure that it would work for the SQL generated on the fly by CR, but I figured it was worth checking.  I was pleased to find that Find it EZ will, in fact, do this.

They have also recently added the capability to search the saved data stored in a report for specific data values.  For more information about Find itEZ and other RPT file management tools, you should read  my review of this product category.

Every few months one of my customers will ask me if I have copies of their reports.  Usually a hard drive crash or some other system failure has made some or all of their reports disappear.  Only then do they realize that they have no backups.  When I work on reports in my office I have copies to replace the lost files.  But recently I am doing more work via remote control.  In those cases I work directly on the customer’s workstation, and so I don’t have copies of the reports to use as backup. So, do you have recent backups of your reports?

If you do, it is just as important that you do a pretend restore periodically.  That is the only way to confirm that your backups are any good.  I remember one customer who used a tape drive for backups.  He had followed an elaborate rotation schedule for the tapes and dutifully swapped the tapes out every week.  But when I checked one of the tapes I discovered that the  backup program had stopped running several months back.  No one realized that the tapes hadn’t been updated in months.  Another client was told that her IT department was managing her backups, but it turns out they were only backing-up the shared network drives.  Only after her hard drive failed did she realize that she had no backup of her local files.

The most recent was a customer who accidentally deleted an elaborate report we had done together.  It was the only copy.  That was when I first got the idea to write this post.  Then yesterday I learned that March 31st is World Backup Day (so you won’t be an April Fool).  The official site has all sorts of info on backing up your data. Do us both a favor and backup your reports regularly.

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