Contents for March 2012:
** Desktop schedulers comparison for 2012
** Vendor responsiveness (good and bad).
** My library of Crystal Reports materials
** Printing QR codes within your Crystal Reports
** CR versions in use by year
** Let me create your Crystal Reports
** Creating the text-shadow effect with Crystal Reports fonts
** Creating a hanging indent
** Open enrollment Crystal classes in Frederick, MD
** Stripping numbers off the end of a string
** Don't start your formula names with spaces.
** Making SQL commands more efficient

Other recent blog articles:
    More on controlling colors from formulas
    Confusion over 32-bit and 64-bit ODBC
    Visual Cut now exports to XLSX format
    Problems with the new PeachTree upgrade

Desktop schedulers comparison for 2012

How would you like your reports to be automatically run, exported to a PDF and delivered to your Email InBox every Monday morning at 6am? The Crystal Reports designer doesn't provide a way to do this (unless you upgrade to CR Server or BO Enterprise).  But if you look at third party products like those on my LINKS page you will find several reasonably priced or free tools that do this. Some do even more.  So each year I compare their fetaures, and update a page on my blog.

I discovered and added one new tool this year, bringing the pool of reviewed tools up to nine. The article provides a brief introduction to each product and describes what sets that product apart.  Then there is a detailed feature matrix that shows the key specifics for comparison, including prices and the install base. To clarify the matrix I have written a feature glossary to  explain what each feature means.  Finally there are links to the vendor websites so that you can get more information on each product.

In May I will be updating a separate article covering server based scheduling tools.  If you think one person can manage all the scheduling you are probably fine with one of these desktop tools, regardless of the number of people receiving the scheduled output.  But if you plan to have multiple people scheduling reports then you may want to consider a server based tool.

Vendor responsiveness (good and bad)

I received this message in February:

    "Ken, thank you for this comparison. I wish I had had it before I selected my previous Crystal Viewer…. I purchased quite a few licenses of a product and intended to schedule reports on it. The scheduling aspect never worked and no matter how many messages and emails I sent to the support people, no one EVER called me back or seemed to care that I was having problems. They literally ignored me."

This vendor tells me that they responded to the Emails they received, and at this point the customer has moved on. However, if this customer had contacted me at the time they would have received a response. A vendor on my LINKS page is unlikely to ignore me. Keep that in mind next time you have a problem with any product listed on my site. I will do what I can to get a reasonable response.

Fortunately, most of the comments that I get are positive. If you have a particularly good or bad experience with one of the listed vendors, please let me know.

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.

Printing QR codes within your Crystal Reports

I have written before about using Bar Codes in Crystal Reports, but recently two different customers have asked me about including QR codes on their reports.  For those of you who have not seen them, QR codes are the square scan codes that have small boxes in each  corner.  The advantage of QR codes over traditional bar codes is that they have extra error checking built in, which means that even a slightly blurry scan, such as one done by a smartphone or tablet camera, can still be decoded and used.

There are several ways that you can incorporate QR codes into Reports:

1) If you have a single QR Code, or a small number of them, then you can use a free web based QR Code Generator to generate the image.  You can then insert the image into the report.  If you have several you could store the image files in a network folder or on a website.  To choose the correct one you could use the picture's "graphic location" property (available in versions 11 through 14) to specify the path to the correct code for each instance of the image.

It is not hard to find QR Code generators on the web.  I like the one at  http://www.qrstuff.com/ which I used for some test images.

2) If you need to generate the codes on the fly based on data in the report, and if you are doing this within a  .NET application, then there are several sites that sell bar code generators that you can use.  These are dlls that allow you to incorporate bar codes, including QR codes, into your reports.  But these dlls need to be deployed as part of a .NET application:

http://www.keepdynamic.com/barcode-crystal-reports/

http://www.barcodelib.com/net_barcode/barcode_symbologies/qrcode.html

http://www.idautomation.com/visual_basic/?26947

http://www.onbarcode.com/

3) But most of us are using stand alone Crystal Reports.  If you need to generate QR codes on the fly from your report data you will need two things:  a dll that can encode the data and a special font to convert that data into the QR Code.  I found two vendors that sell a combination like this that.  They are the QR Code Font and Encoder by IDAutomation and QR Code by Barcodesoft.  These both cost about $200 for one user on one PC and they both allow you to generate your own QR Codes using data within your report.   I tried the one from IDAutomation yesterday and I had no trouble getting it to work in my report.

