Contents for November 2011:
** Crystal Reports formula function libraries (annual update)
** CR 2008 service pack 4
** My library of Crystal Reports materials
** Find it EZ offers a 30 % discount
** ReCrystallize Pro v14 is released
** Let me create your Crystal Reports
** Grouping transactions by season
** Why distinct count subtotals don't add up to the distinct count grand totals
** Mystery line in PDFs
** Open enrollment Crystal classes in Frederick, MD
** Resetting the toolbars and the 'explorer' panels
** Null values and the selection formula

Other recent blog articles:
    Do you see CR as a declining technology?
    Update to CUT Light distance calculation feature

Crystal Reports formula function libraries (annual update)

It has been one year since I did my first comparison of formula function libraries, so it is time for an update.  User Function Libraries (or UFLs) are DLL files that add new formula functions to Crystal Reports.  With these new functions your formulas can do some pretty amazing things like:

1) Carry values from today's report to tomorrow's, or from one report to another.
2) Append lines to an external text file.
3) Automatically copy a value to the clipboard.
4) Check the user name of the user running the report.
5) See if a file or folder exists – either on your network or on the Internet.
6) Rename/copy/delete a file on your hard drive or network drive.
7) Launch an application or run a batch file.
8) Execute a SQL statement (Select/Insert/Delete).
9) Send an Email using information in the report.
10) Create a table of contents or an index for your report.
11) Calculate distances between zip codes or long./lat. coordinates.

If this sounds interesting you can read my complete comparison including a list of all the functions provided by each DLL.   The four primary sources are:

Bjarke Viksoe (U2lwin32)
Millet Software (Cut Light)
Chelsea Tech (File Mgt, Text, Share and others)
CrystalKiwi (Export, Table of Contents)

 CR 2008 service pack 4

One of my readers wrote in to tell me that SAP released Service Pack 4.  He had CR 2008 set to "Check for Updates on Startup" but it  never triggered an update for SP4.  Even after he manually clicked on "Check for Updates" the message said that he had the most recent version.  But when he searched the SAP website he found it.  

I have been using SP2 because of two issues that I wrote about earlier.  But when I asked CR to check for updates I got the same message – that I had the most recent version.  So searched the download site and found SP4 in the "Downloads" area of the SAP web site.  I installed it and it didn't break my dynamic parameters, so that is good news.  The "Saved Data" issue was intermittent so I will be on the lookout for that or any new problems.

And thanks to Mark Edwards at DataReport Consulting for pointing this out.

 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.

 Find it EZ offers a 30 % discount

If you were considering trying out Find it EZ this might be a good month to do it. For the month of November, Find it EZ is offering my readers a 30% discount on their scanning tool. It is designed to find all uses of a particular table or field in any layer of your project. So it goes beyond scanning your RPT files and also scans your application source code, your SQL queries, documentation, config files and just about anything else. It even includes plugins that integrate automatically into Visual Studio and Eclipse.

When you scan your project for a proposed change Find it EZ will generate an "Impact Checklist" that breaks down all changes into categories like: 100 Java program files, 25 SQL Server stored procedures, 15 Crystal Reports and 5 MS Word document user guides that all reference that object. This not only tells you what needs to be changed, but the type of effort that will be needed to make the changes.

The regular price is $199 and you can use the link above or use the discount code of "kenhamady_nov2011″ to have the discount applied to your shopping cart.  And, if you do decide to try it out, please let me know what you think.

ReCrystallize Pro v14 is released

The folks at ReCrystallize have recently released version 14 of ReCrystallize Pro.  For those of you who are not familiar with ReCrystallize, it is a low cost way to put your reports on the web.  Walk through a wizard and ReCrystallize will generate ASP or ASP.NET web pages that are designed to run your reports.  You can read more about it in my comparison of server based deployment options.  With version 14 there are two significant changes.

1) It now supports the newest version of Crystal Reports (2011). This means you can use dynamic and cascading parameters. You can also change parameter values without changing web pages or in some cases without even refreshing the report.

2) It now supports ASP.Net which means it works better with the current browsers and current versions of Windows and IIS.  It also means that the user's browser doesn't need to download anything.

Note that they still support classic ASP and most older versions of CR.  See their web site for more details.

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.

Grouping transactions by season

Recently I was asked if there was a simple way to group records into Spring, Summer, Fall and Winter.  There are several ways but I think this is the simplest.  I convert the month and day into a number by multiplying the month by 100 and then adding the day number.  This gives every day of the year a sortable value from 101 to 1231. Then you can use that in a formula like this:

Local NumberVar x :=
Month({Orders.Order Date}) *100 + Day ({Orders.Order Date});
If x < 320 then "Winter" else
If x < 621 then "Spring" else
if x < 923 then "Summer" else
if x < 1221 then "Fall" else "Winter" 
 

 Why distinct count subtotals don't add up to the distinct count grand totals

