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"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.
then Chr(254)
else Chr(168)
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.It should print bold hearts on some records when you preview the report.
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.
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