The Crystal Reports Underground News - Volume 2004.11 (November 2004)
an independent source for Crystal Reports Information by Ken Hamady
Contents for November, 2004:
** Class Action Suit Against Business Objects
** The end of concurrent licenses?
** Security issue with JPEG files
** Public Intro and Advanced classes in Frederick
** Reportal v3
** 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
** SQL Expressions that do Correlated Subqueries
** When using a Command can Slow Things Down
** Read back issues at http://www.kenhamady.com/news.html
Class Action Suit Against Business Objects:
Some of you may already know this, but I just found out. Some Business Objects shareholders have filed a class action lawsuit. They are claiming that BO management propped up the stock price by making misleading statements regarding the recent acquisition of Crystal Decisions. According to the text of the suit, executives are accused of hiding the fact that the "acquisition was a disaster". When April profits were disappointing, the stock price dropped about 20 percent. The lawsuit specifically mentions the loss of market share to Microsoft and Cognos. This is blamed on the customer confusion over how the 2 product lines would be integrated. To read more about the case, follow the following link:
http://securities.stanford.edu/1030/BOBJ04-01/
The End of Concurrent licenses?
The lawsuit above is public knowledge but this item is an undocumented rumor given to me by one of my readers. He was told by his Business Objects sales rep that the next version of Crystal Enterprise would not have pricing for concurrent licensing. The license would either be named users or a processor license. I only mention this because some of you are probably planning long term deployment strategies around the existing license model. If so, you might want to clarify this with your BO representative. If anyone gets written confirmation on this, one way or another, please drop me a line.
Security issue with JPEG files:
This article was just published 2 weeks ago. Apparently Crystal Reports v9/10 and Crystal Enterprise v9/10 have a security vulnerability that could allow a hacker to take over your computer. The vulnerability relates to JPEG files and how they are processed by gdiplus.dll, which is provided by Microsoft. The Crystal products mentioned above contain a Crystal specific version of this file, separate from the one used by Windows. This means that the Microsoft Security Update that was released to fix this vulnerability does NOT fix the Crystal vulnerability. This DLL is vulnerable to being attacked with a 'buffer overrun' and could allow a remote user to take control of the vulnerable PC.
While Crystal is not usually used to view random JPEG files (like your browser or your Email client would be) this is probably still something you should fix. To read more about this problem, and the fix, use the following link:
http://support.businessobjects.com/library/kbase/articles/c2016358.asp
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. 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 1875 satisfied students, including students at the National Institutes of Health, the Library of Congress and the Executive Office of the President. I am also the all-time, top ranked Crystal Reports expert at Tek-Tips.com. For more information on public or on-site classes you can call me at (540) 338-0194.
rePORTAL v3:
Shahrabani & Associates has just released version 3 of rePORTAL, their web based document management program. Those of you considering a Crystal Enterprise deployment might want to evaluate rePORTAL as a lower cost option. Reportal runs alongside the Crystal Web Component Server or an entry level edition of Crystal Enterprise (like RAS or Embedded). In addition to viewing reports it provides additional features like:
Queuing technology to maximize concurrent licensing
Report-level or row-level security
Parameter filters that are specific to a user or group
Scheduling, bursting and Emailng reports to multiple users
Reportal is licensed per server and currently costs $2,800. If you choose to purchase annual maintenance add an additional $600. I have been told that these prices are scheduled to increase in 2005. To learn more about rePORTAL you can visit the LINKs page on my web site and look under "Web Related Products".
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
SQL Expressions that do Correlated Subqueries:
(To learn more about using complex SQL Expressions, and how they work in later versions of Crystal Reports, you can get my:
Expert's Guide to SQL Expressions, Options and Commands. )
About a year ago I wrote an article about creating a SQL expression that did a SELECT. I didn't really delve into how they worked. All I knew then was that a SQL expression became a column in the data set.
What I learned about a month ago was that a SQL expression's WHERE clause could reference a field from the primary SELECT, creating a correlated subquery. This means that the SQL Expression can return a different value for each record, something like a linked subreport. The advantage of doing it in a SQL Expression is that they happen before the report reads the data, which makes them available for Record Selection, Sorting, Grouping and Totalling. I would never have thought to try this so a big "Thank You" goes out to LBass, one of the regular expert contributors at Tek-Tips.com.
Say that you have customers who place orders. You want to categorize each customer into one of 5 levels, based on the average order size of that customer. You then want to do a chart that shows how many customers are in each level. In Crystal it is easy to calculate the average order size for each customer. It is also easy to rank individual customers based on their average by using the TopN/Group Sorting feature. However, you can't create a group based on a summary field like an average, so counting the members in each level and using that in a chart becomes very difficult.
This becomes much easier with a SQL Expression. You ask the database to look at the customer ID in each order, and run a query that calculates the average order size for all of the records for that customer. An example SQL Expression for MS Access in Crystal Reports v8.5 might look like this:
(SELECT Avg(Ken.`order Amount`)
FROM Orders Ken
WHERE Ken.`Customer ID` = Orders.`Customer ID`)
Note that I used my name as a table alias for the Orders table. This is essential when you are using a table that has already been used in the report. Also note that the SQL Expression has parentheses around it. This turns it into a subquery. The SQL expression will show up as part of the SELECT clause in the main SQL statement of the report. To get a value for this new column the database will have to:
Read the Customer ID of each record
Select all of the orders that match this ID from the orders table (as Ken)
Average the amounts of all of those records
The database will place the calculated average on the end of every order record in your dataset. Because this is a SQL Expression, rather than a formula, this all happens within the database - before Crystal has read any of the records, which is why I can use this value to create a group in the main report. This group can then be used to create the required chart.
Remember to use an alias for any table names in the SQL Expression, especially if that table has already been used in the report. In most cases the WHERE clause in the SQL Expression will reference one field from the main SQL and another field from the alias table. Only one of these will use the alias table name. See the last line of the expression above for an example.
One of the downsides of working with SQL Expressions is that every database has its own flavor of SQL syntax and punctuation. What works in Microsoft Access (my example above) might have to be adjusted for SQL Server or Oracle.
Another downside is that you can't reference parameter fields in a SQL expression, so if I wanted the average amount to be calculated based on the date range of the report, I would have to hard code the dates in the SQL Expression. Parameter fields are not currently available when creating SQL Expressions.
When using a Command can Slow Things Down:
Those of you using v9 or v10 might not think the above is very useful since you can create commands and do all of this directly in the SQL. For those of you still on v8.5, a Command is a SQL statement that can be created from scratch and used as the basis of a report. It was introduced with v9 and replaces the SQL Designer available in v8.5. Usually commands provide more SQL flexibility, but I have found cases where commands can cost you some performance.
Say that you allow your users to select the date range and several customers for a report. You give them two parameter fields. One is a range parameter for the dates and the other is a multiple parameter for the customer. If your report connects directly to the tables, Crystal will generate the SQL statement AFTER you fill in the parameter fields. The WHERE clause will include the date range and the specified customers.
However, if you convert this report to a command you might try using command parameters instead of the Crystal parameters. What you would find is that command parameters can only accept a single value. You don't get the ability to pass range or multiple parameters to a command. The date range can be done in the command by using 2 parameters - Start Date and End Date. However there is no easy way to make a command accept a multiple value parameter. Your only choice is to use a Crystal Parameter that is set to accept multiple customers. That is where you lose performance, because the selected customers will not be reflected in the SQL. In a command based report, only command parameters are processed by the database. The database will have to return ALL customers to the report. It will be up to Crystal to filter the customers locally on the client PC. So in this case the report using a command would perform slower than a regular report.
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