I had two different customers in the same week ask me why their distinct count grand totals were not totaling correctly. They had distinct count totals at both the group level(subtotals) and at the report level (grand totals).  But when they added up all the subtotals the sum didn't match the grand total.  The truth is that usually these won't match.  This is because of the way distinct counts are designed to work.

A distinct count summary will count how many different values there are in a column.   So say my report groups patient visits by doctor.  I then want CR to tell me how many different patients each doctor saw in that period. I could use a distinct count at the doctor group level.  And if I also wanted to know how many different patients were seen across ALL doctors I could create another distinct count of the patients and put it in the report footer.  But it is very likely that this grand total is not going to match the sum of the doctor subtotals.  This is because any patient who saw more than one doctor in the period will show up in each doctor's group, but then will be counted only once in the grand total.

If you ever have a requirement where you need the grand total to be the sum of the subtotals you can purchase Expert Techniques volume 1 and read tip 25.

Mystery line in PDFs

Last night I had a customer with a strange problem.  Their report ran fine and looked great in preview and when printed.  But if it was exported to PDF it had a long line down the page.  This didn't happen if it was printed to a PDF driver so the only time it was visible was when using the CR export function.

I opened the PDF and there was a vertical line on every page, even on blank pages, starting near the top.   I opened the RPT and there was no visible object anywhere near the sections at the top where the object should be sitting.  I was getting ready to start deleting objects one at a time to see if it was tied to a specific object, but first I decided to check the "Report Explorer".  You can read my last post about the Report Explorer if you are not familiar with it.  It is a handy tool.

And in the Report Explorer I found a small box listed in the Report Header where no box was visible.  I clicked on the name in the Report Explorer and it highlighted the object on the layout, starting in the Report Header and ending in the Group Header.  But even now that I knew where it was, if I clicked off of the object it was completely invisible again.  Not even a trace of the box remained visible, which I thought strange.   So I checked the "size and position" of this box and found that it had been set to a width of 0.00.   Most size settings don't allow zero, but apparently width is an exception on certain objects.   I was able to set the width to zero on boxes, fields and text objects.  Fields and text objects are still slightly visible in design mode, even when they are set to a width of zero.   Boxes, however, are completely invisible when they have a width of zero.  The only time they show up is when you export to PDF.

So the next time you find your PDF exports have an extra vertical line or some other artifact that you can't explain, check the Report Explorer – or give me a call.

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.

Reseting the toolbars and the 'explorer' panels

On occasion I have worked with customers where the field explorer has become undocked and we have trouble getting it to dock again.  I also remember clicking toolbar buttons and accidentally dragging the toolbar out of position, then not being sure how to get things back the way they were.  So I was pleased to find a feature (tucked inside a little-used menu item) that will fix these issues.  It is currently available in versions 11 through 14 and I assume that it will be available in later versions as well.

To use this feature go into the VIEW menu and select TOOLBARS.  You will see a window that allows you to control which toolbars are visible. At the bottom is an option called "Reset all toolbars and explorers on the next restart".  Check this option and then close and re-open Crystal Reports.  All your toolbars will go back to their default positions and your field explorer (and other explorers) will re-dock in their original positions.

Now I would have been even happier if this also reset the entire Formula Workshop.   I have written before about the formula workshop disappearing, or the panels not docking correctly.  Unfortunately these features are not currently reset by this option.  So if  you have these problems in the formula workshop you will still have to dig into the registry to fix them.

Null values and the selection formula

Here is a trap in Crystal Reports when using the selection formula.  Say you have two fields, A and B. You want to include all records where either A or B is equal to X. Your selection formula would look like this:

{A} = "X" or {B} = "X"

But what happens if A is null on a record where B is equal to X? Null values cause CR formulas to stop working, so CR would stop processing that formula before it ever got around to looking at B. CR would skip that record. But not if the the formula was reversed:

{B} = "X" or {A} = "X"

This formula should logically return the same result as the first example but it doesn't.  The record I described above would show up just fine using this second formula, but not using the first. It doesn't make sense that flipping the formulas around should get you different results. It certainly wouldn't happen in a straight SQL Query.

Fortunately there is an easy fix. The simplest method is to go into the selection formula and look at the toolbar. There is a setting at the top that defaults to say "Exception for nulls". Change this to say "Default Value for Nulls" and that should make the formula work correctly either way.

If you don't have this setting because you have an older version of CR, or if for some other reason this doesn't work in your environment you can use this:

(( not IsNull ({A}) and {A} = "X" ) or {B} = "X" )

The outer pair of parentheses is to make sure that Crystal doesn't accidentally reverse the A and B rules. That might happen if you wrote this in the selection formula and then added another rule in the select expert. With the outer parentheses in place CR won't modify the rules.  (For more examples of my most popular formulas, please visit the FORMULAS page on my website.)

Other recent blog articles:
    Do you see CR as a declining technology?
    Update to CUT Light distance calculation feature

Contact Information: