phone: (540)338-0194
email: ken@kenhamady.com


Report Miner tools to provide documentation on your Crystal Reports

The Crystal Reports Underground News - Volume 2004.08 (August 2004)
an independent source for Crystal Reports Information by Ken Hamady

Contents for August, 2004:

** Actuate offers a Crystal 'converter' service
** Using the Performance Info in v9 and v10
** Finding groups where the last record meets a criteria
** Public Intro and Advanced classes in Frederick
** 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
** Export to a secured PDF (no Printing, Changing, etc w/o password)
** Rounding to the nearest 5 cents
** Read back issues at http://www.kenhamady.com/news.html

Actuate offers Crystal 'conversion' service:

One of my customers mentioned to me that Actuate had developed a conversion utility that can convert a Crystal Report into an Actuate report.  I was intrigued so I did a quick search and found the Actuate press release that describes this feature. 

I found that this "utility" can only be used by Actuate staff as part of a conversion service package purchased by the customer.  The package is a 5-day consulting engagement that has a "fixed price" of about $10,000.  If that isn't enough time to convert your reports, the engagement can be extended on a time and materials basis.  Actuate says that most of the reports that they have converted took an hour or less, but they don't describe the complexity of these reports.  And, it is obvious that there is more involved in the conversion than running the report through a utility.

If anyone has had experience with this process, or received a quote on a conversion, I am sure other readers would be interested. 

Using the Performance info in v9/v10:


Ever have a slow report and wonder why it was running slow?  If you are using v9 or v10 you can look at the Performance Information to help determine the cause.  Performance Information shows you several categories of statistics about the report.  To see complete statistics, make sure the report has been previewed and that you have gone to the last page of the report. 

Once the report has been run, look for this feature at the bottom of the "Report" menu.  The window will show several categories of information and the last will be the "Performance Timing" statistics.  This lists several time figures, often in milliseconds, that tell you how much time was used in each step of the reports generation process.  If you have subreports, each subreport will have it's own performance table.

If most of the time is being used running the database query, you might want to check the SQL to make it more efficient.  If most of the time is spent reading the records, then you might check for overly complex formulas that are slowing things down within CR.  A delay in formatting the first page might mean that subreports or cross-tabs are causing the delay.  If you have a slow report and would like an expert to take a look at it.  Give me a call.


Finding Groups where the last record meets a criteria:

One of my students presented me with the following challenge.  Their address records are stored in a table that keeps an address history.  So a new address doesn't replace the old address; it is added as a new record with a time stamp.  The current address is the one with the latest date and time.

To display the current address is fairly easy and can be done in one of two ways: 

1) Group by Client and Sort by time stamp.  Hide the details and place the address fields in the Group Footer.  This would display the last address for each account.

2) Or, put in a group selection formula that says:

 {Address.TimeStamp} = maximum ( {Address.TimeStamp} , {Group.Field} )

Either of these will work to show the current address for each account.  But if you try to select only current addresses in a particular state, you have a problem.  The State criteria will become record selection, which happens before the records are grouped.  So, if you ask for addresses in New York,  Crystal will select ALL the New York records regardless of whether the are current or old addresses.  THEN it will do the grouping.  You would get the last New York address for each account, rather than getting the accounts that have New York in their last record.

My solution is to create a formula that combines the Date and the State in a single string field.  The date comes first in the string and must be formatted so that it still sorts chronologically.   If you use this field in your Group Selection Formula you can have the filtering happen at the proper time.  The combined string formula field, which I will call {@combine} would look like this:

    ToText ({Address.Date} , "yyyy-MM-dd") + {Address.State}

The format string in the formula is important to ensure that the date sorts by year first and then month and day.   Then you add a Group Selection formula that looks like this:

    Maximum ({@Combine} , {Your.GroupFieldHere} ) [ 11 to 12  ] = "NY"

This will allow groups to show only if their latest address has NY as the state.  This is a variation of my WormHole technique as described in Expert Techniques volume II number 40.  The Wormhole allows you to grab a database field value from the last record of the report and display it on the first page of the report.  It even works with some formula fields. 


Public Intro and Advanced classes in Frederick:

My specialty is teaching at your office, with your data, but not everyone has the 3 or 4 students to make this cost effective.  If you want to take one of my classes and don't have enough people for an on-site class, come to Frederick, MD and take my public class.  The classes are always small and informal with lots of room for discussion.  For details see:

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

Is it worth the trip?  I have personally taught over 1750 satisfied students. And, I am still the all-time, top ranked Crystal Reports expert at Tek-Tips.com.  For more information, or to schedule an on-site class, call me at (540) 338-0194.


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


Export to a secured PDF (control  Printing, Copying, Changing):

Millet Software has just added a new feature to Visual Cut.  It allows you to encrypt & secure your PDF exports, using the Adobe security model.  This allows you to control what different user's can do with the PDF, such as printing the file or copying text from the file. 

When you export the PDF you can set two different passwords for each PDF. One with limited rights that you determine.  The other with full rights.  You then provide your user's the appropriate password depending on what rights you want them to have. 

See the LINKS page of my site for more information on Visual Cut, or contact me if you would like to request a free copy for evaluation purposes.


Rounding to the nearest 5 cents:

Rounding to the nearest penny, dime, dollar or thousand dollars is simply a matter of using the round function and specifying the correct number of decimals.  This week a reader sent me a formula that was supposed to round to the nearest nickel.  The formula didn't work and seemed too complex to fix, so I wrote my own formula and thought I would share it with my readers.   Of course as soon as I did, one of my readers gave me an even simpler version.  Here it is and thanks to Mike Barron who told me that he found this formula long ago on an Excel forum

     ROUND ( {database.field} / .05 , 0 ) * .05

What I like most about this, other than the sheer simplicity, is that you could replace the ".05" with any odd increment and the rounding will work.   See this formula, and many others on the "Formulas" page of my web site.


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:

Ken Hamady, MS
525K East Market St. 
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com

Copyright 2004 by Ken Hamady
All rights reserved - Republishing this material requires written permission