The Crystal Reports Underground News - July 2002
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for July:
** Update of my VB Quick Reference Guide
** Crystal and Actuate settle lawsuit
** Why can't I total this formula?
** Expert on-site training or consulting
** Reading an external logo file at runtime
** Mysterious shifting objects
** Public classes in Leesburg, Baltimore and Philadelphia
** Taking the owner out of a SQL server database connection
** v8 Hot fix affects compiled reports
** Read back issues at http://www.kenhamady.com/news.html
Update of my VB Quick Reference Guide
I have just updated my Quick Reference Guide for using Crystal Reports with Visual Basic. It now covers the basics of exporting and working with subreports from VB code. I have also added some notes that explain common problems with formulas, parameters, deployment, and a section on additional resources.
If you are new to using Crystal Reports within VB, this guide will shorten your learning curve significantly. It gives an overview of the integration methods, with syntax examples for the most important commands. It comes with source code examples, including a report viewer program.
The guide is only $16. It can be Emailed to you immediately if you pay online. Follow the link below for more details:
http://www.kenhamady.com/vbref.html
Crystal and Actuate settle lawsuit
Crystal Decisions and Actuate have quietly settled their "false advertising' lawsuit. Both complaints were dismissed as part of an undisclosed settlement. Neither side is saying much about the terms, other than that it was to the "mutual satisfaction of both parties". However, I find it telling that the 'Competitive Guide' PDF that used to be available on the Actuate web site has been taken down. This document compares features of Crystal to features of Actuate. It may have contained some of the claims that Crystal was suing over in the original complaint. The press releases are below.
Crystal Decisions:
http://www.crystaldecisions.com/about/press/releases/2002/070101.asp
Actuate
http://www.wsrn.com/apps/news/art.xpl?id=4706450&f=NEWS&s=ACTU
Why can't I total this formula?
One of the most frequently asked questions is why some formulas that print on the report can't be totaled. There are several reasons why this would occur and they all have to do with the steps that CR goes through to build the report. When teaching this concept, I find it helpful to describe a "hump" in the middle of report processing. This hump is where the Grouping, Totaling and Sorting occur. Some formulas can be calculated before the hump, while others must be calculated after the hump.
For instance, if you write a formula that uses a subtotal or grand total, then this formula will have to wait until after the hump, or else it won't have the totals to use. On the other hand, if you write a formula that you want to use as a group field, then this formula has to be calculated before the hump - so that it is ready when the grouping begins. Once you have crossed the hump, you can't go back.
So what happens if you are trying to total a formula, and that formula uses another total? Because there is a total in the formula it has to wait till after the hump. But, then the formula itself can't be totaled, because once the hump is passed you can't go back and do more totals. Since you can only go over the hump once, the report gives you an error when you try to to total this formula. To total a formula like this you must write a running total using variables.
In technical terms formulas formulas before and after the hump would be described as "first pass" and "second pass" formulas. First pass formulas are those that use only standard functions, and that can be calculated before the hump. Most formulas are first pass formulas, and can be used for totaling and most other CR features.
A formula only has to wait for the second pass if it:
1) Uses a summary function like Sum() or Maximum()
2) Uses the functions Next() or Previous()
3) Uses a variables that is shared with subreports
4) Starts with the function WhilePrintingRecords
5) Uses another formula that includes one of these 4 features
There are several restrictions on what you can do with second pass formulas. As you already know, you can't use them with total functions, which also means that you can't use them in Cross-tabs or Charts. They are also ineligible for grouping, sorting, or being put into the record selection formula.
Expert On-Site Training or Consulting:
I have personally taught over 1300 satisfied students in more than 30 states. Training on location is my specialty, and I charge about half of the "List Price" for Crystal Training.
Do I know my stuff? Check out the Crystal Reports Forum at Tek-Tips.com and you will find me listed as the top Crystal Reports expert out of over 23,000 members:
http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/100/pid/149
Can I teach? Many students have said that my classes are among the best technical classes they have taken. Ask to speak to a recent customer as a reference. Call for more information at (540) 338-0194.
Reading an external BMP file at runtime
When you add a logo to a report, Crystal will typically store the image within the RPT file. This means that you can move the RPT and you don't need to worry about the image file, because the image is part of the RPT. However, sometimes you need to reference an external image file, so that the image file can be updated before the report is run. For instance, maybe you have a standard invoice report and each user wants to use their own image or logo. You could create a separate report for each user, or you could insert the image as an OLE 'Link' rather than the image itself. This only works if you are using a Bitmap (BMP) file. Here are the steps:
1) Use the menu options Insert->OLE object (instead of Insert->Picture)
2) On the left select 'create from file'
3) Below the file name you check the 'Link' box
4) Browse to your BMP file and select it
5) Place the image on the report
Now, each time the report is run it will whatever BMP is stored in that path. If you replace the BMP with another file (using the same name), Crystal will put that image on the report instead of the original. If each user names their image file correctly and puts it into the correct folder, this image will automatically appear on the report.
Mysterious shifting objects
I recently read a Crystal KB article that made me chuckle. It seems that when you change the 'object size and position' property of an object, CR will often reduce the numbers by .01 without warning. So if you specifically set the 'X' value to 0.68, CR will make it 0.67 when you click 'OK'. And if you open to check the number and click 'OK' again, CR will make it 0.66, and so on. The solution in the article? Always set the value to be .01 higher than you need.
The article only mentions this behaviour on the "height" property, but I found the same behaviour on all 4 properties under certain circumstances. The numbers tend to stop this behavior when they reach .x0 values, like .70 or .40 and sometimes when they reached a .x5 value. I will guess there is some internal rounding to blame. Read the official line at:
http://support.crystaldecisions.com/library/kbase/articles/c2011056.asp
Don't just take a class, learn from a Crystal Expert:
Click the "Public Classes" link above for dates.
Each class is $675 per student.
Or, call for more information at (540) 338-0194.
Taking the owner out of a SQL server database connection
I have recently read about application developers who get errors when they try to change the database used at runtime. It seems that the even when the ODBC/OLEDB connection has been pointed at another database, or when the SetLogonInfo command has been used, the report still looks for the original database. This might be because the database "owner" is stored in the report as part of the table name. The solution is to remove everything but the table name using "Database->SetLocation" menu commands. The full KB article on this topic is below:
http://support.crystaldecisions.com/library/kbase/articles/c2011321.asp
v8 Hot fix affects compiled reports
Several of my newsletters have dealt with the 'compile' feature of Crystal that allows users to run reports without owning a license to the Crystal Reports designer. See the back issues of my newsletter for more info on compiling reports. However, if you are using Crystal v8, and you applied the 'HotFix' file called dbexdrvr.zip, you may have disabled the compile feature. Apparently one of the files included in this 'HotFix' is not compatible with the compile feature, and you have to replace it with the original file. Instructions on how to do this are in the article below:
http://support.crystaldecisions.com/library/kbase/articles/c2011417.asp
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 2002 by Ken Hamady
All rights reserved - Republishing this material requires written permission