Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
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