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


Individual Instruction by Ken Hamady

The Crystal Reports Underground News - Volume 2008.03 (March 2008)
an independent source for Crystal Reports Information by Ken Hamady

Contents for March 2008:

** BO refuses to fund UG meetings based on the speaker
** Open enrollment classes March 2008
** PeachTree 2008 table changes
** Cascading parameters that lose values
** Cascading parameters with a duplicate field name
** Expert's Guide to Subreports, Parameters and Alerts
** Converting a decimal to a fraction
** Other recent BLOG topics
** Read back issues at http://www.kenhamady.com/news.html

BO refuses to fund UG meetings based on the speaker

It looks like Business Objects is trying to put restrictions on User Group speakers again. So far it is just one consulting group - CTTS in southern Michigan. CTTS is a former BO partner who currently leads two BO user groups.  They also provide very popular presentations for other BO user groups across the country.

But CTTS recently sued BO and BO was forced to settle.  BO must not have liked the settlement terms. BO now refuses to participate in any meeting where CTTS is doing a presentation. According to the UG in Pittsburgh they go so far as to refuse to send an Email to the members telling them about the meeting. Despite this, CTTS is still being invited to meetings all over the country.

But BO has convinced some UGs not to use CTTS for meetings. And at least one UG in Kentucky has asked CTTS staff not to even attend their meetings, let alone speak at them. BO denies that their policy forbids anyone from attending meetings. And BO reps have told me that this does not (currently) apply to other independent speakers - just to CTTS. But if user groups start dropping speakers to keep BO happy, then I expect BO to use this tool more broadly down the road.

The sad part is that the volunteer boards of these groups have to work pretty hard to find practical and interesting presenters. If you are part of a BO user group you should ask your board where they stand on this issue, and let BO know what you think.


Open enrollment classes March 2008

You need information to run your business, so stop struggling with Crystal Reports and learn how to use it fully.  Come join one of my small classes (3-5 students) and learn how to make Crystal work for you.  My Intro class makes sure you know all of the basics.  We even include material on cross-tabs, charts and formulas.  The Advanced class shows you how to solve several common reporting problems as well as when to use advanced features like running totals, subreports, parameters and commands. Users of any version are welcome.  Classes are held in Frederick, MD and the next round is March 17 - 20.  See my web site for more details or call me at (540) 338-0194.

So what makes my class different?  I have written my own course materials and have used them to teach over 2,000 satisfied students.  And, I give you a toll-free number so you can call me after class with questions.  

Or, if you want to schedule a class at your office, using your data, that is my specialty.  I have even found several other top-notch instructors around the US, UK and Canada that can deliver my class at your location for a very competitive price.  Call or visit my web site for more details.


PeachTree 2008 table changes

Several customers have asked me to fix Crystal Reports after upgrading to PeachTree 2008. These are reports that worked fine under previous versions of PeachTree but 'break' when run against 2008. And in most of the cases the customer had been assured by PeachTree sales that the upgrade would not affect custom reports. But if you are using any balances from the CHART table or the billing address from the Customer table then your reports need to be updated.

All of the balance fields in the CHART table have been renamed, although the number of fields and the order they are in seems to be the same. And the billing address fields in the CUSTOMER table have been moved to another table. So, if you need help with custom reports for PeachTree 2008 (or any other version) give me a call.


3-Level Cascading Parameters that lose values

This one is hard to describe without an example. Say you used the following 8 records to create a three-level cascading parameter with Vendor, Category and Model:

Dell   / Notebook / Model_A
Dell   / Notebook / Model_B
Dell   / Desktop  / Model_1
Dell   / Desktop  / Model_2
Compaq / Notebook / Model_X
Compaq / Notebook / Model_Y
Compaq / Desktop  / Model_8
Compaq / Desktop  / Model_9

You would first choose the vendor(s), then you would choose the category within that vendor and finally pick a model from that category. But say you picked both Compaq and Dell and then selected Notebook. You should see all 4 Notebook models ( A, B, X and Y )but you wouldn't. In Crystal versions 11.5 or 12 (also know as XIr2 and CR 2008). You would only see two of the four Notebook models. If you select Compaq before Dell at level 1 you would see X and Y. But if you you select Dell before Compaq you would see A and B.

Apparently this stems from a "fix" for a related problem in version 11 (XI). In version 11 when you selected both Compaq and Dell you would see Desktop twice and Notebook twice - once for each vendor. But there was no way to distinguish between the two duplicates. They fixed the problem by dropping one of the duplicates, which is fine if that is the end of the cascade. But any lower level value tied to the dropped duplicate are lost.

