Updated comparison of Crystal Reports viewers (2013)

You use Crystal Reports to create, change and run reports. But what if you have users who just need to refresh/view/print/export? Do they need copies of Crystal Reports? Do you need to configure an expensive web server?

The most cost effective method for letting a user run reports is to install a third-party locally installed viewer. Many Crystal Reports users are still unaware of these despite the fact that they are offered by a dozen different vendors. Some users get sidetracked by the "viewer" that is put out by SAP because that viewer won't refresh reports. Don't let that fool you. I have listed 12 viewers and all allow you to refresh reports.

And every September I compare the features of these viewers and post the results. The comparison page provides a brief introduction to each product including what sets it apart. There is also a detailed feature matrix (PDF) that shows some of the specifics for comparison like prices and the install base. I have even written a glossary of features in case you aren't familiar with the terminology.

This year there are 12 vendors in the review. However I am very concerned about one of them.  The vendor for EasyView (EasyStreet Software) has gone MIA.  I have Emailed and telephoned and have not received a response for over a year. At least two other people have shared similar experiences.  So, if anyone has been in contact with them recently, please let me know. In the meantime I have added a line in the matrix to indicate year of last contact.  The current vendors are:

Crystal Corral by Groff Automation
cView by Chelsea Technologies
RPTView by Pursuit Technology
ViewerFX by Origin Software
Crystal Kiwi Viewer by Crystal Kiwi
Report Viewer Pro by Report Viewer Limited
RV by Climate 27
Logicity Pro by SaberLogic
Report Runner Viewer by Jeff-Net
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 know what you think.

Crystal Reports and Windows 8

I replaced my workstation (laptop) a few weeks ago and decided to try Windows 8. I ordered it from Costco so I could just take it back if it didn't want to run my favorite [old] software.

To my surprise, I was able to install and run 3 different versions of Crystal (v8.5, v10 and v12/CR 2008) with no problems at all.  When I run Crystal 8.5 on my retiring Windows 7 box, I get an approval dialogue every time, but otherwise it runs fine.  On Windows 8 there isn't even the dialogue.   Even my old version of Office XP (yeah, I know) installed and ran just fine, and it is from 2002.  So now all I have to do is learn to live with tiles.

My complete library of Crystal Reports materials:

Do you want your reports to prompt you for choices that affect the data and layout?  Have you learned to use cross-tabs to recap your data in a different way? Do you know when to use SQL Expressions or Command objects?  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.

Aligning and sizing a group of fields

Customers watching me work on their reports will sometimes see me use a shortcut that is new to them. Usually these are shortcuts that have become second nature to me so they happen in a blink and the customer has to ask me how I did what I did.  Two of the most common are the "Align" and "Size" options in the "format" menu.  If you have a row of objects that are not even or a column that is out of alignment you can use these options to get them back in place.  Just select a group of objects in a row or column. Then go to either Format>Align or Format >Make Same Size.  Each will give you a list of choices that you can apply to that group of objects.

I often do both together, and in that case you should adjust the size first. This is because changing the size may change the alignment points.  So if I have a row of objects to align vertically I will first make sure to set them all to the same height.  Then I will align them using "Tops" or "Baseline", depending on what is needed.  If I am aligning objects in a column I will first make sure that they are all the same width, then align them to their left or right edges.  Even changing the font making text bold can also change the width of a column, so do that before aligning.

And in both cases you have to pay attention to which field has the sizing handles. This object is the object that sets the alignment and size for the others.  If you select the objects by using Shift-Click then it will be the last one you select.  You can select another field in the group by clicking on it without holding the Shift key.  It will then have the focus and will set the alignment point or size for the group.

One other related shortcut is useful when you want to align all the objects in a section, like details.  If you right-click on the name of any section (in the left margin), you will see an option to "select all section objects".  This is quicker and more reliable than a trying to shift-click them or lasso them. Just note that this will also include graphic objects, like lines, that may prevent the group formatting functions from working.

Crystal Reports and rounding errors

This question usually comes up related to subtotals and grand totals. Someone will wonder why subtotals on a report don't add up exactly to the grand total. What they are seeing is a classic 'rounding error'. It is defined as "a miscalculation that results from rounding off numbers to a convenient number of decimals", usually when adding up the numbers. This 'error' is actually older than computers. It appears regularly in financial statements because the ledgers used to generate the statements have cents but the statement is shown in whole dollars. It is even more obvious when ledgers are shown in thousands. Often a note is included that says "numbers may not add up due to rounding".

But the key thing to remember is that despite the term 'rounding error', there isn't any error at all. It only looks like an error to those who forget about the hidden dollars or cents. Here is a simple example.

