Crystal
Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
The
Crystal Reports Underground News - April 2002
an independent source for Crystal
Reports Information
by Ken Hamady, MS
Contents for April:
** Distributing reports via Email
** Expert on-site training or consulting
** How to print "continued on NEXT page"
** New tech articles on CD web-site
** Converting IF-THEN criteria to a WHERE clause
** CRv9 and Unicode support
** Public classes in Leesburg, Baltimore and Philadelphia
** How to improve the resolution when exporting to PDF
** Printing check boxes and other symbols from formulas
** My Quick Reference to Crystal Reports in Visual Basic
** Read back issues at http://www.kenhamady.com/news.html
Crystal Reports and Email Distribution
Several products have this
capability.
Group Level Bursting:
This is the best method. The report is
burst automatically at the time of export. Each piece is loaded
automatically into a separate Email message, with addresses taken from
the report itself.
There are two tools that do this on my LINKS page - Visual Cut and PDF eXPLODE.
http://www.kenhamady.com/bookmarks.html
Single Attachment Distribution:
In this technique, a report is exported from Crystal. Another
program attaches the export file to the Email messages, with Email
addresses
read from a distribution list. This could be used for sending a
single
company sales report to multiple people in the company. Or, you could
send
a recipient-specific report to each user, like an invoice to each
customer.
Here are your options:
1) Seagate Info allows you to create email distribution lists. Crystal
Enterprise has similar features, but only with customization.
2) If you don't want to do programming you can combine Crystal Reports
with WorldMerge, or another similar Email merging program. A
brief description of the process is at the link below:
http://www.kenhamady.com/email.html
3) There is a UFL released by Millet Software that allows one report to
Burst, Export, and Email a second report. This doesn' t require
any
programming and can use attachments in any format supported by Crystal.
The UFLs are available here:
http://www.milletsoftware.com/CUT_Light.htm
Text Parsing:
In this technique, you create a report that generates a custom text
message for each recipient, including their Email address. Then
you
use one of two techniques to parse the text and Email each section of
the
text to the specified user. The options for doing this are:
1) Using a UFL from ChelseaTech. This doesn't export the text, but uses
a custom UFL in a formula to create the EMail messages right from the
report.
http://www.chelseatech.co.nz/ctufl.htm#SMTP
2) Text parsing can also be done by exporting to a text file, and then
using a Visual Basic Program to parse the file into Email
messages. The following links give rough code examples:
http://www.tek-tips.com/viewthread.cfm?qid=30718&page=1
http://www.tek-tips.com/viewthread.cfm?qid=99605&page=1
Expert On-Site Training or Consulting:
I have personally taught Crystal
to over 1,300 satisfied students in more than 30 states. Training
on location is my specialty, and I charge about half of the "List
Price"
for Crystal Training.
Do I know my stuff? Check out the Crystal Reports Forum at
Tek-Tips.com and you will find me listed as the top Crystal Reports
expert out of over 14,000 members:
http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/100/pid/149
Can I teach? Many students have said that my classes are among
the best technical classes they have taken. Ask to speak to a
recent
customer as a reference. Call for more information at (540)
338-0194.
How to print "Continued on NEXT page"
Most Crystal classes will teach
you to print a "Continued" message when a group takes up more than one
page.
This prints on the top of each page in the group (except the
first).
Here is how you print "Continued on next page" at the bottom of
pages.
There are actually several ways to do this, but the one that works in
almost
all situations is below. It uses 3 formulas, and these can be cut
and pasted into your report:
The first formula goes on the Group Header:
WhilePrintingRecords;
BooleanVar Continued:= True
The second formula goes on the Group Footer:
WhilePrintingRecords;
BooleanVar Continued:= False
The third formula goes on the page footer:
The first formula goes on the Group Header:
WhilePrintingRecords;
BooleanVar Continued;
if Continued
then "Continued on next page"
else ""
New tech articles on CD web-site
Crystal Decisions' web site has
come a long way from where it was a couple of years ago. One
recent
handy change is posting the Top 10 Downloads, Knowledge Base Articles
and
Technical Briefs. (Someday, they will even add keyword searches
to
all three of these areas.) I will be keeping an eye on these
lists
for you, as well as highlighting new KB articles that I think are
helpful.
Here are a couple of new items this month:
How to launch a Crystal report from an Access program:
http://support.crystaldecisions.com/library/kbase/articles/c2010793.asp
How to calculate the time it took to process a report:
http://support.crystaldecisions.com/library/kbase/articles/c2010570.asp
This article got me thinking about IF-THEN logic and SQL statements:
http://support.crystaldecisions.com/library/kbase/articles/c2010685.asp
If - Then
logic and SQL Statements
I think this last article might
be missing something. I am not convinced that it is the function
that makes the difference (as stated in the article). Let me give
you a much simpler example. The following 2 selection formulas
use
multiple conditions. They both return the same records in CRv8.5,
but
version 1 of the formula doesn't make into the WHERE clause while
version 2 does:
//Version 1
if {?param1} = 'Past' then {Orders.Order Date} in
AllDatesToToday else
if {?param1} = 'Future' then {Orders.Order Date} in
AllDatesFromToday else
{Orders.Order Date} in Date(1950,1,1) to Date(2050,1,1)
//Version 2
{Orders.Order Date} in
if {?param1} = 'Past' then
AllDatesToToday else
if {?param1} = 'Future' then AllDatesFromToday else
Date(1950,1,1) to Date(2050,1,1)
It appears that when THEN and ELSE specify a value within a single
"rule", the criteria is passed to the WHERE, but when THEN and ELSE
specify separate "rules", the criteria doesn't get passed. That
means the criteria
and has to be processed on the client side, by Crystal Reports.
Crystal v9 and Unicode
Last month I mentioned some of
the
new features in Crystal Reports' next release, CRv9. One of my
readers
pointed out another feature that should make it into v9, Unicode
support.
Unicode makes it easier for reports and other programs to cross the
language
barrier. The Crystal Designer bundled with Visual Studio .NET
already
supports Unicode. I have heard (but not yet seen in
writing)
that a report written in CR for .NET, cannot be opened in CRv8.5,
because
the .NET version is considered a later version. The .NET version
reports should be compatible with CRv9.
Don't just take a class, learn from a Crystal Expert:
Click the "Public Classes" link
above
for dates.
Each class is $675 per student.
Or, call for more information at (540) 338-0194.
How to improve the resolution when exporting to PDF
This isn't a new article, but one
I just became aware of. Graphic objects exported to PDF (and
other
formats that support graphics) often lose some of the resolution, and
look
choppy. This is a known issue, so a simpler fix may come, but you
can
fix it yourself if you don't mind making changes to your registry
settings.
For details see the following CD Knowledge Base article:
http://support.crystaldecisions.com/library/kbase/articles/c2009714.asp
Printing Check Boxes and other Symbols from formulas
Instead of a column that prints
"TRUE" and "FALSE", how about printing a check box symbol, with or
without
the check mark? All you need is the WingDing font, which is on
most
PCs, and a formula like the following:
If
{Order.Shipped} =
"T"
then Chr(254)
else Chr(168)
Place this formula on your
report and format it to use the WingDing font. You will see a box
for each record, with check marks only on the shipped records, or
whatever
your condition is. You can also use other graphic fonts like
Webdings,
Symbol, Map Symbols, Vacation MT, etc. This gives you hundreds of
symbols to pick from, as long as you know the font and the ASCII number
of the symbol that you want.
To see all the symbols you have available, open up the windows
"Character Map" program. This is usually in your start menu
somewhere around
"Programs->Accessories->System Tools". If not there, you
can
search for Charmap.exe and run it. This little program shows all
of
your fonts and the symbols that you have available in each. Here
is
an example of transferring a symbol to your report without knowing the
number.
1) In CharMap,
select the "symbol" font. Locate the bold heart in the lower
right.
2) Single-click on it to
magnify it.
3) Click the "Select" then
the "Copy" buttons at the bottom of the window.
4) Switch to your report
and open one of your If-Then-Else formulas that is character.
5) Paste the symbol in
as the THEN or ELSE value (it will be copyright symbol for now).
5) Put quotes around the
copyright symbol and save the formula.
6) Place the formula on
the report, and select the "Symbol" font.
It should print bold hearts
on some records when you preview the report.
I prefer to use the CHR function, as in my original example. But,
this requires the extra step of finding the ASCII code for the
symbol. One way is to paste your symbol from the character map
into a formula with the ASC() function. This way Crystal will
display ASCII number for you. The following formula:
ASC('©')
would display the ASCII value
169 when previewed. Therefore, the following formula:
CHR(169)
would give you a heart, or a
copyright symbol, or possibly something else, based on the font
selected.
VB Quick Reference Guide:
If you plan on incorporating
Crystal Reports into a VB application, you should get my Quick
Reference
Guide. It gives an overview of the integration methods, with
syntax
examples for the most important commands. It comes with some
source
code examples, including a sample report viewer. It is only $16
and can be Emailed to you within 24 hours. Follow the link below
for more details:
http://www.kenhamady.com/vbref.html
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 2002 by Ken Hamady
All rights reserved - Republishing this material requires written
permission