Contents for September 2011:
** Update to my comparison of Crystal Reports Viewers
** Helpful features of the linking window
** My library of Crystal Reports materials
** Using CRLogger.dll to troubleshoot database issues
** Resetting SQL Errors
** Let me create your Crystal Reports
** Why does CR sometimes add '1' to a table name?
** CUT Light UFL provides distance calculation
** Open enrollment Crystal classes in Frederick, MD
** Ninite.com installs and updates free software applications.

Other recent blog articles:
    Free copy of Report Runner by Jeff-Net
    CR XI dynamic parameters in CR Server 2008
    

Crystal Reports viewers

You use Crystal Reports to create, change and run reports. But what if you have a user who doesn't need to create reports or even change them. This user just wants to refresh the reports and view/print/export the results. Does he need another copy of Crystal Reports? Do you need to configure an expensive web server?

The most cost effective method for letting users run reports on demand is to install a third-party client-based viewer. And I am amazed at how many Crystal Reports users are completely unaware that these are available, despite the fact that they are offered by a dozen different vendors. Some users the "Viewer" that is put out by SAP and get discouraged because it won't allow refresh.  But every third party viewer I know of allows you to refresh the data.

So every September I review the different viewers available on a fixed page on my blog. This year there are 9 vendors in the review.  The review page provides a brief introduction to each product including what sets it apart.  I have also created a detailed feature matrix (PDF) that shows some of the specifics for comparison like prices and the install base.  There is even a glossary of features in case you aren't familiar with the terminology. The tools being compared are:

    Crystal Corral by Groff Automation
    cView by Chelsea Technologies
    ViewerFX by Origin Software
    RV by Climate 27
    Report Runner Viewer by Jeff-Net
    Logicity Pro by SaberLogic
    Easy View by Easy Street Software
    RTag Report Viewer by RTag
    DataLink Viewer by Millet SW

If you have already tried one of these products, or are currently using one, I would love to get your feedback.  

Helpful features of the linking window

I just discovered 2 features of the linking window that make it easier to work with a large number of tables.   Some of you may have already found these but maybe I am not the only one that overlooked them:

Change Linking View:

If you right click in the background of the linking window there is an option to "Change Linking View".  This collapses all tables to just their headers and shows a single line join for all links.  I find that this is great for a big picture view of the tables.  To see individual fields for a table you can double-click on the heading of a table and it opens up that table to normal view.  Another double-click on the heading collapses it back down again.

Locate Table:

If you have lots of tables and you don't want to scroll around to find a particular one you can right click in the background and select "Locate Table".  This will give you a list of the tables in the window in alphabetical order.  Highlight any table in the list and it takes you directly to that table.   This is especially helpful when you have really long table names and they are wider than the tables, making it tough to distinguish tables with similar names.

My complete library of Crystal Reports materials

Do you want to really understand Crystal formulas?  Do you know when to use the four different methods to add totals? Why not let me explain these Crystal Topics to you with one of my Expert's Guides.  Each guide comes with clear explanations and sample files to illustrate the concepts.

    Expert's Guide to Formulas  ($36)
    Expert's Guide to Subreports, Parameters and Alerts ($28)
    Expert's Guide to SQL Expressions, Options and Commands ($26)
    Expert's Guide to Totals ($24)
    Expert's Guide to Cross-Tabs ($22)
    Expert Techniques Vol. 1 - 4  ($19 each)
    Quick Reference to Crystal Reports in Visual Basic ($16)
    Quick Reference to Crystal Reports in .NET ($14)

You will find these on the LIBRARY page of my site.

Using CRLogger.dll to troubleshoot database issues

There is a technical article in the SAP library that caught my attention recently.    It mentions something called CR Logger and suggested that it could help you troubleshoot database error messages.  But when I went to read the article I was disappointed to find that there was no information on how to use it, when to use it, or what it would do for you.  So I decided to try it and see if I could see some value in it.

