Contents for September 2012:
** Updated comparison of Crystal Reports viewers
** Why is CR Splitting into two versions?
** Named User Licensing in CR 2011
** My library of Crystal Reports materials
** Using Crystal Reports to read a MS Windows file folder
** Display strings and null values
** Let me create your Crystal Reports
** Using Visual Cut to turn Email messages into useable data
** DataLink Viewer's new 'click' interaction
** Open enrollment Crystal classes in Frederick, MD
** Report processing flow chart dropped from the CR help file?
** My class now available in Australia and New Zealand
    
Gems from the archives:
Printing check boxes and other symbols from formulas
The effect of dashes on the VAL() function

Updated comparison of Crystal Reports viewers

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 client-based viewer.  I am amazed at how many Crystal Reports users are still unaware of these, since they are offered by a dozen different vendors.  Some users get sidetracked by the "viewer" that is put out by SAP because it won't refresh reports.  Don't let that fool you because every viewer in my list allows you to refresh reports.

Every September I compare the features of these viewers and post the results.  This year there are 12 vendors in the review with three being added for the first time. 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 included a glossary of features in case you aren't familiar with the terminology. The tools being compared for 2012 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.

Why is CR Splitting into two versions?

When CR 2011 was released there was a parallel product released called "SAP Crystal Reports for Enterprise".  This is a version of CR that is tailored to work within the Enterprise environment. It is very similar to standalone CR but I haven't tested it myself for two reasons:  None
of my customers are using it and it requires the setup of a web server and BO Enterprise.  But initially it sounded like CR 2011 was the end of the line for 'stand-alone' Crystal Reports, based on statements like this from the product wiki:

"SAP Crystal Reports for Enterprise is the foundation for all future releases of Crystal Reports."

But this has lots of wiggle room.  I have a hard time believing that they will stop producing a stand-alone product.  Additional wording that has been added more recently seems to indicate that standalone CR will be around for a while:

"Even when we have closed the major gaps between Crystal Reports for Enterprise and Crystal Reports 2008, we will continue to deliver and support minor enhancements to the Crystal Reports 2008 / Crystal Reports 2011 line of products. This will be required as not every feature from the designer and associated SDKs will be moved into the Crystal Reports for Enterprise product line."

I even heard from "Influence Council" of the official ASUG user group, and was told that they are making sure that the needs of us "LEGACY" users are part of the ROAD MAP.  So it sounds to me like there will be parallel products indefinitely. If Enterprise ever becomes the only way to use CR then I will start looking for another tool.  I am sure that I won't be alone.

Named User Licensing in CR 2011

A colleague in LinkedIn was concerned about the wording in the CR 2011 license.  He had read my article on NULs (Named User Licenses) and it wasn't clear if this was still applicable to CR 2011.  I read the license and figured it was worth clarifying again, so I sent the following message to sales at SAP:

What I am looking for is a copy of the EULA (the license agreement).  I want to see for myself if the NUL license model has changed before I tell my customers and readers whether or not upgrade.

You see in CR XI and CR 2008 a user is allowed to install a single copy of CR on several machines as long as only the "Named User" is using the product in all places.  This was confirmed by the product team in an Email to me several years ago.  However this week another user said that the CR 2011 license is restricted to a "single install" rather than a Named User, and I found that surprising.  I want to read the current license for CR 2011 to see for myself if this has been changed.  Can you send me a copy?

Here is the response that I received:

This is the link to download the Clickwrap Agreement for Business Objects software, Crystal Reports 2011 included.

 In addition, here is the Software Use Rights document.

Your definition of Named User Licensing of Crystal Reports is consistent with the EULA which you should be able to find in a standard Crystal Reports 2011 installation (C:/Program Files (x86)/SAP BusinessObjects/Crystal Reports 2011/license/en/). 

[emphasis is mine]

So it appears that despite some wording changes, it is still fine for an individual to install CR designer on several PCs if he is the only user.

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 Crystal Reports to read a MS Windows file folder

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  "Create a new connection" node. Look for the item called "File System Data" and select it.  It will ask you to select a starting directory (folder).  Then you can decide to include all files or filter to specific values. You can also decide to include some or all subdirectories (subfolders). From there the folder becomes a table that you can use like you would any other table.

