I recently got a chance to work on a project using Crystal Reports to read QuickBooks data. To read QB data from CR you have to use a special ODBC driver called QODBC put out by FLEXquarters. This an unusual driver in that when it queries the QB tables it displays a small counter panel for each table, showing the records returned from that table. If your report includes 6 tables and you will see a stack of 6 small windows pop open and close as QODBC assembles your data set.  There is a switch to hide them if you choose.

We found another quirk when tried to deploy the reports to users with a client side viewer program. The report worked fine in CR but would not return any data through the viewer. It took a while to find the problem.  Before QODBC can access QB data you have to run the first query while you are logged into QB as an administrator.  The instructions made this sound like a one time process, but we found that Continue Reading »
Crystal Reports and QuickBooks

This came up recently in a forum discussion and I was surprised at how many users were not aware of this.

Crystal Reports has a connection option called “File System Data” which turns any Windows file folder into a table, providing 65 fields for every file in the folder.  These fields include the common items like file name, extension, size and dates.  It also includes some that are specific to applications like the number of pages and the number of words for MS Word files.  If the file is a Crystal Report you have access to all of the  “Summary Info” fields like Title, Author, Comments, Revision Number, etc.

To use this feature create a new report and open the  Continue Reading »
Using Crystal Reprts to read a MS Windows file folder

I was teaching a lesson today about variables and evaluation times for a remote student.  One of the things I like to use during this lesson is a diagram from the CR help file.  It is called the “Multi-pass reporting flow chart” and is found at the end of the section called the “Report Processing Model”.    The diagram shows the specific steps that a report goes through from beginning to end, and which steps are considered part of each evaluation time.

So I was surprised to find that the diagram was not listed in its usual place in the help file contents.  I didn’t spend much time looking for it, since I couldn’t imagine that there was a more appropriate place.  I figured that the diagram was dropped from CR 2011.  It may be that it has been moved, but just in case, I decided to post it on my website.  That way if I am teaching a class on-site and I don’t have it I can still use it in class.

I won’t be able to check it here because after evaluating CR 2011 I decided to hold off on the upgrade.  There are so few changes that I didn’t feel it was urgent.    So if someone out there is using CR 2011 and finds this diagram somewhere in the help file, I won’t mind being proven wrong.

I read a forum discussion where someone was asking how they could have all the null values on the report print ‘n/a’. They were faced with having to write a formula field for every field on the report. I suggested that they could use the Display String property that is available when you format a field. The Display String allows you to override the value that will print on the report. The advantage is that you can use the function CurrentFieldValue within the condition formula and this makes it easy to apply to lots of fields at once.  I suggested that they use a Display String formula that was something like this:

If CurrentFieldValue = ”
then ‘n/a’
else CurrentFieldValue

My plan was Continue Reading »
Display strings and null values

Two different customers have contacted me recently because they were surprised to find a ‘new’ Crystal Reports feature in specific reports. They are usually puzzled as to why there is no way to make these features appear in ALL of their reports. Here are two examples:

1. You go into File > Report Options and see an extra setting at the bottom that says “Convert DateTime Field:” and there are three choices: To Date, To Date-Time, To String.
2. You go into the LINKS tab of the Database Expert and look at the properties of a join. There you see an extra section at the bottom with a check mark that says “Return all rows before joining” then below that you see:

When linking to two files from this file:
Look up both at the same time
Look up all of one and then all of the others
Look up all the combinations of the two files

Rather than being new features in CR, these are actually very old features, from Continue Reading »
Old reports with ‘new’ features?

I was troubleshooting a report that was taking a long time to get data from the database.  The report used the same table configuration as many other reports, and none of those reports had performance problems.  But when going in to check the links I noticed that there were two different connections.  Then I got an info message from CR that said the report was using two different data sources, which was strange.  So I went into “Set DataSource Location” and, sure enough, one of the tables was listed separately from the others, with a separate connection.  The funny thing was that the two connections seemed identical.  Same name, same properties, etc.

So first I took lone table Continue Reading »
Slow performance caused by a “split” DSN

When people enter date criteria they almost always expect to enter just the date value without times.  But most modern databases use a DateTime data type rather than a date.  If your data stores dates with times you have to be sure that you enter your date ranges correctly or you take chance of losing the data for the last day of the range.

The good news is that in most situations CR will make the adjustment automatically, but I found one situation where the adjustment doesn’t happen.  Say you have a transaction date that is a DateTime field, and you are trying to select the transaction for  January.  You might go into the select expert and  add a rule that says  Continue Reading »
How CR adjusts date values for DateTime fields

If your report uses tables, it is usually straight forward to point the report from one data source to another.  You use the menu item:

Database > Set DataSource Location.

Once in that window, you highlight the existing connection in the top half of the window.  Then you highlight the new connection in the bottom half of the window.  When you click “Update” the properties of the current connection (top window) should change to reflect the new connection.

However, this is not as simple when the report is based on a command, because with a command there is no existing table called ‘command’ for CR to find in the new connection.  The command only exists in CR.   So here are two workarounds that you Continue Reading »
Using ‘Set Datasource Location’ with a SQL command

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.

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.