Hopefully this will be fixed in CR12.  For now there is one workaround that I have found. You use a command to supply the values for the dynamic parameter and replace the duplicate field with an expression that combines the duplicate field with a portion of it's parent value. So the data above might look like this:

Dell / Notebook-D / Model_A
Dell / Notebook-D / Model_B
Dell / Desktop-D / Model_1
Dell / Desktop-D / Model_2
Compaq / Notebook-C / Model_X
Compaq / Notebook-C / Model_Y
Compaq / Desktop-C / Model_8
Compaq / Desktop-C / Model_9

This works in all versions because it ensures that all the category values are unique across vendors. If you want to see all Notebooks you would select both vendors and then select Notebook-D and Notebook-C in the second level. A few extra clicks but it works today. If you have trouble writing a command or a concatenation expression you might find my "Expert's Guide to SQL Expressions, Options and Commands" helpful.

And, thanks to Rob Hershfield of United Data Strategies, Inc. for discovering this problem and sharing the original KB article.


Cascading parameters with a duplicate field name

Another problem with cascading parameters occurs if you have two fields in the cascade with the same field name. This isn't unusual when you have several tables that all use the names "Description" or "Code". But in a cascade each level can be used to create a separate parameter.  Each parameter is named after the field that provides its data. So when CR finds a duplicate field name in the cascade it tries to create a duplicate parameter name and this generates an error. There is no visible option for renaming either parameter within the cascade.

The solution is to click "Create" on only one of the duplicate levels and then save the entire parameter. Once the parameter is saved you can rename that first duplicate so it isn't a duplicate anymore. Then you can go back and Edit the parameter's properties and click "Create" on the second duplicate. Since the first was renamed, that second parameter name won't be a duplicate any more.

If you create your dynamic parameters from commands (something I usually recommend) you can prevent this problem altogether by adding an alias to one of the duplicate fields within the SQL.


Expert's Guide to Subreports, Parameters and Alerts:

Why not let a master teacher walk you through these 3 important (and sometimes confusing) Crystal Reports topics. I have explained these features to hundreds of students and my exercises have been refined through my 12 years of teaching Crystal. Put that experience to work for you with my E-book, "The Expert's Guide to Subreports, Parameters and Alerts".  The material includes an in depth discussion of each feature, example reports that illustrate the techniques, and exercises that walk you thorough each step.  The exercises are the key.  They have been written to provide practical examples that are easy to understand and follow.


Converting a decimal to a fraction:

One of my customers asked if I could help them fix a formula found in the Business Objects knowledge base (c2012665).  The formula converts a decimal number into fraction format. In other words it converts .75 into "3/4". The formula used a clever loop but it had 3 problems:

1) An integer results in a fraction with a 0 on top like "1 0/2".
2) An number between 0 and 1 (like .75) shows a zero like "0 3/4".
3) The formula used BASIC syntax which is unfamiliar to many users.

So I converted it to Crystal syntax and cleaned up the output. The result is below.

Note that the formula rounds the decimal value to 4 places (see the 3rd line below). You can round it to more or less if you choose. But if you are using v8.5 you MUST round to no more then 2 decimal places or Crystal will start to error because there are limits on the number of iterations that v8.5 can do.

NumberVar raw :={your.field};
NumberVar wholeNumber := int(raw);
NumberVar decInput := round (raw - Wholenumber , 4);
NumberVar numerator :=1;
NumberVar denominator :=1;
NumberVar decimalFraction := numerator / denominator ;
Do ((If decimalFraction < decInput
Then numerator := numerator + 1
Else ( denominator := denominator + 1 ;
numerator := truncate(decInput * denominator)))
;decimalFraction := numerator / denominator )
While decimalFraction <> decInput;

(if wholenumber > 0
then totext(wholeNumber,0,"") + " "
else "")
&
(if numerator > 0
then totext(numerator,0 , "") & "/" & totext(denominator,0 , "")
else "")

After it was fixed the same customer requested that the value be rounded to the nearest 1/16th. This can be done using formula 22 on my Formulas page to round the value to the nearest 0.0625 (or 1/16).  I would do the rounding in a separate formula and use the rounding formula as the 'raw' input in this formula.


Other Blog topics since the last newsletter:

    Convert Nulls and Selecting Nulls
    Appending Strings in v8.5 into one long string over 256 Chars
    Trouble with Business Objects ESD licensing system
    Mystery calendar for date parameters in v10 on Vista
    Business Objects releases Viewer XI for Linux
    Recent Upgrades for third party utilities
       


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 2008 by Ken Hamady
All rights reserved - Republishing this material requires written permission