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