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