The Crystal Reports Underground News - Volume 2006.07 (July 2006)
an independent source for Crystal Reports Information by Ken Hamady
Contents for July, 2006:
** NEW - The Expert's Guide to Cross-Tabs
** Depositions start this week in the BO commissions lawsuit
** BO loses SAP as on OEM Reseller
** Associate Trainers for NYC, Philly, Chicago, L.A. and Toronto:
** BO starts to look at 'mid-size' business.
** BO (finally) announces plans for Google OneBox partnership
** My Library of Crystal Reports Guides
** Build a Data Dictionary, automatically from your reports
** Printing "Continued on Next Page"
** Date parameters in a command with DateTime Fields
** One parameter for 2 different commands
The Expert's Guide to Cross-Tabs:
Every student who takes my Intro class learns that Cross-Tabs are my favorite Objects. They make it easy to recap a report's data in several different ways. As soon as my students see how easy they are to use they start thinking up places to put them in their own reports. And yet most Crystal users never really bother to figure out what cross-tabs can do. I recently suggested a cross-tab to a user who was trying to use arrays and got the following reply:
..15 mins this AM to get my crosstab doing what I needed
... I must have spent more like 30 hrs agonizing over how to get it done.
Now you can tap into 10 years of Cross-Tab experience with my new book, The Expert’s Guide to Cross-Tabs in Crystal Reports. The material includes a detailed description of each feature, example reports that illustrate the techniques, and exercises that walk you through each step. The exercises are the key. They have been written to provide practical examples that are easy to understand and follow. Also included are several relevant articles borrowed from my Expert Techniques for Crystal Reports Vol. I and II.
The material is condensed into 20 no-nonsense pages and is backed up with 20 sample reports. Exercises provide keystroke by keystroke instructions, showing you how to implement each of the discussed features. For more information, see the LIBRARY page of my website.
Depositions start this week in the BO commissions suit:
I asked Michael Ward of Creative Technology & Training Solutions in Michigan how the lawsuit was going. He is suing BO to collect outstanding commissions from deals they made when they were a Business Objects partner. He said that the case is moving forward and that depositions start July 31. He also said that he has been contacted by investors all over the country concerned about this being the tip of the iceberg. Current and former BO partners are also calling to discuss their problems with BO and there is even some talk of turning this into a class-action suit.
Ward says that even his customers are getting pressured by BO. Some customers have been told that using a former partner like CTTS to provide Crystal training puts the customer at "high risk". I remember hearing a similar tale from one of my customers. Fortunately that customer knew that BO was simply trying to push their own training.
BO loses SAP as on OEM Reseller:
According to a story on CRN.COM.AU, Business Objects and SAP are not going to renew their existing OEM relationship which ends in September. SAP is going to replace the current Crystal components with some home grown functionality. BO has similar OEM agreements with IBM, Oracle and even Microsoft. BO has warned investors that if these deals also unravel it could have a significant effect on revenues. All three of these vendors have their own BI software in the works.
Associate Trainers for Boston, NYC, Philly, Chicago, L.A. and Toronto:
I have been putting together a team of experienced Crystal Reports instructors who are based in major cities throughout the US and Canada. These folks have excellent references and can provide a class at a significant savings. So far I have associates in and around Los Angeles, Minneapolis, Chicago, New York City, Philadelphia, Boston and Toronto. These instructors all use my materials and anyone who takes their class gets my Email address and phone number for questions after class.
If you still can't afford your own private class, come to my public classes in Frederick, MD. These are very small classes (usually 3-5 students) and people come from all over to attend. Click the PUBLIC button at the top of this page for more information or call me at (540) 338-0194.
BO starts to look at 'mid-size' business:
There was an article on CRN.com a few months back that had comments from several unhappy ex-partners. To make the article seem more balanced they found a current partner who was still happy with his relationship with BO. His comment was essentially "They take the big customers and we take the mid-size customers and everyone is happy."
That may not be the case anymore. I just read an article quoting the new CEO of Business Objects, John Schwarz. He says that he sees opportunities for growth in medium-sized businesses. Apparently the rate of growth at the Enterprise level is slowing and they are looking for new areas to expand. So now where does that leave the partners? You may soon see a whole new class of unhappy ex-partners. To read the new article go to the link below and look at the bottom category:
http://www.kenhamady.com/licensingissues.html
BO (finally) announces plans for Google OneBox partnership:
In my last newsletter I mentioned that BO was lagging behind the other BI vendors with regards to Google. It seemed that everyone was developing partner products to work with Google software and BO hadn't joined the party. Just after I wrote that article BO announced their Google partnership. They don't have a product ready yet but they expect it to be out later this year.
My complete Library of Crystal Reports Materials:
Let a master teacher help you understand these Crystal Topics. Each guide comes with clear explanations and sample files to illustrate each concept.
Expert's Guide to Formulas ($36)
Expert's Guide to Subreports, Parameters and Alerts ($28)
Expert's Guide to Totals ($24)
Expert's Guide to Cross-Tabs ($22)
Expert Techniques Vol. I ($19)
Expert Techniques Vol. II ($19)
Quick Reference to Crystal Reports in Visual Basic ($16)
Quick Reference to Crystal Reports in .NET ($14)
http:/www.kenhamady.com/tools.html
Build a Data Dictionary, automatically from your reports:
Barry Walter has just released a tool to help Crystal Reports developers organize and explore their reports. It automatically extracts and stores all of the database connections, tables, joins and even the formulas used in all of your reports. You can then select any connection, table or field to see which reports use that object. You can see and compare all of the joins that have been created with each field, and which SQL join types were used. In other words you can generate a data schema or dictionary from the information stored in your existing reports.
This tools is especially handy when you have to explore someone else’s reports. Just import a group of reports and see how the key tables fit together and what fields and formulas are used in each report. Other features include:
1) The ability to store
notes and comments for your tables and fields
2) The ability to view Stored Procedures and browse the data that they generate
3) The ability to view all of the information from several different angles.
The tools is called MyDataDictionary and you can try it free for 30 days. The price is normally $120 but my readers get a 10% discount by using my discount code - which is KH7259.
Printing "This Group Continued on next Page":You may find several other ways to do this, but this one works in almost all situations. It uses 3 formulas, and these can be cut and pasted into your report:
//This formula goes on the Group Header
WhilePrintingRecords;
BooleanVar Continued:= True
//This formula goes on the Group Footer
WhilePrintingRecords;
BooleanVar Continued:= False
//This formula goes on the Page Footer
WhilePrintingRecords;
BooleanVar Continued;
if Continued
then "Continued on next page"
else ""
Date parameters in a command with DateTime Fields:
There is always a risk when you use Date parameters to query DateTime fields. Because a Date parameter has no time value, the query may use 12:00am for both the start and end dates of the query. If all of your times are stored as 12:00am then this isn't a problem. But if your field has true time stamps, than any DateTime value on the last date in the range would be skipped over.
Crystal fixes this problem automatically when it generates the SQL for most reports. It automatically adds one day to the end date of the parameter range and then uses a "less than" operator in the SQL. This way it finds any value before that next day starts at 12:00am.
However, if you are using Command objects you are entering in your own SQL, so you have to make your own adjustment. Fortunately, this is easy to do. You put your command parameter into the WHERE clause of the SQL and add one day to it, changing the comparison operator to a "Less Than". The WHERE clause would look something like this:
WHERE ("TABLE"."DATE" >= {?Begin}
AND "TABLE"."DATE" < {?End} + 1 )
Note that the first "greater than" operator uses the equal sign but the second operator doesn't. Of course, if you created DateTime parameters instead of Date parameters this problem doesn't occur. It is just that most people prefer not to enter times.
One parameter for 2 different commands:
And speaking of commands. There was one thing I recently noticed that seems obvious now. When you add a command to a report it behaves like a table. This means that you can add 2 different commands and link them together as if they were 2 tables.
So far so good, but what if each command has a parameter? If you want both commands to share the same parameter value, all you do is create a parameter in the second command that has the exact same name as the parameter in the first command. If several commands all have the exact same parameter name, then Crystal treats them as one parameter and prompts you for one value. It then passes that value to all of the commands that use that parameter name.
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 2006 by Ken Hamady
All rights reserved - Republishing this material requires written permission