Both have a free trial which is what I used.  These work just like the real thing except that the demo codes start with the word DEMO (IDAutomation) or have a watermark (Barcodesoft).  So when I sacanned the QR code for my web site it read out as:

DEMO http://www.kenhamady.com

The demos let you get close enough to know that they work, but not enough that you can go to production without buying the full product.  IDAutomation also offers a 30-day return policy.  They actually encourage you to purchase the full license for testing, and then return it if you don't want it.  Both sites provide instructions specific to Crystal Reports.

CR versions in use by year

One of my customers recently asked me how quickly I thought people were going to the newer versions of CR.  I didn't really have any basis to answer him, so I was pleased last week when one of my LinkedIn groups started a survey, asking people to select their primary version of CR.  After nearly 100 responses I was surprised to see the persistence of version 11 (XI) which is shows up as the primary version in use.  This is a full 7 years after XI was released.

Only then did I remember that I ask my newsletter subscribers to list their version when they sign up.  So I did my own analysis based on the 1200 users that answered this question in the past 5 years.  See the chart here.  I used the same 5 categories used in the LinkedIn survey.  You would think that since I am only counting new subscribers each year, that the data would skew slightly toward newer versions.  But version XI is still a clear winner, even when looking at the new subscribers so far this year.

Also note that as version 14 starts to appear, its increase seems to come at the expense of version 12 more than version 11(XI).  The only explanation I have for the persistence of version XI came from a comment on my blog.  They mentioned that XI is the last version that supports application integration with COM objects through the RDC.  After that everything is .NET. 

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.

Creating the text-shadow effect with Crystal Reports fonts

Here is a clever tip I just received from a blog reader who is also a Crystal Reports developer. He wanted to create a title with something that looks like the "text-shadow" property.  He found that using an image didn't print as clearly as a true font so he came up with a clever way to create the shadow.  Here are the steps:

1) Put the title text into a large bold font.
2) Copy the title text object and paste a duplicate directly on top of the original.
3) Right-click on the copy and select "Move – To Back".
4) Changed the font color of the copy to silver.
5) Nudge the copy slightly down and to the right.

There you have a clean shadowed font.  And thanks to Adam Butt of APB Reports in Trondheim, Norway.

Creating a hanging indent

Sometimes there are text blocks on your reports that contain more then one line of text (notes, memo fields, comments, etc.).  When you format any multi-line text field, you have a tab of options called 'paragraph' formatting. The first 3 settings on this tab allow you to determine how far the text block should be indented. Your indent options are "Left", "Right" and "First Line". This can be misleading because "Left" and "Right" are calculated from the left and right edge of the object, while "First Line" is calculated from the indent set in the "Left" position.

Take this example: You want the first line of text to be indented 1.0 from left while all other lines in the block are indented .5 from left. You might try putting 1.0 in "First Line" and .5 in Left indent.  What you would get is an indent of 1.5 on the first line.  This is because it adds the 1.0 in "First Line" to the .5 in "Left". If instead you put .5 in both places, then the first line will indent .5 + .5 or 1.0 inches.

And what if you want a 'hanging indent' where the first line is not indented but the rest of the paragraph is indented .5? For this you would put in .5 for the "Left" indent and -.5 (a negative number) for the "First Line". The negative number added to the positive nets to zero so that the first line has no indentation, while the rest does.

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. 

Stripping numbers off the end of a string

Recently a customer needed to group records using part of a code field. The code field had alpha characters followed by a series of numbers. They wanted to to strip off any numbers on the end and group using only the initial string portion of the value. In the past I have done things like this using a FOR loop to check each character. This time another idea popped into my head. If you reversed the string so it started with the numbers you could use the VAL() function. This would convert the numeric portion to a number and ignore the string characters that followed. You could then check the length of that number to know how many characters to take off the end of the value. To get the length of the number you would have to turn it back into a string and then use the Length function. So the calculation would nest four functions like this:

Length( Totext( Val( strReverse( {table.field} ) ) ,0, "" ) )

