Crystal Reports Training by Ken Hamady, MS, Reporting
and Training Nationwide
The
Crystal Reports Underground News - Volume 2004.07 (July 2004)
an independent source
for Crystal Reports Information by Ken Hamady
Contents
for July, 2004:
** Free Support for
Crystal Reports
** Crystal ends free support for v8.5
** Putting Percentages in a cross-tab
** 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
** Aligning Text objects with Fields in v10
** Experiences with Crystal Support
** Read back issues at http://www.kenhamady.com/news.html
Free Support for Crystal Reports:
Most of you probably
didn't even know that you get some free support from Business
Objects. It is called Crystal Assist. Crystal Assist
provides you with unlimited support for installation issues, as well as
2 free incidents for non-installation issues. Keep in mind that
all of the support is via Email (telephone support is never
complimentary). You get 2 more incidents each time you buy and
register a new product. So, if you register 5 copies of Crystal
Reports you get 10 free support incidents. However, buying a five
pack of licenses and registering it only counts as one registration and
therefore only 2 support incidents. To submit a support question
via Email use the following link:
http://support.businessobjects.com/support/answers.asp
Crystal ends free support for v8.5:
In a related note,
many of you recently received the notice from Business Objects that
they are dropping Crystal Assist support for Crystal v8.5 at the end of
July 2004. Paid support incidents can still be purchased and used
up until September 2005 when interactive support for v8.5 end
altogether. The knowledge base and other self-service resources
can be used indefinitely.
Version 8.5 of Crystal is still a very widely used version. I
have taught more v8.x classes in 2004 I have of both v9 and v10
combined. However, that is starting to change. Of the nine
private classes that I have scheduled between now and the end of
September, most of those will be using v10. It appears that many
users have simply skipped v9 altogether.
The recent notice from BO also included a link to their life cycle
schedule showing when v9 and v10 would lose their support. It is
currently 3-4 years after their release, but the page says that the
minimum length of support is 30 months. The support cycle, like
the product cycle, seems to be getting shorter.
Version Released
Support Ends FREE support
ends
10
01/07/2004
04/31/2007 (not specified)
9
08/19/2002
04/01/2006
02/28/2005
Percentages in a Cross-Tab:
This is a very common
request that is not fully supported by Crystal Reports. Starting
with v9 you can have a series of cells calculated as a percentage of
the total for that series (either row or column). However,
anytime you want to take two summary fields and find one as a
percentage of the other, or do some other calculation, this is not
supported directly by the Crystal Crosstabs.
Say you have a cross-tab that has Sales Reps down the side and Months
across the top. You put 2 summary fields in each cell. The
upper one counts all quantities shipped while the second counts only
the Express Shipped quantities.
Standard crosstab percentages would calculate the percentage each
Salesmen was of the Grand total. Or you could do each Month the
same way. But what if you would like to have a third number in
each cell showing the percentage of Express Shipped quantities to the
total quantity in that same cell. In other words, divide one
summary field in the cell by the other. Standard Cross-tab
percentages don't support this. However there are 2 ways that I
know of that allow you to do this. One simple but limited, the
other more complex but more flexible.
Technique #1
The simpler technique is one I recently read about in Tek-Tips (thanks
to Shelby55). It uses a weighted average of a special formula to
relate one cell to another. The down side is that it can only be
used when one of the fields you are summarizing is a subset of the
other. In our example every express shipped quantity is also part
of the total quantity. Here are the steps you would need.
1) Choose the data field or formula for your detail quantity (the 'all'
field). If I were using the Crystal sample data I would just use the
data field quantity. Note that this cannot be a total field.
2) Write a formula for the subset, in my example the express
shipped Quantities. This also cannot be a total field.
if {ExpressShipped}
then {Quantity}
else 0
3) Use 1 and 2 in a percentage formula:
if {@ExpressQty} > 0
then {@ExpressQty}/{Quantity}*100
else 0
4) Put these three fields in as your cross-tab summary fields.
Sum the first 2 but do a weighted average of the third. The
weight field would be {Quantity}. This third field will show the
correct percentage in every cell of the cross-tab. It
automatically carries to the total rows and columns.
Technique #2
As I said, the
first technique will only work if one of the numbers is a subset of the
other. But lets take a different example. Say that you have a
cross-tab with rows by Country and 12 columns - one for each
month. In each cell you want to show the month for this year as
the top number, then the same month for last year as the second number
and finally the percentage increase(decrease) as the third
number. The weighted average technique won't work here because
one number is not a subset of the other. Now you have to use a
more sophisticated technique that relies on passing a variable from one
cell to the next. It also relies on the "Display String" property
which is only available starting with v9. I have a work around
that comes very close in v8.5, but doesn't look quite as nice.
Here are the steps:
1) Create two formulas, one for each year, that each look something
like this:
If Year ( {DateField} ) = 2004
then {Amount}
else 0
2) Make these two formulas the first two summary fields.
3) Your third summary fields can be pretty much anything in v9, but
should be the Minimum of the following formula in v8.5:
WhileReadingRecords; 0
//Thanks to
Shawn Thomas for pointing out that this can be just a zero
4) Once you have these three summary fields in place, preview your
cross-tab and highlight the first (top) number of the 3 summary
fields. Use the format menu to format the number and put
the following formula in the "suppress" conditional formula:
WhilePrintingRecords;
numbervar Top := CurrentFieldValue;
False
5) Select the second summary field and use the following as the
suppress formula for that number:
WhilePrintingRecords;
numbervar Bottom :=CurrentFieldValue;
False
6) Select the third summary field and use the following as the
conditional formula for the "Display String" property in v9 or
v10. In v8.5 put this formula in the conditional formula for the
currency symbol character. In v8.5 you should also make sure that
you delete the character shown in the "Show Zero Values as"
property. (Thanks again to Shawn Thomas for
pointing out that you can delete this character. )
WhilePrintingRecords;
numbervar Top;
numbervar Bottom;
if Top <> 0
then totext((Bottom - Top) % Top) + '%'
7) Repeat steps 4 - 6 for the row total cells, the columns total cells
and the grand total cells. That is because these formatting
attributes are not automatically carried to the totals.
If you want to read more about using variables with cross-tabs, you
should get my "Expert Techniques Vol 1" which has an example of running
totals that accumulate inside a cross-tab. It also explains the
order in which the cells are evaluated.
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
Aligning Text objects with Fields in
v10:
I frequently use the
"guidelines" for aligning objects into rows and columns. I find
this much more flexible than the "Snap to Grid" feature which I usually
keep off. Typically you can take a text object or data field and
attach it 5 ways to a guideline. The 5 ways are left edge, right
edge, vertical center, top edge or text base line. At least that
was true through version 9. With version 10 Crystal accidentally
changed the way text objects get attached so that they attach
differently than database fields. The attachment options for
vertical guidelines are the same, but a text object can now attach 3
ways to a horizontal line. They are top edge, bottom edge and
horizontal center. The text baseline got dropped. This
makes it difficult to align a text description beside the field that it
describes. The text aligns to the bottom edge and the data aligns
to the text baseline, which makes the data field a bit lower.
Even if you select both items and use the format menu to say Align
Baselines, the text will line its bottom edge with the baseline of the
data field. The only work around that I have found is to:
1) Snap the data field to the guideline by its text
base line
2) click on the text object
3) Hold your [CTRL} key
4) Click on the data field
5) Use Format => Align => Bottoms.
Even though the text is not snapped into place on the guideline, it is
at least in line with the field.
One of my customers in England raised this issue with his Crystal sales
rep and then kept at them for almost a month so that the fix for this
'feature' made it into the latest hot fix. In the process my
customer learned more than they wanted to learn about Crystal's
Support. If you have had a particularly good or bad experience
with Crystal Support, read the next section and drop me a line.
Experiences with Crystal Support:
When my customer
presented the alignment bug to Crystal support, he figured it would be
included in the next hot fix. He then got a quick lesson in how
Crystal prioritizes their bug fixes. So, if you ever need to
escalate an issue like this with Crystal support, be prepared to answer
the following questions:
How many licenses of Crystal Reports do you have?
How many users will be affected by this issue?
What amount of revenue is at risk?
What is the impact to your company?
The impression my customer shared with me is that BO spends as much
effort managing the priorities as they do actually fixing the
bugs. Do any of you have an experience (positive or negative)
with Crystal Support that you would like to share. If so, drop me
a line.
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