Archive for April, 2012

Exporting to Excel when you have a subreport.

Sunday 22 April 2012 @ 10:33 pm

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.

Searching your reports for SQL phrases and ‘saved data’ values

Thursday 12 April 2012 @ 2:56 pm

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.

Do you have your reports backed up?

Sunday 1 April 2012 @ 9:28 pm

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.

Recrystallize Pro