phone: (540)338-0194
email: ken@kenhamady.com


Individual Instruction by Ken Hamady

The Crystal Reports Underground News Volume 2013.03

an independent source for Crystal Reports Information
by Ken Hamady, MS 

Contents for March 2013:
** Comparison of desktop-based schedulers (2013 update)
** Discount on purchases of Find it EZ
** My library of Crystal Reports materials
** Using metric rulers in design or preview
** Font sizes inside charts
** Let me create your Crystal Reports
** CR versions over time 2013
** Using auto-complete in Crystal Reports formulas
** Open enrollment Crystal classes in Frederick, MD
** SQL expression bug in v9, v10, v11(XI)
** Using SQL commands in ABRA HR


Comparison of desktop-based schedulers (2013 update)

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 every March I go through the list and publish a feature comparison on my blog.

I discovered and added one new tool this year, bringing the pool of reviewed tools up to ten. 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 terms 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 that compares server based scheduling tools. If you think one person can manage all of your scheduling you are probably fine with one of the 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.


Discount on purchases of Find it EZ

If you have been considering purchasing Find it EZ then you might want to do before the end of March. The developers of Find it EZ are offering my readers a 25% discount on purchases through 3/31/2013. Use this link to get the discount.  And they are planning a price increase in mid-March so if you use the link now there is an extra savings.

If you aren't familiar with Find it EZ, it is a source code scanning tool that I have written about as part of my comparison of RPT management utilities.  It can search your entire application, including your RPT files, for the use of specific tables, fields and text.

The promotion this month is to highlight some recent updates. Find it EZ is now:

    certified to run on Windows 8 and Windows Server 2012
    certified for Citrix Server with floating license concurrent distributed user support
    certified for SQL Server 2012 and latest version of SSRS
    able to search your database by owner or schema
    able to scan within MS Query files

If you have tried it and have any feedback, 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.


Using metric rulers in design or preview

I was working with one of my customers on reports they needed to deploy in Japan. The customer asked me how to convert the rulers in design and preview to use metric units. I had never done this before, but I knew there was a setting that mentioned this in the Page Setup window (File>Page Setup) so we went there first. That changed the page sizes to metric units but didn't have any effect on the the design or preview rulers. Next we went to the "View" menu and tried changing the Product Locale to Germany, since that country uses metric units. This changed the ruler to metric, but it also put the entire menu and all the GUI labels into German. Right below this menu option is a similar menu item called "Preferred Viewing Locale". It wasn't really clear how this was different but we tried that next. That changed the rulers to metric without changing the language of the product.

On my own installation of CR I only have one option in Product Locale (English), but I have dozens of options in the "Preferred Viewing Locale".  It appears that these are installed automatically, while the full Product Locales are only installed if you select them at the time of install.   My customer had the full list in both places.


Font sizes inside charts

If you have worked with charts you know that the font sizes can be a bit tricky. With most text objects and fields on a report, the font size is independent of the object size. You can make an object bigger but the font size doesn't change.  But with charts, all font sizes are relative. Make a chart bigger and you automatically get bigger fonts for all the text in the chart.

This poses a challenge for getting labels or legend entries in your chart to be the same size as the text outside the chart. You can't simply set them to the same number because the chart labels are relative. Setting it to size 10 may not result in size 10 font, so you have to play around a bit to get the sizes right. In some cases I have had to type the my own labels independent of the chart and line them up manually.

There is one other challenge that usually comes into play when you want very small chart fonts. With most text objects you can override the font size selector by typing in a smaller number that doesn't exist in the drop down list. However in most places for charts you are limited to the drop down values. So for instance if you right click on a label below a bar and select "format axis label", you will have to select a number from the size selector drop-down.  You can't type a number in here.

But, if you need to override that list and type in an unlisted font size you can use an alternative method:

  • Go into the Chart Expert
  • Go to the TEXT tab
  • Select "Group Labels" from the list at the bottom
  • Click the "Font" button

Now you get a different font size interface that allows you to enter an unlisted font size (like 2). Thanks to Adam Butt of APB Reports in Trondheim, Norway for this last tip. Also note that if you are trying to change the size of the legend labels, the item for this in the TEXT tab is called "legend title" which might throw some people off. And, it only appears if you have activated a legend.


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.


CR versions over time 2013

Last year about this time I posted a chart that showed my newsletter subscribers and what versions of CR they were using when they signed up. I have decided to make this an annual post so you can get a feeling for which versions are being used and how quickly the new versions are catching on.  This years post even includes numbers for 2013. With only 2 months of data for this year the 2013 ratios might be a bit skewed.

What I notice is that CR 2011 (v14)  is starting to take over while CR XI (v11) is now starting to recede.  I think the slight jump in the oldest versions in 2003 is a statistical fluke that will diminish as more data for the year accumulates. 


Using auto-complete in Crystal Reports formulas

When you do something one way for a long time, it can be hard to get out of the rut. So even though the Crystal formula editor has a pretty good 'auto-complete' feature, I still don't take full advantage of it. I still tend to scroll through the list of fields. But recently I have started to take advantage of auto-complete to speed things up.

The auto-complete in CR has two components. One is for fields which was introduced in CR v12(2008). The other is for reserved words like functions and was introduced in CR v10.

Fields:
If you want to enter a field you simply type a curly bracket into the formula editor. A list of fields will pop up. At that point it will include a list of all the field objects that you have created (ie formulas, parameters, running totals, etc) and it will include a list of the data tables used in the report. If you type one or more characters after the bracket the list will get shorter, including only the fields and tables that start with those characters.

Once the list is short enough you can scroll down and highlight the field or table you need. You select the field or table by hitting either enter or tab. If you are on a field when you hit enter then that field will be inserted into the formula. If you are on a table then that table name will be inserted into the formula followed by a period. Another drop down will now appear so that you can repeat the process to select a field. You can type a few characters to shorten the list or select a field by highlighting it and hitting enter or tab.

Reserved Words:
If you want to enter a reserved word like WhilePrintingRecords or DrillDownGroupLevel you can type the first character or two of that word and then click Ctrl-Spacebar. This will pull up a list of all the reserved words that match those starting characters. You can then type characters until you see the word and select with a TAB or ENTER. If there is only one word that matches what you have typed it will be entered automatically.

But there is one small annoyance I have found. It happens when I am deleting part of a formula and accidentally delete the initial bracket of a field name. Typing that bracket automatically invokes the auto-complete which also adds the right bracket, and opens the drop down list of fields. I have to delete the extra bracket and I can't use the up/down arrows till I leave that field.


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 May 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.


SQL expression bug in v9, v10, v11(XI)

So here is an odd bug I found in Crystal versions 9, 10 and 11(XI). It affects SQL Expressions that contain a correlated sub-query. This example expression works in both CRv8.5 and CRv12 (2008) but none of the versions in between:

(SELECT Min (CAL.ONDATE)
FROM GM6.dbo.CAL CAL
WHERE CAL.ACCOUNTNO = Contact1.AccountNo)

The expression is designed to show the first appointment in the Cal table for each contact in the report. Using a SQL Expression avoids duplication I would normally get by adding the Cal table to the report directly. Run this expression in CR versions 9, 10 or 11 and you get this error:

    "Error in compiling SQL Expression.
    The multi-part identifier Contact1.AccountNo
    could not be bound.
"

At first I thought this error was coming from SQL. As a test I took a v12 report that had this expression and opened it in v10. The report ran fine. But if I then try to edit the SQL expression or even check it, I get the error above. At that point the report will no longer run in v10 no matter what I do. My only option is to close it without saving changes and reopen it – then it works fine again. This tells me that the SQL is fine because it isn't the DB that rejects it. The error only comes from the CR SQL syntax validation. The validation must not happen at refresh because if it did the report would not run when first opened. But once the validation has failed the report can't be refreshed, so the SQL expression must be marked as bad until it can pass the validation.

A few more experiments showed that this error is somehow related to using aliases. If I remove all alias references the problem goes away. This expression works in all CR versions:

( SELECT Min ( ONDATE )
FROM GM6.dbo.CAL
WHERE ACCOUNTNO = Contact1.AccountNo )

It is curious that the alias of the CAL table causes the field from the Contact1 table to error out.

But here is the really odd part. Another way to fix it is to put a SQL comment line at the beginning of the expression. If the comment includes the name of the field mentioned in the error message, then that somehow makes it work, even with aliases. So the following expression also works in all versions:

(
/* some words accountno and some more words */
SELECT Min ( CAL.ONDATE )
FROM GM6.dbo.CAL CAL
WHERE CAL.ACCOUNTNO = Contact1.AccountNo )

The only word necessary in the comment is the word 'accountno'. If I delete that word or misspell it, I get the original error. Clearly the comment doesn't make a difference to the database, but it must have some effect on the CR syntax validation. This last behavior was discovered by Bob Antaki, a developer with the Bay Area News Group in California.

So if you have struggled with adding SQL expressions like these to your reports, hopefully this will help. If you want to learn more about using SQL in Crystal Reports you can read my Expert's Guide to SQL Expressions, Options and Commands, or you can schedule a session with me to go over some of these concepts.


Using SQL commands in ABRA HR

I don't know how many of you out there using Crystal with the ABRA HR/Payroll software, but I just had a real challenging episode with it and ended up learning quite a bit (the hard way).

My task was to speed up a report that took 3 hours to run. The problem was obvious – a recurring subreport that ran 900 times on a typical payroll. A subreport was used because linking the table would have required adding a filter to an outer join, something that the  linking window can't do.  When I found out commands didn't work in the ABRA OLEDB connection it started me on a long process for finding a solution.  Plan B turned to plan C and then plans D through F before we found our solution.  Those interested can follow the full saga.


Contact Information:


Ken Hamady, MS
525K East Market St.  
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com

Copyright 2013 by Ken Hamady
All rights reserved - Republishing this material requires written permission