The Crystal Reports Underground News

Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide

(For recent updates read my BLOG, The Crystal Reports Underground )

The Crystal Reports Underground News - Volume 2003.12 (December 2003)

an independent source for Crystal Reports Information by Ken Hamady

Contents for December, 2003:

** Using Bar Code Fonts in Crystal:
** A description of SQL Server Reporting Services
** "best class I've Taken ... very knowledgeable"
** Find a date based on Year and ISO Standard Date
** Doing a SELECT in a SQL Expression field
** My Library of Crystal Reports Materials:
   Expert's Guide to Formulas
   Expert Techniques Volumes I and II
   Quick Reference to Crystal Reports in Visual Basic
   Quick Reference to Crystal Reports in VB.NET
** Automatically Updating Repository Objects in Reports
** Reinstall fixes bug?
** A new Crystal Expert (in about 20 more years).
** Read back issues at http://www.kenhamady.com/news.html

Using Bar Code Fonts in Crystal Reports:

Bar Codes are everywhere - handling the most repetitious data entry. That familiar box of stripes on every consumer product is simply that product's ID number in a form that can be quickly read by a scanner. The UPC code you see at the store is the most common example. Every consumer product is given an ID number by the Uniform Code Council in Dayton, OH. The number is converted into a machine readable form so that they can be scanned very quickly. Recently I have noticed bar code scanning everywhere. Checking materials in and out of inventory; patients in some hospitals; even my local library uses a scanner when checking books in and out.

Crystal Reports can be used to print bar codes on your reports or on adhesive labels. However, you need two additional things to make this happen. First you need a Crystal Function (UFL) to convert the actual ID number into the equivalent Bar Code. This is usually done by writing a formula field and placing that formula onto the report. Second you need to install a Bar-Code font that can print the individual bars that make up the bar code.

Unfortunately Crystal Reports does not come with bar code fonts or functions. You get these from a third party. There are three primary commercial sources for bar code fonts that work with Crystal Reports: Azalea, Wasp, and ID Automation. Azalea is the most expensive because each font seems to be sold individually. The other two vendors sell sets of Fonts and font formats. Each vendor provides the appropriate formula functions for their fonts. Form more information on these vendors, see the LINKS page of my web site.

I have also added a link to some FREE font resources. However I was warned by Brant Anderson of ID Automation that he has seen many illegal counterfeit fonts, sometime his own, offered as freeware. ID Automation offers one commonly used font for free to certain small companies or non-profit organizations. You can contact them for more information, or look for the link to FREE information on the ID Automation web site.

A Description of SQL Server Reporting Services:

SQL Server Magazine has a great article this month showing the details of SQL Server Reporting Services. The article includes some screen shots and a description of using the product. There are two important items in the article.
1) Information on participating in the second beta.
2) Microsoft's plan to make it an add-on to SQL Server 2000.
See the LINKS page of my web site for this article.

""Explains the material well...very knowledgeable" November 2003:

This comment is from my class in November, and is not unusual. I have another set of public classes coming in December for Both Frederick, MD and Leesburg, VA. You can use the link below to review dates, course outlines and registration info:

http://www.kenhamady.com/public.html

Or better yet - schedule a private class at your office, with your data. This is my favorite way to teach, and I charge about half of what Crystal charges for on-site classes. I have personally taught over 1500 satisfied students everywhere from Tampa to Anchorage so don't assume that you are too far away. Call for more information at (540) 338-0194.

Find a date based on Year and ISO Standard Date:

A customer recently asked me for a formula that calculated the ISO Standard week of the year in a specific way. He wanted to input a year and week (ie. 2003 and 46), and he wanted the formula to return the Monday of that week. I have added this formula to list of formulas on my web site. You can adjust it to find any day of the week. Let me know if you find it helpful.

Doing a SELECT in a SQL Expression field:

When teaching SQL expression fields I have always tried to stress that SQL Expressions are different from SQL Statements. A SQL Expression is a column in the report, where a SQL statement is a full query. My short version of this was to say "a SQL Expression can't do a "SELECT".   Well I recently learned that this is not precisely true. Under certain situations, a SQL Expression CAN do a completely separate select from the main report.

The main limitation is that it can only return a single value. So you probably will need a summary function. The following example comes from the Xtreme Sample Database:
   (SELECT   Max ( Orders.`Order ID`)
   FROM     `Orders` Orders)

Normally a CR SQL Expression would error on the SELECT, but if you put this expression in parentheses, Crystal will pass it to the database as a separate query. Amazingly, the column being queried does not even have to come from one of the tables in the report, but can be from another table in the database. In the past I would have recommended doing this via subreport. The advantage of a SQL Expression is that the value returned can be used to control things like Selecting, Sorting and Grouping in the report. To learn more about using complex SQL Expressions see my Experts Guide to SQL Expressions, Options and Commands.

My Library of Crystal Reports Materials:

Expert's Guide to Formulas ($36)
   http://www.kenhamady.com/form00.html

Expert Techniques Vol. I ($19) and Vol. II ($19)
   http://www.kenhamady.com/expert.html

Quick Reference to Crystal Reports in Visual Basic ($16)
   http://www.kenhamady.com/vbref.html

Quick Reference to Crystal Reports in .NET ($14)
   http://www.kenhamady.com/ntref.html

Automatically Updating Repository Objects in Reports:

The repository is still fairly new, and not many of my customers are using it yet, so I am still learning new details. Last month I mentioned that repository objects don't automatically update when you open a report, unless you flip a switch in the report's options. One of my readers, Jacque Murrell of Pyxis, pointed out that when you use the File => Open dialog, there is a a check box at the bottom that tells Crystal to "Update Repository Objects" for the report you are opening. This allows you to do it on the fly. Thanks Jacque.

Reinstall fixes bug:

If all else fails, try a reinstall. Tony Lee of Bri-Chem Supply Ltd. recently sent me an Email entitled "DESPERATE FOR CRYSTAL HELP". They were trying to resolve a bug in the web component of CRv8 on an Win2K server. Fortunately he resolved the problem on his own and shared his result with me, and with the other members of Tek-Tips. I couldn't describe it better than he did:

" This is bizarre but true, and we've verified it. If you run the Crystal 8.0 install with "Typical" selected (instead of complete/custom), none of the crystal objects work properly until you reinstall the SAME installation again. We've done this on clean W2K Server boxes 3 times now, and the identical thing occurs each time. "

In a follow-up Email he told me that they were planning on uninstalling and doing the complete install. When they inserted the CD it asked "reinstall" or "uninstall" so they went with "reinstall". It just ran the same exact install as the first time.

Removal instructions:

I have sent you my newsletter because you are a former client, or because you have contacted me regarding Crystal Reports. If I am mistaken, or if you don't wish to receive the newsletter, please reply to this message with the word 'unsubscribe' in the header. I do not share these Email addresses with anyone else.

Contact Information