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


Individual Instruction by Ken Hamady

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