The Crystal Reports Underground News - Volume 2004.10 (October 2004)
an independent source for Crystal Reports Information by Ken Hamady
Contents for October, 2004:
** Distinct Sum when the duplicates are not consecutive
** Registry key to turn off auto linking in v9
** Public Intro and Advanced classes in Frederick
** Free and Simple Bar Code Fonts
** Tricks to using the Mailing Label Wizard
** 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
** Finding the First or Last Friday of the Month (or any weekday)
** How many Mondays in a date range
** Read back issues at <http://www.kenhamady.com/news.html>
"Distinct Sum" when the duplicates are not consecutive:
Crystal has a distinct count function but not a distinct sum. A distinct sum would be a sum that eliminates duplicate numeric values based on duplicates in another "key" field. Something like this: "only add the customer's balance into the total once - even if this customer shows up on several records". The customer ID would be the "key" field while the credit limit would be the numeric.
I was recently showing students one solution using running totals that "evaluate on change of group". This method eliminates duplicates from a sum, but only when the duplicates can be grouped together. One of the students described a very complex formula that they used at her office to do a "distinct sum" when the duplicates were NOT grouped together. I assumed that they used an array variable, but she said she didn't think so. I was intrigued.
When I reviewed the formula she was using I found it to be overly complex, but buried in it was a method of doing a distinct sum - even when the duplicates were scattered. When simplified, this method only requires two variables. I searched to see if anyone else had described this technique on the internet and couldn't find anything like it. So thanks to Tina Rieger and her team at MIL Corporation for this innovation.
To use this method you first create an automatic running total field that is a distinct count of the "key" field. Then you create another running total, using a formula with variables. This formula totals the numeric field, but only when the first running total has changed from the record before. Any record that changes the first running total must have a new value for the "key" field. If the first running total doesn't change it means that this record's "key" field value has appeared before. The formula I used looks like this:
WhilePrintingRecords;
NumberVar Prior;
NumberVar LYS;
if {#CustCount} = Prior + 1 //which indicates a first record for this customer
then LYS := LYS + {Customer.Last Year's Sales};
Prior := {#CustCount}; //store the current count to compare with next record
LYS
If you needed a Disctinct Sum Subtotal you could reset both the running total and the variable with each group.
In the past I might have suggested creating an array of all of the "key" values and checking each "key" value against the array before adding the numeric. This is much more complex than the formula above. And, since the Distinct Count in Crystal maintains an internal array you can simply piggy-back on that rather than creating your own.
If you would like to learn more techniques like this one you will probably enjoy my "Expert Techniques" guides. I currently have 2 volumes and a 3rd in the works. Each has 30 techniques that I have discovered over the years. See the LIBRARY page of my site for more information and a short description of the techniques covered in each volume.
Registry setting to turn off Auto Linking in v9:
Last month I mentioned Crystal's Auto Linking feature. I said that you couldn't turn it off in v9, and that you had to go into the registry to turn it off in v10. I got a note from Brian Reed of Crystal Advantage who pointed out that Service Pack 3 for CRv9 provides a registry key to turn off the Auto Linking. In v9 the key is called DoSmartLinkingByKey but in v10 it is called DoAutoSmartLinking. You can read an article about the v9 key on Crystal's web site by searching their knowledge base for article c2012286.
I then got another note from Paul Marrano, who teaches Crystal down under. He pointed out that if you use this registry key in v10 it may disable your ability to create new reports against business views. Apparently auto linking is essential to creating a new report with a business view, but existing reports against business views seem to run after the change is made.
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 1875 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.
Free and Simple Bar Code Fonts:
I recently wrote an article about printing bar codes and I mentioned that there are some free bar code fonts available, especially the "3 of 9" type. One of my customers at the Library of Congress uses one of these free fonts and said it was really easy to do. After a bit of research I have to agree. Here are the steps:
1) Download a free bar code font. My LINKS page has a link to some free fonts.
2) Install the font on your computer
3) Write a string formula in your report that puts your numeric code in double quotes
4) Format this formula to use the new font.
That is it. Your report should now print bar codes. Of course there are more sophisticated bar codes than the "3 of 9", but it is hard to argue with free.
Tricks to using the Mailing Label Wizard:
Crystal has a neat label wizard that allows you to automatically set up the most common label formats. It comes with preprogrammed settings for the most common Avery label sizes. However there are two common problems encountered when people try to set up these labels.
Problem #1) The Wizard will only put one field on each line of a label. So, when you add City-State-Zip, Crystal puts each of these fields on a separate line. The solution is to add only the first field of each line while you are in the wizard. Then when you preview the report you write a formula field that combines all of the fields that you need for each line. You put the formulas in place of the single field that you used to start each line.
Problem #2) This label wizard always creates detail labels. If you have duplicate details and you want to print the label at the group level it is difficult to use the wizard. However, you can make it appear as if the detail labels are really at the group level by only printing the first label of each group.
My approach is to add the group but suppress both the group header and the group footer. Then create a running total field that counts the records in each group, making the running total reset 'on change of group'. Once this is done you can suppress the detail section with a condition that the says:
{#running total} > 1
All records will be suppressed except the first record of each group. This allows you to use the wizard to get the layout automatically, but still print one record per group.
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>
The First/Last Friday of the Month, or the number of Fridays in a date range:
I have recently updated my FORMULAS page to include formulas for finding:
(#19) Last occurrence of any weekday in a month (ie date of the last Friday)
(#23) First occurrence of any weekday in a month (ie date of the first Friday)
(#24) Number of occurrences of any weekday within a date range
You can cut and paste these formulas into any report and simply replace the fields with your own database fields. If you need help implementing any of them I can always be "rented".
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