For some real fun you can use the file name in the 'hyperlink' property, which allows you to open any file directly from within CR.   I also use this to analyze my Customer project folders, looking for the largest folders that have been dormant for over a year.  That lets me move the largest and oldest files to archive folders and trim down the size of my weekly backups.  If you have a creative use for this feature please drop me a line.

Display strings and null values

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 to convert the nulls to default values inside the formula so that all nulls would be treated as an empty string [""]. Then this format could be quickly transferred to all the other fields using the format painter.  I thought it was a great plan.

However, there was a snag. A field with a null value won't show a Display String, no matter what you do. To me this seems like a bug or at least an inconsistency in the Display String logic. To be sure I put in a hard coded Display String of 'X' on a field.  All values in the column printed an "X" except for the null value records.  None of the 'convert null' settings had any effect.

On a related note, I also tested the functionality of the CurrentFieldValue function in the Hyperlink property formula. I found that when a field is mentioned in that formula it can be converted to null using the "Convert Null to Default" settings.  So a hyperlink can be generated even when nothing is printing in the field.  But if you try to use the CurrentFieldValue function, like my example above, the formula will not be able to convert the null value.

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. 

Using Visual Cut to turn Email messages into useable data

Millet software has added an interesting feature to Visual Cut. Visual Cut has always had the ability to send out Emails from a Crystal Report.  Now it can collect incoming Emails from an Email server and use them to build a database table from the incoming messages. These tables can be created in MS Access, Oracle or SQL Server and can be used to update existing data or to create new reports.  The table structure has 20+ fields which means plenty of detail for analysis.  Here are two examples of how this could be used:

1) Set up several "MailTo" links on a web page or a similar document.  Design each one so that when clicked, a preset Email message is sent to a specific account. Visual Cut can then retrieve all those messages and build a table out of the clicks.  This could be used to allow users to update the database or to request a report.

2) Build a "MailTo" into a message or attachment sent out by Visual Cut.  When the recipient click those "MailTo" links, a preset Email is sent to the specified accounts.  You can use Visual Cut to collect those responses and build them into a table.   This could be used to get feedback or track approvals.

Once the table is built, Visual Cut can read them to update an existing database based on the results, or you can read these tables directly to generate summary information.   See this link for a more detailed explanation and several other examples.

DataLink Viewer's new 'click' interaction

I have been working with Ido Millet of Millet software to enhance one of the features in the DataLink Viewer.  One of my favorite DLV features is that it allows you to change the value of a formula at runtime with a click.  This allows the user to change a group, a sort, or do "in place drill down" where you can expand and collapse the details of a group.  But I needed to tweak this so that I could change the value in a target formula based on the name of the clicked formula. The clicked formula name becomes an input available in the calculation of the target formula.

Here is how I used it. My customers report was grouped by category, and each group footer had a row of 31 subtotals.  These subtotals represented each day of the month.  It looked a bit like a cross-tab.  The problem was that a drill-down on any of the 31 values in the row would expose all the details for the category. What we wanted was to allow the user to click on any of the 31 subtotals and see the details for just that day within the category.  I have done this before using 31 on-demand subreports, but the size of the report made this much too slow. With the new DLV feature I can change the target formula based on the name of the clicked formula. The target formula is then used to control which drill down rows are visible or suppressed.  I can now get 31 different drill-downs from the same group footer.

While this was perfect for my needs, Ido took the idea one step further and allowed the click to generate a printable HTML ToolTip. This is a bit different than what I described above but I can see it having several uses.  This is easier to understand if you see it so Ido has posted a video demo. 

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, Canada and now 'down under' (see below) that can 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. 

My class now available in Australia and New Zealand

I recently had an experienced instructor in Australia teach a Crystal Reports class using my materials. He showed me his student feedback forms and they were excellent.  It is obvious he knows the subject and knows how to present it effectively. So if you are based in Australia or New Zealand and want an experienced instructor to teach your on-site classes, let me know.

Report processing flow chart dropped from the CR help file?

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. If you are unfamiliar with this diagram, it is worth reviewing.  

Gems from the archives:
Printing check boxes and other symbols from formulas (April 2002)
The effect of dashes on the VAL() function (March 2002)

Contact Information: