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}) -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:
Sum ({Orders.Cost}, {Customer.Customer Name}) )
% Sum ({Orders.Amount}, {Customer.Customer Name})
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