First you set it up (see my blog post for more on this).  Then every time Crystal hits the database (refresh, verify database, show SQL query, etc) a whole bunch of actions are logged in a pair of log files.   A new pair of files is created whenever you close and open Crystal.

The real question is how to use the contents of the log file.  It does show the SQL Query and if it was successfully run in the database, but this is buried in hundreds or even thousands of lines of repetitive data. It seems to detail every library file used in the connection process. It also seems to return a line for every record in the results.  It might be that this is intended to help SAP understand what is going on within your system during a support call.  Otherwise, the value of the logs didn't immediately jump out at me.   I would be interested in hearing from someone who actually got some value out of this process.

One more note.  The size of the log entry is related to the number of rows returned, so it makes sense to do your testing with a small result when possible.  And it is even more important to turn the logging off when you are done or the log files can get very large, very quickly.  You disable logging by renaming the CRLogger.dll (ie changing the extension to .ORG).

Resetting SQL Errors

I have had several instances recently where a change to a report caused Crystal to generate strange SQL.  Sometimes you can see the problem under "Database > Set Location" where you will notice two different instances of the same database in the upper window.  Or, you may notice that your SQL Query gets separated into two or three independent queries when it should all be in one query.  In a few cases the SQL gets so bad that it generates an error from the database, or it generates a "Cartesian product" result, trying to return all combinations of records between the tables.

If you run into a situation like this, the following steps might help:

1) Switch to design mode so that you don't launch a query with every change.

2) Go into "Database >Set Location" and see if you have two different connections that should be one.  If so, set the location of both instances on top to the same connection on the bottom.  This might seem redundant since they appear the same, but your goal is to eliminate duplicates in the top half of the "Set Location" window.  When you set them both to the same connection you should see them consolidate into one list.

3) Go into Database > Show SQL Query and see if you have two different SELECT clauses that read data from the same connection.  If you do you might want to try step 2 again.

4) If that doesn't help, you may need to remove a problem field or table from the report temporarily and then re-add it. Make sure it isn't used in a subtle place like sorting. I also found that it helps to refresh the report before re-adding that field/table so that CR is forced to generate new SQL.

In one case I saw recently someone had removed a table and replaced it with another table, instead of using "Set Location".  One field from the table they removed was still being used as a Group field in the report.  Rather than generating an error the report added a separate query to the SQL to pull that field from the original table.  But, since that table was already removed from the report there was no way to remove it again.  And changing the group to use the field from the new table didn't reset the SQL, since the table and field names were the same. Removing the group was only a temporary solution.  If we added that group back in from the new table the report still wanted to pull it from the original table.

I finally solved it by grouping on another field from a different table (temporarily).  Then I wrote a formula to use as the group field and put the original field into he formula.  This cleaned up the SQL.  My guess is that the SQL was trying to convert the group field into an ORDER BY and that part wasn't being reset correctly.  By grouping on a formula field I prevented CR from putting an ORDER BY into the SQL.

So, if you experience any strange SQL behavior you can start by trying the above approaches.  If after those you are still stuck and you want a professional to take a look at what you are facing, give me a call.

Let me create your Crystal Reports

There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert?  Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done".  And since I am also a teacher I am happy to explain to you how the techniques work together.

I can also review existing reports that break, or run slowly, or seem overly complex.  Let me have a look at them and see if there is a more elegant solution. 

Why does CR sometimes add '1' to a table name?

There was a recent SAP knowledge base article that I found interesting.  Whenever Crystal generates a SQL statement it always creates an alias for the table. Usually this alias is the same as each table name so some users don't realize that an alias is being used.  Technically it is the alias, not the table name, that you see in the field lists in CR.  This way, if you add the same table to the report a second time, you will be able to tell the two instances apart. The new table's alias will default to something like table_1 and you can change the alias to anything you want.

