Crystal
Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
The
Crystal Reports Underground News Volume 2009.01
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for January 2009:
** SAP resurrects the "screenshots" issue
** Expert Techniques vol. 1 Update for CR 2008
** Learn Crystal Reports (your place or mine)
** Using AutoComplete in Crystal Reports formulas
** Shared Variables from a suppressed subreport
** My Library of Crystal Reports materials
** Ranking based on a ratio
** Other recent blog articles:
Global variables fail in multi-page Cross-tabs
Converting Text to Date or DateTime
The original creators of Crystal Reports try again
More label printer problems
Read Back Issues
SAP resurrects the "screenshots" issue
Some of you may remember my "Don't Pay Twice"
campaign back in the summer of 2005. It exposed Business Objects'
attempt to eliminate independent training by making it more expensive.
Business Objects did convince several training providers that they
needed to pay special fees for the privilege of offering Crystal
Reports public classes. These providers were also required to buy
courseware from one of two authorized vendors. It appeared that
"authorized" meant they were willing to pay Business Objects a $30-$40
fee for every course book they sold. So why would courseware vendors
agree to pay this exorbitant fee?
Business Objects claims that no one can use a Crystal Reports
screenshot in a book without their approval. They sent letters to
several vendors in 2005 saying just that and most were ignored. Nothing
more was said in the three years since. Now it appears that similar
letters are being sent by SAP (who now owns Business Objects). I read
one of the letters this past week. It talks about screenshots as before
but now adds a new warning against using SAP trademarks, like the term
"Crystal Reports". The letter was very impressive. It had majestic
sounding references to various sections of US copyright and trademark
law. Sprinkled throughout the letter was the Latin incantation "inter
alia" to make it seem almost pontifical. It sounded so ominous that it
brought to mind the blustering of the Wizard of Oz, which ends
fittingly with "ignore the little man behind the curtain".
As I explained in 2005, using screenshots of a software product in a book is a "fair use" of a copyrighted work (see Sony vs Bleem).
And there are also several clear cases to show that "nominal" use of a
trademark word or phrase is fine for any purpose at all, so long as you
are not claiming to be affiliated with or authorized by the trademark
holder (see Volkswagen vs Church). But being right in a case like this doesn't always make you safe from a lawsuit (although it helps).
The real irony is that SAP might have to pay up to US $1 Billion for illegally using Oracle software and materials. That kind of loss won't help their current financial position any, and it may also be why they have started to aggressively cut costs as shown by this leaked internal memo.
Hopefully they won't want to add to their losses (both goodwill and
karma) by continuing to threaten the people who promote their product
line.
And thanks to Jay Hackenbracht for these last three links.
Expert Techniques vol. 1 Update for CR 2008
I recently reviewed my original volume of my Expert Techniques
and was pleased to see that most of it is still just as useful as it
was five years ago. But out of the 30 problems that I helped you solve
in 2003, six have now become solvable by using new features in Crystal
Reports. So I have rewritten six articles to reflect the changes in
Crystal Reports. I still explain the old solutions for people using
older versions, but I add the new solutions and specify the version of
Crystal Reports you must have to use each one. There are new sample
reports for all 6 articles.
If you purchased Volume 1 in 2009 you already have the latest
version. If you purchased it in 2008 (or earlier) please let me
know and I will send you the update. Here are the updated articles:
2. Titles on Cross-tabs that spills onto virtual pages
6. Finding the subreports in a report
7. The Stealth Subreport - it runs but is invisible
16. Selecting Ascending or Descending order based on a parameter
18. The user chooses the number of groups to show in a TopN report
19. Finding a where a stray parameter (or formula) is being used in a report
Learn Crystal Reports (your place or mine)
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. 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,500 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.
Using AutoComplete in Crystal Reports formulas
Here is a little trick that not everyone knows.
The Crystal Reports formula editor has an autocomplete function that
allows you to type the beginning of a function or other reserved word,
and let Crystal give you choices to pick from. To invoke the
autocomplete you type CTRL - Spacebar and a window will open with all
of the reserved words in it. As you type the list will narrow down to
the functions that start with the characters that you have typed.
So say you want to use the function InRepeatedGroupHeader. You do your
CTRL - Spacebar and type "IN" and you get a choice of 10 options. But
if you add the "R" there is only one available choice so that choice is
entered into your formula for you.
Or if you start typing a function you can hit the CTRL - Spacebar and
the same window will open showing the matches for what you have typed
so far.
This feature is available in versions 10 and later.
Shared Variables from a suppressed subreport
A few years back I wrote about the Stealth Subreport.
This is a subreport that doesn't appear on the report but does run in
the background, providing shared variables to the main report. One of
the limitations I listed at the time was the need to have a visible
section that occurs at the same frequency as the subreport, so that the
subreport can be hidden behind the section. In other words you couldn't
have a suppressed subreport at the detail level in a summary report
where no Details were visible.
But I just learned that this limitation went away with v9 of Crystal
Reports. For versions 9 and later there is a method that allows you to
truly have a suppressed subreport that is run invisibly in the
background. It stems from a new feature introduced with version 9
called "Suppress blank Subreport". Normally this used in combination
with "Suppress blank Section" to hide a subreport that doesn't find any
data
What I didn't realize until this month is that "blank" can also mean a
subreport that has all of its sections hidden or suppressed. So even if
there is data, as long as there is no visible output, the subreport is
considered blank and will be suppressed. I also didn't realize that
using these properties to suppress the subreport does not prevent the
subreport from running. Normally suppressing a subreport means that it
won't run. But apparently these blank subreports are run before Crystal
determines that they are blank, so there is time to actually store and
capture their shared variables.
Thanks to Phyllis Swenson of CFI who pointed this method out to me.
My complete Library of Crystal Reports Materials
Let a master teacher help you understand these
Crystal Topics. Each guide comes with clear explanations and
sample files to illustrate each concept.
Expert's Guide to Formulas ($36)
Expert's Guide to Subreports, Parameters and Alerts ($28)
Expert's Guide to Totals ($24)
Expert's Guide to Cross-Tabs ($22)
Expert Techniques Vol. I ($19)
Expert Techniques Vol. II ($19)
Expert Techniques Vol. III($19)
Quick Reference to Crystal Reports in Visual Basic ($16)
Quick Reference to Crystal Reports in .NET ($14)
You will find these on the LIBRARY page of my site.
Ranking based on a ratio
I recently reread a newsletter article
I wrote a few years back and was reminded of a trick that I had
forgotten. The article was about ranking groups using a formula that
contains summary functions. It is simple to summarize (subtotal) most
formulas in a report. And once you have a summary field it is usually a
simple matter rank the groups based on that subtotal. Crystal calls
this a Group Sort (or a TopN in older versions).
The challenge comes if the formula itself contains a summary operation.
Then Crystal will not let you do a summary of that formula, and then
there is no way to rank the groups based on that formula. However the
article I wrote points out two very important exceptions to this
rule: You can do a summary of these formulas either inside a
cross-tab or inside a chart.
So take a common formula that calculates a profit margin percentage:
( Sum ({Orders.Amount}, {Customer.Customer Name}) -
Sum ({Orders.Cost}, {Customer.Customer Name}) )
% Sum ({Orders.Amount}, {Customer.Customer Name})
If I wanted to find the groups with
the highest margins or rank all of my groups by there margin, I would
have a problem. Crystal won't let me summarize this formula which means
no TopN or ranking feature for the groups. But, I can do the ranking or
TopN in a cross-tab. The steps would be:
1) Create the formula
2) Add a cross-tab with customer as the row field.
3) Add the formula as the summarized field and change the summary to maximum.
4) Apply the TopN to the cross-tab (not to the groups).
This works all the way back to v9. These formulas are also available
for summarizing in charts, even back to v8.5. And, since a chart can
also do its own TopN, we can find the top 5 profit margin percentages
using a chart in v8.5 or later.
If you like articles like this you might want to check out my three volumes of "Expert Techniques".
Other recent blog articles:
Global variables fail in multi-page Cross-tabs
Converting Text Dates to true Date or DateTime
The original creators of Crystal Reports try again
More label printer problems
Removal instructions
I have sent you my newsletter because you or your
company are one of my consulting or training customers. If you
don't wish to receive the newsletter you can use the link below.
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
2009 by Ken Hamady
All rights
reserved - Republishing this material requires written permission