The Crystal Reports Underground News - Volume 2008.01 (January 2008)
an independent source for Crystal Reports Information by Ken Hamady
Contents for January 2008:
** NEW - The Expert's Guide to SQL Expressions, Options and Commands
** Things you wont find in Crystal Reports 2008:
RDC (COM) integration
Sample MDB
VB UFL functions - maybe
** BO price increases
** Open enrollment classes January 2008
** The index that ate my data
** Getting vertical lines to stop at the bottom of a group
** Suppressing vertical lines based on a condition
** Read back issues at http://www.kenhamady.com/news.html
NEW - The Expert's Guide to SQL Expressions, Options and Commands
Use these SQL tools to improve performance, solve data problems and add capabilities to your queries. This new guide covers all versions from 8.5 through 12. It shows when and how you can change the report generated SQL, and also how to use your own SQL as the basis of a report.
In the section on SQL expressions I show 16 different SQL calculations that you can pass directly to the database. These include string manipulations, boolean tests, data type conversions and date calculations. Many of these demonstrate useful functions that are not listed in the SQL expression editor. I even explain the rules for writing subqueries within a Crystal SQL Expression. And each of these expression examples is shown in 4 different SQL flavors (SQL Server, Oracle, MS Access and MySQL).
There are over 40 pages of material plus sample reports. The cost is only $26 for an immediate download.
Things you wont find in Crystal Reports 2008
Below are 3 things that you don't get when you upgrade to Crystal Reports 2008. For more of what is missing in CR 2008 see the very bottom of the "What's New" (PDF) document.
RDC (COM) integration
If you are planning on deploying Crystal Reports within a COM application then you should NOT upgrade to Crystal Reports 2008. The Report Design Component (RDC) is no longer supported starting with CR v12 (or 2008). The .NET SDK documentation and Merge Modules/MSI files are still available but they aren't on the CD. They are now a separate download on the CR start page.
Sample MDB
Crystal Reports 12 no longer includes the sample MDB that has been provided to users ever since I started working with Crystal 12 year ago. You still have sample reports but they all read XML data that is stored on the BO web site. You can download the XML data but I still prefer the MS Access format because it can be made to behave just like the normal SQL based data that everyone uses. It is what is used in most of the BO Knowledgebase examples and in all of my books. Fortunately you can still download a copy of the MDB. See my BLOG for details.
VB UFL functions - maybe
I just noticed this on Friday and it may be a bug. CR 2008 fails to read previously available Custom UFL Functions. I have tried this on two PC's. In CRv12 the node for Visual Basic user functions (u2lcom.dll) doesn't show up in the function tree under "Additional Functions". However those functions do appear on that same PC in all previous versions of Crystal.
If you get a different behavior please let me know. In the meantime, if your reports rely heavily on UFL functions, you may want to confirm that your functions work in CR2008 before you lock in on the upgrade.
BO Price Increase (everything but Crystal Reports
According to a recent press release, Business Objects is planning a 10% price increase for most of their product line as of January 2008. It doesn't affect Crystal Reports (and Xcelsius) but does affect most of the other products and web based training. They are also eliminating 'standard' support. The lowest level of support will be 'corporate'. I have read speculation that this is related to the SAP acquisition.
Open enrollment classes January 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 January 22-25. 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.
The index that ate my data
Indexes in the database normally improve the performance of a report. But I have also seen indexes cause a report to misbehave.
I recently ran into a simple report listing account numbers. With no criteria account "X" displayed 2 records. But when we added a selection criteria to select account "X", only one of the records remained. The account numbers were identical and there were no other report features that would affect the number or records returned. When we added the criteria and used saved data both records remained, but when it was refreshed there was only one.
That told me the database wasn't doing the right query and I knew where to look first. When some databases get a criteria from Crystal they try to optimize the filtering by using an index. But I have found that on rare occasions that the index selected by the database engine won't include all records. In these cases I find a Crystal function I can add into the selection formula that doesn't change the result of the criteria. What it does do is prevent the rule from being passed to the database. In most cases this makes the selection process less efficient, but in some cases this is the only way to get the correct data.
When the user saw that the problem was the index he said he would change the index and sure enough, when he changed the index from descending to ascending, the original report worked correctly. This report read Visual Dbase through ODBC and I have seen this same behavior several times in PeachTree Accounting (Btrieve). So, if you are ever run into this behavior you now know one more thing to check.
Getting vertical lines to stop at the bottom of a group
Many people want to have vertical lines that run through all of the detail records in a report and so they draw lines that cross over the details, going from header to footer. But a problem arises when the details carry over a page break. Lines that cross over the Details section may extend below the last Detail of the page, down to the page footer. This is especially likely to happen when you have a deep Details section. This is because a deep section may not fit at the bottom of the page, leaving a large white space. Lines that cross over the Details section will continue through this space.
The alternative approach is to have the lines contained completely within the Details section. This way the lines will appear only where the details sections appear, and not between them. The tricky part is making one short line within a single Detail section seem like a continuous line. There are three steps to make this happen.
1) In design mode, draw a short vertical line that is completely within the details section.
2) Right-click on the line and select "Size and position". Set the "Y" value to zero. This ensures that the top of the line touches the top of the section.
3) Right-click on the line again and select "Format > Line". Check the property that says "Extend to bottom of section…". This ensures that the bottom of the line touches the bottom of the section.
Now each line will touch the line above and the line below, giving the appearance of one continuous line.
Suppressing vertical lines based on a condition
One of my customers asked if there was a way to do this. There isn't a way to do this directly, but I played around and found a way to control the visibility of vertical lines, horizontal lines and boxes. Below are the steps for controlling all lines and boxes located between the Page Header and Page Footer. The same method can be adapted for other locations.
1) Create a new Page Header below the ones you have and set it to "Underlay". Make it just big enough to reach the top of the page footer n preview, but be careful not to make it too big or it will generate errors and crash crystal.
2) Place a large empty text object into this section and set its background color to white. The text object should be deep enough to fill the new page header and wide enough to cover the lines and boxes that you want to control.
3) Place the opposite of your suppress condition in the suppress formula of this new section. When the new PH is suppressed the lines and boxes will be visible. When it is NOT suppressed it will cover the lines and boxes, making them invisible.
The funny thing is that this text object won't hide any fields, pictures or other text - just lines and boxes. But these other objects can be suppressed with their own formulas. Lines and boxes don't have their own suppression formula buttons so this workaround will help.
If you have trouble managing the lines and boxes on a page, don't forget that I can be 'rented' for as little as 15 minutes to help out. And if you like tips and tricks like this, don't forget to check out my Expert Techniques materials.
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