Contents for January 2012:  
** Server based deployment options compared (2012)
** Another Free Viewer for Crystal Reports
** My library of Crystal Reports materials
** A total of a formula instead of a formula of totals
** Controlling the color of many objects from one formula
** Let me create your Crystal Reports
** Case sensitivity in Crystal Reports
** Find it EZ adds support for Dashboards and SQL
** Open enrollment Crystal classes in Frederick, MD
** Strange printer behavior at runtime
** Dynamic parameter limit on "list of values"

Other recent blog articles:
Running v8.5 reports in later versions of CR
Five intangible metrics
 

 Server based deployment options compared (2012)

There are many ways to deploy Crystal Reports to users. I normally lean toward the simpler and less expensive options, like locally installed viewers, or scheduled delivery of PDF output.  But there are environments where a server based option is necessary. The 'official' options from SAP are Crystal (Reports) Server and BO Enterprise. You can read about these on the SAP website.  But there are other, less expensive products out there that many users never see. These are third party products that allow you to centrally manage your report deployment from your server.

There is a page on my blog that lists and compares these products, and I update it every January.  This year the list includes:

Ripplestone – a traditional Web portal
Report Runner Web Portal – a traditional Web portal
RVweb – a traditional Web portal
rePORTAL – a traditional Web portal
RV for Windows Pro – a server-based viewer
ReCrystallize Pro – a Web launch page generator.
Report Launch – a bridge between BO server products and server based applications.

The features vary and they are aimed at different types of users.  The blog page mentioned above contains a brief rundown on what each product does and provides links to all of the product web sites.  I have also posted a feature matrix (PDF) that shows some of the specifics for comparison, including prices.  If you have any feedback to share on these tools I would be happy to hear form you.

Another Free Viewer for Crystal Reports

Jeff-Net has released a free edition of Report Runner viewer.   It is completely free, can be installed as many times as you like and doesn't have any ads.  It is only missing 2 features that are included in their paid edition:

1) You can't launch reports from the command line (batch files).
2) You can't customize the interface

But otherwise it can do everything a report viewer should do (preview, print, export, refresh, etc).  To get your license you have to provide a company based Email address (not Hotmail/Gmail/etc.).  That's it.

If you want to compare the features of Report Runner with the other free (and paid) viewers you can read my review of Crystal Reports Viewers, which I update every September. 
 

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.

A total of a formula instead of a formula of totals

Imagine a report that has several columns of numbers that need to be added together - like Price, Tax and Shipping. At the end of a customer group you would have three subtotals. You might decide to write a formula that adds those three subtotals together to get the combined total for the customer. It would work fine but there is usually a more efficient way to do this. Instead of writing a formula that combines the three subtotals, I would write a formula that combines the three fields at the detail level. It would look like this:

   {order.ExtPrice} + {order.tax} + {order.shipping}

Then I would do a subtotal of this formula for the customer group. The subtotal of this formula returns the same value as the formula that combines the three subtotals.  So why is this more efficient?  Because if you have other groups, or if you need a grand total, all you have to do is check "add to all group levels" when creating the subtotal (summary field) of the formula.  If your version of CR doesn't have this check mark you can copy and paste the subtotal field to all the other footer sections.  The point being that this one formula can support all group levels as well as the grand totals.  And if you ever have to change the original formula you only have to change it in one place.

Using the first method you would have to write a separate formula for each group level and another formula for the grand totals.  And if the formula ever changes you would need to modify every one of the formulas.

In my mind I picture it this way:  Instead of going "down" the subtotals first and then "across" in a combined formula, you first go across to combine the details, and then go down to summarize that formula.

If you want to learn more about the 4 different types of totals that you can do in CR, and when to use each type, you should get "The Expert's Guide to Totals in Crystal Reports".

Controlling the color of many objects from one formula

I recently helped a customer develop a GANTT style chart in Crystal Reports.  The challenge was that Crystal's normal Gantt chart doesn't allow multiple bar segments on the same row.  So I was showing them how to create a GANTT chart using formulas and specially formatted summary fields. This method requires formatting dozens of small fields with the same color condition.

I selected an initial color and was trying to think of the most efficient way to change the color defined in all the condition formulas. Before I had an answer the customer showed me a method I had not seen before.  He created a single formula that returned the name of one of the CR colors, and then referenced that formula wherever we needed the color choice.  By changing that one formula value, the color would change in dozens of other objects.