Say you have 12 ledger accounts and they all have a value of $13.33. You want to report in dollars so they all appear as $13 on the report. Now lets pretend they are grouped into 3 groups with 4 records in each group. The group subtotals would actually be $53.32, which would each be displayed as $53. Your grand total would actually be $159.96 but would display as $160. All of the numbers displayed are as accurate as they can be without showing cents. There is no 'error'. Yet if you add up the three visible subtotals you would get $159 and if you add up the details you get $156. So the rounding "error" is just that the displayed numbers may not look correct, even though they are correct.  If that doesn't make sense you can go to my blog article to see a visual example of the numbers above.

So what are your options?

1) Educate the users that the numbers are correct. This gives you numbers that are as accurate as they can be, without showing decimals.
2) Round at the detail level. If you round every single detail to the same level as the totals, then all the totals will add up consistently. But they will all be off slightly( or not so slightly).
3) Use formulas to round at the subtotal level and then use variables to add up the rounded subtotals to create the grand totals. This is a bit more work but it gives you grand totals that match the subtotals. There is less distortion here than when rounding at the detail level.
4) Show everything with all the decimals. Very precise, but not very convenient.

I prefer the first option, which is what you find in most financial statements, along with the note.

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.

Adding Google Maps to reports

Ido Millet of Millet Software showed me how to take any address in a report and use it to generate a map on that same report. The technique relies on a combination of two different features:

1) Crystal's ability to show a dynamic image based on a path to that image
2) Google's ability to generate a map image based on a URL with parameters

You write a formula that uses the address fields to generate a Google compatible URL. You then set that URL to be the dynamic path for the image. The Crystal image object goes to that URL to fetch the image. Requesting the image causes Google to generate the map image and send it back to the report. Here is a simple example of the formula:

"http://maps.google.com/maps/api/staticmap" &
"?center=Leesburg, VA" &
"&zoom=11&size=512x512" &
"&maptype=roadmap" &
"&markers=color:0x99FFFF|label:X|525 East Market St, Leesburg, VA" &
"&sensor=false"

In this simple example the address is hard coded. See below for calculated examples.  Also note that the "Center" and "Zoom" arguments are optional. Google will select a value if you don't specify it.

Here is a slightly more sophisticated version using four formulas. The first is just the city state combined for use in the 'center' property. The second generates a single address. The third appends all the address values into one long marker string. The last combines everything to create the URL, including all of the markers:

// CityState
{Customer.CITY} & ', ' & {Customer.STATE}

//Address
{Customer.ADDRESS1} & ' ' & {@CityState} & ' ' & {Customer.ZIP}

//Markers Accum
WhilePrintingRecords;
StringVar Markers;
NumberVar Counter := Counter + 1;
Markers := Markers & "&markers=color:0x99FFFF|label:"
& Totext (Counter,0) & "|" & {@Address} ;
Counter

//Image URL
WhilePrintingRecords;
StringVar Markers;
"http://maps.google.com/maps/api/staticmap" &
"?center=" & {@Address CityState} &
"&size=512x512" &
"&visual_refresh=true" &
"&maptype=roadmap" & Markers &
"&sensor=false"

To use this last formula to generate a map you have to:
1) Drop an image on the report as a placeholder.
2) Right-click on that image and select Format Graphic.
3) Go to the Picture tab.  
4) Click the condition button called "Graphic".  
5) Use the last formula field above as the entire condition formula and click OK.  

When you preview the report, the image should turn into a Google Map.

A couple of additional points on markers:
I created a counter in the Accum formula above so that the label for each marker has a unique number. If you want to use letters (A, B, C, etc) then replace this:

Totext (Counter,0)

with this:

CHR (Counter+64)

This changes 1 into A, 2 into B, etc.

Also, you can specify any color that you want. There are some basic colors you can specify by name (Red, Blue, Purple, etc), or you can pull up a color chart and specify an color code like I have above.

For more information on this you can refer to the Google Map API documentation.

Converting Crystal formula logic into SQL queries

I recently had to convert a complex Crystal Report into a SQL Query, including all the calculations. I eventually found ways to convert everything that was needed and learned some valuable SQL skills. But I also learned to appreciate the things I take for granted in Crystal syntax, things that have no equivalent in SQL syntax. Here are some examples just in case someone else has to do a similar conversion:

1) One huge difference is how simple it is in Crystal to reference one expression in another. Crystal will automatically calculate a series of dependent formulas in the right order, based on which formulas refer to others. In a SQL query, if you write a calculation for column A and you want to use that as part of the calculation for the next column, you can't simply refer to column A by name. Instead you have to repeat the entire calculation.

The exception is if you use subqueries. You can write a simple query that calculates expression A and then refer to that query in a higher level query. For some of my more complex formula chains it took 6 layers of nested subqueries to get the logic we needed. A simple example is shown in Item #2 below.

However, this highlights one advantage of SQL – you can nest as many levels as you want. And, any level can calculate summaries of the prior level. This allows you to do summaries of summaries of summaries, etc. In Crystal you get one pass for summaries, then one pass for variables and you are done.

2) My second example is somewhat related. SQL will not allow the result of an expression to be TRUE or FALSE because SQL Server doesn't have a Boolean data type. The closest is a bit datatype which has a value of 1 or 0, but this isn't the same because a bit can't be used as boolean condition without a comparison.

I miss these because I often write Boolean formulas like this:

//Qualified
{Code} = "X" and {Status} = "A" and {Period} = 123

I can then refer to these Boolean formulas in other IF THEN statements, which makes the logic very clear:

If {@Qualified} and {@OverDue}
then….

But in SQL the closest I could get to the first formula is something like this:

SELECT
CASE WHEN CAL.CODE = 'X' and CAL.STATUS = 'A' and CAL.Period = 123
THEN 'True' ELSE 'False' END as Qualified ….

Then if I wanted to refer to that expression in another expression have to use a subquery as mentioned above, like this:

Select
CASE WHEN A.Qualified = 'True' and A.DaysOver > 0
then 'Qualified' else 'Not Qualified' END as QualText
from ( SELECT
CAL.DaysOver,
CASE WHEN CAL.USERID = 'KEN' THEN 'True' ELSE 'False' END as Qualified
FROM CAL) A

3) Crystal has the ability to automatically set individual formulas to deal with the null values as if they were empty strings or zeros. It gets to be very cumbersome in SQL to check for NULL values in every part of every calculation.

4) Crystal has a a VAL() function which is very effective at turning numeric strings into numbers. In SQL you can use CAST or CONVERT but both of these will fail if the field has any non-numeric characters. The Val() function will just give a zero for a value that is not numeric and will even take the numeric portion from the beginning of a string (like 45B) and stop at the first alpha character. Val is much more forgiving than the CAST/Convert options.

5) Crystal has CASE logic, but I find IF THEN logic much clearer to read and follow. This is just my preference but I really miss using IF THEN statements in SQL.

I am sure I will think of more, but these are the ones that came to mind on this project

Open enrollment Crystal classes in Frederick, MD

Stop struggling with Crystal Reports and learn how to use it fully.  Come join me in my November 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.

Windows that open off-screen

I have written before about Crystal windows that end up off screen.  This often happens when you open something like the Database Expert or the Formula Editor.   This happened to me the other day when I was using my laptop outside of my office and so my second monitor wasn't available.  It can also happen if you switch from a high resolution mode (which shows a larger workspace) to a lower resolution mode.  When I wrote about this before my solution involved digging into the registry.  Since then I have found some keyboard shortcuts that allow you to move a hidden window back into the viewable area.  One of these 3 things should work:

1) Hold down the Windows key and hit the up and left arrows until the window appears in your viewable area
2) Hit ALT-Space, then hit the X.  This will maximize the window in your current screen.
3) Hit ALT-Space, then hit the M.  This will put you in 'Move' mode and you can use your up and left arrows as mentioned above.

Invalid printer message on a home-grown report

I solved a mystery this morning that has bugged me for a long time. As you can imagine I use lots of reports to run my business. I just noticed that half of them give me the "invalid printer" message when they are opened. This message usually appears when you move a report from one PC to another that has different printers. Because I fix other peoples reports on my PC, I see this message all the time, so I am used to ignoring it. But I only have one printer so it doesn't make sense that I should see this message on my own reports. The normal fix (selecting an existing printer) doesn't work here because these reports are already pointed to an existing printer.

The message is also harmless. It doesn't prevent the report from running. It just adds an extra click every time I open a report. That is why I ignored it to the point where it became invisible. But after one of my customers asked about the message I decided to see what it took to get rid of it.

So the next time the message came up I selected a different printer on my PC and hit 'apply'. I then reselected my main printer and hit 'apply' again. This fixed the problem on almost all reports.

On the few occasions where it didn't fix the problem I clicked the printer 'preferences' button. This doesn't appear for all printers but my Brother brand printer has a button called preferences. In there I found a setting that sets the printer preferences to the default. This also fixed the problem, so I used that method on reports where the first method didn't work.

Gems from the Archives:
How to move values from the last record to the Report Header (June 2002)
Why can't I total this formula? (July 2002)

Contact Information: