The Crystal Reports Underground News - Volume 2004.06 (June 2004)
an independent source for Crystal Reports Information by Ken Hamady
Contents for June, 2004:
** Security Flaw in Crystal Enterprise
** SQL Server Native Client and syntax changes
** Other reasons to keep a copy v8.5 around:
** Public Intro and Advanced classes in Frederick
** My Library of Crystal Reports Materials:
Expert's Guide to Formulas
Expert Techniques Volumes I and II
Quick Reference to Crystal Reports in Visual Basic
Quick Reference to Crystal Reports in VB.NET
** Parameter for ALL including Nulls (Improved)
** Export to PDF and color code the PDF Bookmarks
** Changing a command causes CRv10 to shut down
** PrintDate vs DataDate (and another bug in v10)
** Read back issues at http://www.kenhamady.com/news.html
Security Flaw in Crystal Enterprise:
This past month a security flaw was discovered in Crystal's web products that could allow a hacker to gain access to your web server. It exploits a flaw in Crystal Enterprise or Crystal Reports' other web engines. There is a patch available on the BO web site. To read more about this issue go to:
http://support.businessobjects.com/fix/hot/critical/default.asp
SQL Server Native Client and syntax changes:
In a recent newsletter I mentioned that Crystal Reports v9 and v10 do not support the SQL Server native client. This week I learned that there should be a caveat in that statement. CRv9 and CRv10 will not allow you to CREATE a new report using a native connection. However, if you have an old report that connects using the native client, this report can be opened, modified and run in either CRv9 or CRv10.
This might give some of you a reason to hold onto one copy of version 8.5. With v8.5 you can create SQL Server native client reports and run them in v9 and v10. (See below for a couple of other reasons you might want to hold onto the older version of v8.5.)
Note that while the report will run in later versions, you may find that it gives you different results. This is because CRv8.5, when connected to native SQL Server, uses the proprietary "start equal" (*=) SQL Syntax for Left Outer Joins. The same report, when run in the later versions of CR will use the ANSI standard syntax for Left Outer Joins. This is an important difference and can have a dramatic effect on the number of records returned by the report.
When using the older proprietary syntax, the records are filtered in each table separately and then the filtered tables are joined together. With the new ANSI standard syntax, the join is done first, and then the filter is applied to the composite table. Here is an example to illustrate the difference:
Say you have a table of Customers and a table of Orders. You want a list of all customers and for each customer you want to show their March Orders (if any). You use a Left Outer Join from Customer to Orders. Under the old syntax the filter would not touch the customer table because it would be applied to the Orders table before the join. The Left Outer Join would be between the entire customer table and the filtered orders table. All customers will show in the report, even those with no orders or with only orders in other months.
Using the new syntax, the join would first link all customers to all of their orders. Then the filter would applied to the composite table. Now only customers who have March orders will show up in the report. Two groups of customers are lost: Those who have orders but none in May, and those with no orders at all. In other words you have cancelled the effect of the outer join.
Other reasons to keep a copy v8.5 around:
In version v8.5 you could let Crystal generate the SQL for you automatically, and then you could tweak the SQL in the "Show SQL Query" window. Some users have gotten used to making minor adjustments in this window to optimize the SQL that Crystal has generated. You can change anything in this "Show SQL" window except the SELECT clause.
In versions 9 and 10 you can go further and take any SQL statement and make it the basis for a report. Crystal calls the SQL that you use to start a report a COMMAND. This is an extraordinary feature to many users. However it causes a challenge to a few users, because you can no longer make ANY changes to the SQL in a normal report. So, if you need a report with any custom SQL, it has to be created from scratch using a command. And if you want to add a new field to this report, or add a new table, you can no longer do this within the program menus. You have to rewrite the SQL first. One of my customers keeps a copy of v8.5 around so that users can both tweak the SQL and still maintain the report using the menus. These reports can be run in v9 and v10.
Another of my customers connects to Dbase and Access using the "Database Files" connection options instead of ODBC. This allows them to use a handy join option called "All of one, all of the other" which allows you to deal with multiple one-to-many relationships. This Join option was removed from v9 and v10. However if you use this type of join in an older report you can still run the report in v9 or v10. Not only does CR maintain the old join type, but if you go into the join options you will now see that option listed. Of course, it only appears within reports that came from older versions and that used this type of join.
Public Intro and Advanced classes in Frederick:
My specialty is teaching at your office, with your data, but not everyone has the 3 or 4 students to make this cost effective. If you want to take one of my classes and don't have enough people for an on-site class, come to Frederick, MD and take my public class in early July. The classes are always small and informal with lots of room for discussion. For details see:
http://www.kenhamady.com/public.html
Is it worth the trip? I have personally taught over 1750 satisfied students. And, I am still the all-time, top ranked Crystal Reports expert at Tek-Tips.com. For more information, or to schedule an on-site class, call me at (540) 338-0194.
My Library of Crystal Reports Materials:
Expert's Guide to Formulas ($36)
http://www.kenhamady.com/form00.html
Expert Techniques Vol. I ($19) and Vol. II ($19)
http://www.kenhamady.com/expert.html
Quick Reference to Crystal Reports in Visual Basic ($16)
http://www.kenhamady.com/vbref.html
Quick Reference to Crystal Reports in .NET ($14)
http://www.kenhamady.com/ntref.html
Parameter for ALL including Nulls:
Last month I described a selection formula that allows a parameter to have an option for ALL. I described how the order of the conditions would effect the WHERE clause in the SQL and therefore affect the performance of the report. However, some of my readers at Epic Systems Corporation in Madison, WI pointed out that "ALL" in a parameter can be interpreted 2 different ways: All records that have a value entered in that field or All records, including NULL values. The example that I showed last month would work well when you know there are no NULL values, or when you don't want them included.
They shared a better way to allow "ALL" in a multiple string parameter. It allows the ALL to include NULL values while still allowing specific values to be passed to the SQL. They use:
(not ( isnull ( {Table.Field} ) ) and {Table.Field} in {?Parameter} )
or "ALL" in {?Parameter}
The Not(IsNull()) clause prevents the formula from trying to process null values at the end of the first line. This would cause the formula to skip all NULL values. A NULL record now skips to the second clause and is given the green light to be included in the report.
Export to PDF and color code the PDF Bookmarks (Visual Cut):
It seems like just about every month Ido Millet adds a fun feature to one of his products. This month he has been digging into the export interface for creating Adobe PDF files with Visual Cut. He has found a way to go beyond making the report's group tree into the bookmarks tree in the PDF. He has now found a way to have the PDF bookmarks change color based on conditions in the data. He sent me a sample PDF and I can see how it would be handy to flag certain groups in the PDF by having color coded bookmarks. See the LINKS page of my site for more information on Visual Cut, or contact me if you would like to request a free evaluation copy.
Changing a command causes CRv10 to shut down:
If you have a CRv10 report based on a SQL command with a parameter, and you change the SQL of that command, you may have trouble saving the report. When you try to save the report Crystal will just shut down. Fortunately this has been fixed in one of the HotFixes on the BO web site.
http://support.businessobjects.com/library/kbase/articles/c2015556.asp
If you still have v9 you can open the report in v9, make the change, save the report and then reopen the changed report in v10.
PrintDate vs DataDate (and another bug in v10):
If you have taken my Crystal class you are familiar with the Date Range functions like YearToDate or LastFullMonth. These can be used in formula fields or in the select expert to have the report automatically calculate date ranges based on today's date. Crystal even has a feature that allows you to override the current date for your reports without actually changing the system date of your PC. This option is found near the bottom of the "Report" menu and is called "Set Print Date". Changing this date affects all of the calculations that are based on the current date, like those mentioned above. This allows you to have your calculations based on an "As Of" date that you can select.
One of the things to watch out for is that adjusting the date changes the "PrintDate" field that prints in the page header. This makes it difficult to see when the report was actually run. In most cases you can get today's date by adding the special field called "DataDate" to the page header. The DataDate is the date that the data was queried from the database. The DataDate is not affected by the "Set Print Date" option mentioned above.
Well at least it isn't supposed to be. I discovered another minor bug in v10 this passed week. If you use the DataDate on the report it will show today's date as expected at first. However, the next time you refresh the report the DataDate will change to match the PrintDate. This odd behavior only appears in CRv10.
Removal instructions:
I have sent you my newsletter because you are a former client, or because you have contacted me regarding Crystal Reports. If I am mistaken, or if you don't wish to receive the newsletter, please reply to this message with the word 'unsubscribe' in the header. I do not share these Email addresses with anyone else.
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 2004 by Ken Hamady
All rights reserved - Republishing this material requires written permission