Now I have written several articles about the use of 'feeder' formulas like this and this idea had even crossed my mind. But for some reason I was convinced that the only formulas that could return a color value as a result were formatting formulas. Obviously, I had never actually tried it. So when I saw that you could return this type of value in a regular formula field I wondered what would happen if I placed this formula on the report. I created a formula field that contained just the color function CRAqua (not in quotes), then I placed it on the preview page of the report.  What I saw was a large number.  And when I put that number into a font color condition, the font turned Aqua, the same as if I had used the name CRAqua.

So I learned two things.
1) All of the formatting constants that we use are really just aliases for numbers, and you can use the numbers instead.

2) You can assign a formatting constant in one formula and then use that formula to control value in formatting formulas.

It is always a good day when I learn something useful from a client or a student, so thanks to Suresh Gursahaney of MicroAutomation in Centerville, VA .

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.

Case sensitivity in Crystal Reports

I always mention case sensitivity in my Crystal Reports classes. My normal comment is that CR formula comparisons are case sensitive with the exception of the selection formula, which is usually NOT case sensitive. So I was surprised last week in my Advanced class when our formulas ignored all case differences. I thought this might be a recent change so I went home and tested reports back to v8.5. None of the comparisons were case sensitive.

So I did a quick Google search and found a 10 year old forum thread discussing case sensitivity. One short comment in that thread explained the difference. And guess who wrote that comment and promptly forgot about it (Doh!).

So the answer is that there is a setting in File -> Report Options that tells CR if your SQL statements need to be case sensitive.  That setting also controls the case sensitivity of your Crystal formula comparisons.  This setting is saved with each report so you can have some reports that are case sensitive and others that are not. You can set the global default for this setting in File -> Options (Database Tab).  The File Options setting will set the default value for all NEW reports, but will not change the default value for existing reports.  If you need to change the value for existing reports you need to go to File -> Report Options for each report.  

Find it EZ adds support for Dashboards and SQL

It is always a good sign when vendors respond quickly to user requests.  Last month the developers of Find it EZ received a request from a potential customer.  He needed to search for table and field names within his SQL commands and SQL expressions.  The next day this user was using a new version of Find it EZ, including this new feature.  The user was surprised that he didn't even need to fill in the report parameters before the SQL was searched – something required by his old software.

This support for embedded SQL searches is now included in the current release of Find it EZ.  It can also search Crystal Dashboard(Xcelsius) files and even their embedded Excel models.   See this article for more info on Find it EZ.

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.

Strange printer behavior at runtime

One of my customer sent me two screen shots. One showed a normal preview on his development machine. The other showed a preview window on the runtime machine. The second had the group tree, but the report was nothing but a long blank vertical strip. He had been struggling with it for a month and could not figure out what was causing the strange behavior at runtime.

Now I have found that when the preview behavior changes on a different machine, it is usually due to a printer driver problem. I also know that CR will normally look for the same printer that was used at design time. If CR doesn't find the same printer name it will then use the default printer on the runtime machine.

The long narrow strip didn't look like a normal printer, so I wondered if the new workstation somehow was defaulting to a printer with a very narrow printable area set by default.  So I Emailed my customer if the deployment workstation had a label printer set up as the default printer. Here is part of the response:

    "All I have to say is WOW! I changed the default printer
     and problem instantly solved. It has haunted me for a month
     and you resolved it in one simple email."

Granted, not all problems are this easy to solve, but if you have a strange behavior with one of your reports it might be worthwhile to let me take a look. 

 Dynamic parameter limit on "list of values"

Crystal limits how many values you have to choose from when you use a dynamic parameter.  The default limit is 1,000.  To show more than 1000 values you have to edit the registry on the PC. Here are the steps:

1) Add a new registry entry under:
HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.5\Crystal Reports\DatabaseOptions\

2) Add a new key at that level called: \LOV

3) Add a string called MaxRowsetRecords

4) Set the value to whatever limit you want, or enter a zero to mean unlimited.

Notes:
**Always make a backup before making registry changes
**Loading large lists this way can make the parameter run for a few minutes before the user can select a value.
**Any dynamic list that has more than 200 values will activate the parameter's 'batch interface" discussed in a previous post.

(And thanks to Duane Fenner, an Account Manager and CR developer at LeaseTeam, Inc. for suggesting this post.)   

 Other recent blog articles:

Running v8.5 reports in later versions of CR
Five intangible metrics

Contact Information: