Crystal Reports Training by Ken Hamady, MS, Reporting
and Training Nationwide
The
Crystal Reports Underground News - Volume 2008.05 (May 2008)
an independent source
for Crystal Reports Information
by Ken Hamady
Contents for May 2008:
** Article comparing the big BI vendors
** CR Server XI for $2,495 (CR Server 2008 coming?)
** Open enrollment classes May 2008
** Displaying chosen parameter values with descriptions
** Moving/sizing objects with Shift-Arrow and Ctrl-Arrow (and crashing CR)
** Conditional Page Footer
** Expert's Guide to Cross-tabs (CR 2008 Update)
** Other recent BLOG topics
** Read back issues at http://www.kenhamady.com/news.html
Article comparing the big BI vendors
I recently read a good article in Information Week.
It discusses the recent consolidation in the BI market and compares the
primary vendors, explaining the strengths and weaknesses of each. The
article mentions SAP's decision:
"to let Business Objects–the BI tools industry leader–operate autonomously"
The article classifies this commitment as a strength of SAP.
CR Server XI for $2,495 (CR Server 2008 coming?):
Business Objects is running a promotion
for their entry level web portal, Crystal Reports Server XI. Crystal
Reports Server doesn't allow you to create reports but allows you to
make reports available on a network server to users who log in through
a browser. The promo price is $2,495 for five named users. The normal
retail version is still $7,500 for 5 concurrent. Considering the
change from concurrent to named users this doesn't seem like much of a
discount. I seem to remember 5 named users being a free offer at one
point.
But the real news isn't the promotion itself. It is that promotions
like this usually happen just before a significant product release. In
this case it is CR Server 2008 which is due in July (according to
my sources). Nothing will be officially announced until after the
promotion has expired.
On-Site classes just about anywhere:
If you want a Crystal Reports class at your
office, incorporating your own data, than give me a call. That is
my specialty. And I have 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.
So what makes my class different? Course materials written by an
expert who has done Crystal Reports full time for 12 years. I have
personally taught over 2,000 satisfied students. And every
student gets my toll-free number for questions after class.
My Intro class makes sure you know all of the basics, including
cross-tabs, charts and formulas. The Advanced class shows you how
to solve several common reporting problems and when to use advanced
features like running totals, subreports, parameters and commands.
Course outlines are on my web site. For more details call me at
(540) 338-0194.
Displaying chosen parameter values with their descriptions:
When you create a parameter field in Crystal
Reports you can create a "pick list" of values so that the user can
select a value rather than typing in the value. If your pick list is a
series of codes you can also add a second column of values to provide a
description for each code. So your pick list might look like this:
A - Adjustments
C - Credits
D - Debits
The challenge comes when you want to display the user-selected values
on the report. A single-value parameter can be displayed by placing it
on the page header. A multi-value parameter needs formula #7 on my web
site's formulas page. But in both of these cases only the user-selected
codes will be displayed, not their corresponding descriptions. Crystal
doesn't provide a way to display the descriptions, except within the
parameter window. But there are several workarounds:
1) If the values are available as a column of data in the report or in
one of the report's tables you could construct a cross-tab to list the
codes and values in the report. The code could be the row field while
the description could be the summarized field. You could even turn off
all of the grid lines in the cross-tab so that it looks like a list.
The only downside of this simple method is that the list will display
the chosen values only if they also appear in the report. So in the
example above, if you had picked A, C and D but there were no
Adjustments that month, the cross-tab would only show:
C Credits
D Debits
2) Another option is a subreport. You can use this if the codes and
descriptions are available in a table or command. You could pass the
original parameter to a parameter in a linked Subreport and create the
list of codes and descriptions inside that subreport. If this is a
dynamic parameter you could use the table or command that the parameter
is using. If this is a static parameter and you don't have a table that
contains those values you could always create a table by exporting the
static pick list to a file and importing that into a database (even
Excel or Access would work) and then creating a linked subreport to
read list from the new table.
3) If the values can NOT come from a table or command you will have to
duplicate the literals of the parameter in a formula. For a short list
you could do something like this:
(If "A" in {?parm} then "A - Adjustment" & CHR (13)) &
(If "D" in {?parm} then "D - Debit" & CHR (13)) &
(If "C" in {?parm} then "C - Credit" & CHR (13))
For a long list you might consider creating 2 arrays (codes and
descriptions) and create a For Loop to see which are used and then
append them into the display string, but that would be my last resort.
Moving/sizing objects with Shift-Arrow and Ctrl-Arrow (and crashing CR)
There are three ways to move/size individual objects in Crystal Reports.
1) Dragging with the mouse. To move an object you drag the object by
its middle. To size the object you drag the sizing handles on the sides
or corners.
2) Using the "Size and Position" property available through the
right-click menu. You can enter coordinates for the position of the
upper left corner as well as the height and width.
3) This one is lesser known. You can click on an object and move it by
holding down the CTRL key and clicking one of the four arrow keys. What
you are moving is the upper left corner, but since the size of the
object isn't changing you are really moving the whole object. To size
the object you hold the SHIFT key instead and use the same four arrows.
This moves the lower right corner of the object without moving the
upper left corner, thus changing the size.
But there are two limitations to be aware of when using technique #3:
1) If the object's right side or bottom touches a guideline it will
'attach' itself. Then the SHIFT-Arrow will no longer change the
object's size. You can, however, still move the object with the
CTRL-Arrow.
2) If you select more than one object, AND you are in preview mode, AND
you hold down the arrow key to move the objects continuously, then
Crystal Reports will almost always crash. Thanks to Gordon Portanier of
Crystalize in Canada for pointing this latest behavior out.
Conditional Page Footer
Crystal will allow you to conditionally suppress
a Page Footer. But even when the section is suppressed, the space it
would have taken up is still there, reserved as blank space at the
bottom of the page. This is because other features like "can grow' and
"keep group together" make the job of determining the page size very
complex.
There is a related option for page footers called "reserve minimum
space" but this only applies if you have multiple page footers and are
conditionally choosing between them. Activating this feature will
reserve space for the largest of the sections, only. It works best if
you want to choose one of several page footers. But if a page ever
exposes two sections, and if they combine to form a Page Footer that is
larger than what is reserved, you will end up with a Page Footer that
runs off the page.
There is a (complex) workaround but it only works if you are NOT using
"keep group together" or "can grow". You create a second detail section
and set it to "print at bottom of page". This will create a pseudo Page
Footer. Then you suppress that section on most records and only
display it on the Detail record that would print last on each page.
This is the tricky part - calculating the position on the page. It
requires a running total based on a variable. You need a formula in
every printing section (other than page header and footer) that adds
the height of that section to the running total variable. You then
suppress the section until the variable reaches the correct page
length. The suppress condition would be something like this:
WhilePrintingRecords;
NumberVar Position < 7.6 //the page height minus PH and PF
or PageNumber > 5 // your original condition for the page footer
The first condition makes sure it is only going to print at the bottom
of any page. You will have to experiment with the number to get it to
work. The second condition makes sure it only prints on the pages that
meet your original condition. And, because this section is not a true
Page Footer it will not leave empty space when it is suppressed.
Expert's Guide to Cross-tabs, and an update for CR 2008 features
My students already know that cross-tabs are my
favorite objects and yet they are often overlooked by Crystal users. In
CR 2008 they are even more powerful. I have explained them to thousands
of students and my exercises have been refined through 12 years of
teaching Crystal. Put that experience to work for you with, "The Expert's Guide to Cross-tabs".
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.
One of the big improvements to CR 2008 (Version 12) is the ability to
add calculated rows and columns within Cross-tabs. While it is simple
to do at first, it can be very difficult to truly understand. I have
spent hours experimenting with this new feature and have written up a
draft addendum to my Expert's Guide to Cross-tabs. This will be free to
anyone who has purchased the Cross-tabs guide and will be completed by
the end of May. The draft is available early if a few of you want
to proofread the draft.
Other Blog topics since the last newsletter:
Actuate Migration (written by Actuate)
Adding the Euro symbol to reports
SAP retiring overlapping products
"Free" vendor compliance management
CR 2008 Service Pack 0 breaks Xbase Connector
Field names containing dashes add nodes to the Field Tree
Prevent Crystal from requesting an Internet connection at startup
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