The only glitch was if the string ended with a zero, like "XXX1230". In those cases the VAL() would ignore the zero (true numbers don't start with a zero) and then my length would be off by one. To fix that I appended a "1" to the end of each string before I ran it through the calculation. That way the value would never end with a zero.

Last, I created a local variables so that a user would only have to put their field name in on place. The end result looks like this:

//add a one to deal with a final zero
Local StringVar x := {Customer.Postal Code} & '1';
//calc the # of digits to remove
Local NumberVar y := Length(Totext(Val(strReverse(x)),0,''));
//if it is all number characters return a blank.
if y = length(x) then ""
// otherwise reduce the length by y positions.
else x [ 1 to length(x)- y ]

Don't start your formula names with spaces

Formula names can contain any characters you want, including numbers, symbols and spaces. But I just found a reason not to use a space as the first character of a formula name.

A customer recently tried to modify a formula and was prompted to log into the repository. She sent me a screen shot of the messages, which said:

enter the name of your system (Central Management Server)

then:   Repository services are not available

I figured that there was probably an old custom function in the formula.  So I asked to see the report and went through all the formulas.  There were no repository functions in any of the formulas.  And yet, when I right clicked on the first formula and tried to edit it – up popped the same message.  I was stumped until I noticed that the first formula name started with a space. So I switched to one of my own reports and renamed one formula so that it started with a space. When I right clicked on that formula and tried to edit it, voila! I got the same messages. I ran the same test it in versions 10, 11 and 12 and got the same result.

Apparently there is a bug in the field tree logic. When it takes you into the formula editor it should highlight the field that you were on in the field explorer.  But if the field name starts with a space it somehow tries to go to the next higher category in the list, which is the repository functions. This triggers the message.

If you get any strange behaviors in your reports that you want to share, please drop me a line.

Making SQL commands more efficient

In most cases I find it more efficient to let Crystal generate the SQL for reports.  This makes the reports simpler for most CR users to create.  It is also easier to add additional fields, and the Visual Linking window makes the query structure easier to understand. Finally, in most cases, the performance is essentially the same since CR can generate workable SQL.

But there are plenty of cases where the only practical solution is to base the report on a SQL Command, instead of on linked tables. I use SQL commands when I need to do a UNION of two or more queries, or when I need to apply a filter to the 'outer' table of an outer join. Recently I find myself using SQL more, so I have started to pick up tricks that make SQL queries work faster.

Last week I was working on a query that combined several fairly large tables.   The query was taking several minutes to return data and we were trying to see if there was anything we could do to speed things up. I noticed that one of the key criteria fields was coming from the last table added in the FROM section of the query.  I decided to do an experiment and moved that table so that it was added first in the FROM.  Then I moved the criteria for that table from the WHERE clause to the FROM clause, making it part of the "ON" rules for that table.  My theory was that as each table was added it would multiply the number of records in the results.  So if the primary filtering is done on the first few tables in the query it will reduce the number of records that get multiplied as the later tables are added.  Sure enough, we saw a dramatic improvement in the performance.  I decided to move all of the rules from the WHERE clause to the FROM clause.  We ended up with a much faster report.

Some Google searches showed me that the idea of putting filters in the FROM rather than the WHERE seems to be common knowledge among DBAs.  I was surprised that I had never heard this before.  Some databases try to optimize the query automatically, but that certainly wasn't happening in this situation.

Today I got a very similar result in a report that used tables directly.  This report was designed to take one order, look up the inventory items on that order, and then generate a number of labels for that item based on the order quantity.  We used an "inflation table" in the report containing 5000 rows, which would force the label to duplicate as many times as we needed.  But when we ran the report it never completed the query.

So I looked at the SQL and noticed something.  Even though there was a rule in the WHERE clause to select one order, the Orders table was being added at the very end of the FROM clause, after the Inflation table.  That meant that before the database could apply the filter to select the one desired order, it had to assemble the entire Inventory table, then repeat every inventory item  5000 times.  Only THEN could it start looking for the desired order.  With thousands of inventory items, each multiplied by 5000 records in the Inflation table, there were millions of unnecessary records being generated.  I reversed the  joins in the CR linking window to put the Orders table first, then the Inventory table and then the Inflation table.  My theory, again, was that the database would first narrow the search to one desired order, then look up just the inventory items on that order.  Only THEN  would it need to multiply the result by 5000.  The modified report finished in just a few seconds.  I could have probably sped it up further by converting the report to use a command and moving the WHERE criteria to the FROM, but the performance already satisfied the customer.

So if your reports are taking longer than you think they should, you might try some of these ideas.   If you need help I would be happy to set up a short consult to take a look at your reports.

Other recent blog articles:
    More on controlling colors from formulas
    Confusion over 32-bit and 64-bit ODBC
    Visual Cut now exports to XLSX format
    Problems with the new PeachTree upgrade

Contact Information: