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
Copyright
2003 by Ken Hamady
All rights reserved - Republishing this material requires written
permission