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