The Crystal Reports Underground News - June 2002
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for June:
** Sneak Peek at Crystal Reports Version 9 (read the full review in the September Newsletter)
** Summary of Crystal Enterprise configuration options
** Expert on-site training and consulting
** Crystal Reports user groups
** New version of Crystal Desk scheduler
** CUT Email UFL does electronic bursting
** How to move values from the last record to the Report Header
** Public classes in Leesburg, Baltimore and Philadelphia
** Carving a memo field into smaller pieces
** My Quick Reference to Crystal Reports in Visual Basic
** Read back issues at http://www.kenhamady.com/news.html
Sneak Peek at V9:
Crystal Reports Version 9 is currently in Beta testing. There are no official announcements of new features, but I have found a few discussions of what is being tested in the Beta version. These are things that might make it into the final release. Below are what I consider the most important features. If these make it to final release, Crystal will be a much more powerful product: To see my review of the final release, see the September issue.
1) Writing/pasting SQL statement directly into the report designer
2) Putting Crystal parameter fields directly into the SQL statement
3) Creating custom functions (like UFLs) within the report designer
4) Storing commonly used items (formula functions, SQL statement) in a repository
5) Using memo fields in formulas
6) Writing string formulas that return 64,000 character
7) Writing loops that evaluate 100,000 times
8) Finding objects in the report using an object navigator
9) Changing the order of items in the sort box
10) Highlighting fields of any data type, not just numbers
Summary of Crystal Enterprise configuration options:
Are you considering Crystal Enterprise or ASP to put your Crystal Reports on the Web? If so, you will find the following article helpful. It explains and compares the 4 different configurations for web deployment:
1) Using "Unmanaged" Reports with CE Standard
2) Using "Managed" Reports within CE Standard
3) Using "Managed" Reports within CE Professional
4) Using Active Server Pages (ASP) without Crystal Enterprise
Read the article at:
http://www.kenhamady.com/ce.html
The article ends with links to detailed documentation on these topics from Crystal Decisions' web site.
Expert On-Site Training or Consulting:
I have personally taught over 1300 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. You will find me listed as the top Crystal expert out of over 23,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.
CR User Groups:
Did you know that there are many regional CR user groups that provide free meetings every few months? They can be a great place to share your experiences with other users and learn about the Crystal product line. Some are fairly new, while others are quite mature. If you already participate in one of these groups, I would love to know what you think. Drop me a line. I will be featuring different groups in future newsletters.
CrystalDesk 3.0 adds Email functions
CrystalDesk has released a new version of their scheduling utility. CrystalDesk 3.0 allows you to schedule reports to be printed, exported and now Emailed using MAPI (Outlook). You can schedule an item once or recurring. CrystalDesk has also added PDF format as an export option, and the ability to view a report on demand (without scheduling it).
Gary has offered my readers an introductory discount of 20% for the month of June. The normal price is $229.00, so your price would be $183.00 if you buy it before June 30.
http://www.crystaldesk.com
New CUT Email UFL does electronic bursting:
In my April newsletter I described options for Emailing reports, including three options for 'Attachment Distribution'. Now there is a fourth option using a set of UFLs released by Millet Software. Here are the advantages:
1) No programming is required (other than writing Crystal formulas)
2) It works directly with your SMTP Email server, without an Email program
3) It creates attachments in any export format supported by Crystal Reports
To use the CUT technique you create 2 separate reports. One is the report to be attached (called the slave). It can be grouped by a Key Field, like Customer Name, and each group will become a separate attachment. This report does not use any of the UFLs so it can be any report you currently have.
The second report is the master report. It holds the Email addresses and a Key Field that matches the slave report's key field. The master report has a UFL formula that runs a single group of the slave report and exports it to a file named for that group. (This by itself is a handy function). Another UFL formula creates an EMail message, and attaches the export file. Other UFL formulas can log the results.
There is an introductory price of only $75 for the entire set of UFLs:
http://146.186.176.195/CUT/CUT.htm
How to move a value from the Report Footer to the Report Header:
Here is a technique I have named the "wormhole", because it can go back in time. It allows you to take one or several values from the last record of a report, and then display them or use them in formulas that occur earlier in the report. Likewise, it can take the last record of a group and print it's values anywhere in the group. This works without variables or subreports, as long as your report has at least one sort field.
Lets say that you have 100 orders sorted by a DateTime field in ascending order. You can't change the sort. But, you need to start the first page of the report with three fields from the very last order. Here is my technique:
First create the WormHole formula. You do this by converting the sort field (or fields) into a single text string. Check to make sure that the formula is formatted to sort correctly (just like the report), especially if you are sorting on dates or numerics. That means that dates should be formatted Year-Month-Day; numeric values need to be padded with spaces; and all fields should be a fixed length in the formula. If you calculate the Maximum of this formula you should always get the value from the last record.
Next, you append the fields to be moved onto the end of this string. They also have to be converted to strings, and each has to be a fixed length in the formula. The following example starts with the sort field (DateTime) and adds the Order Amount and the Order ID as fixed length strings:
ToText({@DateTime},'yyyy-MM-dd hh:mm:ss') +
ToText({Orders.Order Amount},'########.00',2,'') +
ToText({Orders.Order ID},'#####',0,'')
This formula, if placed on the details band, would generate values like these for the last three orders:
"2002-05-29 17:15:02 245.12 98"
"2002-05-30 12:25:00 2346.14 99"
"2002-05-31 14:13:06 14512.15 100"
Last, I put the following formula on the report header:
Val(Maximum({@WormHole})[ 20 to 30])
The Maximum of this formula is still the value on the last record. Taking out positions 20 to 30 gives you the Order Amount from that last record. I can print this on the first page or on any other page. Taking positions 31 to 35 would give me the Order ID from the last record. You can append as many "passenger" fields on the end of the wormhole as you need.
This also works within groups. If you need to take values from the last record of a group, and use them at the beginning of that group, use a formula like this:
Val(Maximum({@WormHole},{Your.GroupField})[ 20 to 30])
By putting the group field in as an argument you are asking for the maximum (or last) value of each group, instead of the last for the whole report.
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.
Carving a memo field into smaller pieces
One of the current limitations of Crystal Reports is that you can't use memo fields (or fields over 254 Characters long) in the most important features. This means that you can't use them in most formulas. You also can't use them for sorting, grouping or selecting records. The typical workaround has been to create a view or stored procedure in the database which splits the memo field up into smaller pieces. You could then use these pieces in your formulas. The downside is that you have to make these changes in the database.
I have just learned of another workaround that works with most SQL databases and doesn't require going into the database itself. You use a SQL expression field within Crystal, and fool Crystal into using the memo field. Crystal has access to the SQL Substring() function, which can take characters out of any character field. However, your memo field won't appear in the list of available fields, so you start by using another character field in your SQL expression. It will look something like this:
{fn SUBSTRING ( Employees."LastName" , 10 , 15 ) }
This would take character positions 10 to 15 from the LastName field. Test this to make sure that your syntax is correct. Then, while still in the SQL Expression editor window, replace the name of the character field with the name of your memo field. You can also change the numbers to be whatever you need:
{fn SUBSTRING ( Employees."Notes" , 10 , 60 ) }
Even though the memo field wasn't listed, in many cases it will be accepted. This SQL expression field can now be used in all 4 of the features I mentioned above. I have tried it myself in SQL Server, and have heard that it works in Oracle. Unfortunately it does NOT work with MS Access.
VB Quick Reference Guide:
If you need to incorporate 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