But sometimes I have seen that CR adds a 1 (without the underscore) to EVERY table used and I never knew why.  According to this article, there are some ODBC drivers that do not allow you to have an alias that is exactly the same as the table name. Crystal checks the ODBC driver to see if the alias has to be different, and if it does then it adds the '1'.  This doesn't create a problem but it does confuse some users who don't know why their table names show differently in CR than they do in the application or in SQL Queries that do not use aliases.  It is a function of the ODBC driver.

This also sheds some light on a problem that I mentioned in a previous post with errors related to a table alias.   There must have been a difference in the ODBC driver or its configuration.

CUT Light UFL provides distance calculation

Millet software has recently added some new features to the CUT Light UFL.  Cut Light is one of several DLLs that you can install to let your Crystal Reports formulas do all sorts of interesting things.  For example you can have formulas that carry a value from one report to another, launch an application, run a batch file or execute a SQL statement.   You can read this page for a more complete list of what they can do, and for more information about CUT Light.

A few months ago Millet Software released version 5.2 of CUT Light with a handful of new features.  The one that got my attention is the ability to calculate distances between 2 zip codes or between a pair of Latitude and Longitude coordinates.  It can even convert zip codes into coordinates.  The calculated distances are direct surface distances – not driving distances – but I can still think of several uses for this in reports, like sorting locations based on their distance from an input zip code.

Note that the function that converts zip codes into latitude/longitude uses a web based look-up, so it needs web access to work.  I also found that it stopped working after a few tests, because the web site limits the number of requests per day from the same IP.  My workaround was to download a zip code database that included longitude and latitude and then look up the coordinates for that zip code myself.  That eliminates the web lookup so it goes much faster and doesn't hit any limits.  Ido is looking into other ways to solve this.  If you want a zip code database that has longitude and latitude coordinates you can try these free sources:

http://www.boutell.com/zipcodes/
http://download.geonames.org/export/zip/

If you think up a creative use for this, drop me a line.  

Open enrollment Crystal classes in Frederick, MD
 
Stop struggling with Crystal Reports and learn how to use it fully.  Come join me in one of my August classes and learn how to make Crystal work for you.  My Intro class makes sure you know all of the basics.  We even include material on cross-tabs, charts and formulas.  The Advanced class shows you how to solve reporting problems with running totals, subreports, parameters and commands.  The material is good for any version.  See my web site for course outlines and dates.

So what makes my classes different?  I have written my own course materials and have used them to teach over 2,500 satisfied students.  And, I give you a toll-free number so you can call me after class with questions at no charge.

Or, if you want to schedule a class at your office, using your data, that is my specialty.  I have found some stellar instructors in the the US and Canada that deliver my class for a very competitive price.  They are based near Spokane/Seattle, LA, Omaha, Detroit, Tampa, Philadelphia, New York City, Boston, Vancouver and Montreal.  Call for details.

Ninite.com installs and updates free software applications.

This isn't strictly a Crystal topic, but it is a website that immediately earned a warm spot in my heart.   I was working with a customer who needed a PDF printer driver for extra wide exports, and I suggested CutePDF.  But instead of going to their site he went to ninite.com.  He checked off "CutePDF" from a list of about 90 free applications, downloaded an installer and then installed it in one click. I was impressed.

So I looked at the list of free applications and noticed several tools that I use were on that list, along with a few I have been meaning to try.  So I went to Ninite.com myself, checked off a dozen or so items and downloaded my own automated installer.  That installed all 12 of the applications in one click.  Those that were already installed were automatically updated to the latest version.  I was even more impressed.  No extra clicks, no extra tool bars, no figuring out which version you need and it is all on one site.  I can rerun the same installer next month to see if any of these tools have an update or a later version.  If so it will be installed automatically.  Simple, elegant, free and effective.

And, thanks to Ron Ross, a Software Engineering Specialist at SPK and Associates for the heads up on this site.

Other recent blog articles:
 
 Free copy of Report Runner by Jeff-Net
 CR XI dynamic parameters in CR Server 2008

Contact Information: