The Crystal Reports Underground News - Volume 2007.01 (January 2007)
an independent source for Crystal Reports Information by Ken Hamady
Contents for January 2007:
** Email reports and other documents by "printing" them
** Stop struggling with your reports, let me help
** BO cuts off support for an independent consulting firm
** I am now Ken Hamady, LLC
** Business Objects acquires Nsite Software
** My Library of Crystal Reports Guides
** Linking tables vs Appending tables
** Correction for Holidays formula
** Read back issues at http://www.kenhamady.com/news.html
Email reports and other documents by "printing" them:
I was recently shown a new utility that allows you to burst reports into sections and Email each section as a separate PDF attachment to a different Email address. The tool is called PDF-eXPLODE and it makes bursting and Emailing your reports as simple as printing them. It doesn't matter how you run the reports, as long as you can select a printer.
Say you have a report that prints 30 invoices and each invoice should be Emailed to a different person. All you do is put the Email Address at the top of each page (invisibly if you want), and then "print" the document to the PDF-eXPLODE virtual printer. This printer is installed automatically by the program. When you "print" to this printer it automatically bursts your report into separate PDFs for each Email Address and then sends out the PDFs as attachments to the messages. If one Email address shows up at the top of several pages (even non-consecutive pages) those pages will be combined into one PDF and sent. It even works with other document types like Word docs or MS Access reports.
Of course the best way to see what it does is to test it. You can download a free trial copy and try it for 30 days. The purchase price is $349 per user. See the link below for more information.
http://www.kenhamady.com/pdfx/learnmore.htm
Stop struggling with your reports, let me help:
You need information to run your business, so stop struggling with Crystal Reports and learn how to use it. 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, including cross-tabs, charts and even some 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. See my web site for dates and details or call me at (540) 338-0194.
So what makes my class different? How many instructors teach you from a book that they wrote? How many have taught more than 2,000 students? How many give you a toll-free number to call after class for questions? For more information see my web site or call me at (540) 338-0194.
And, if you want to schedule a class at your office, using your data, let me know. That is my specialty.
BO cuts off support for an independent consulting firm:
Some of you may be following the lawsuit filed by a former BO partner group in Michigan. The suit is an attempt to collect commissions on some large sales that they helped make. I have written about this in my BLOG and recently received the following update from Michael Ward of CTTS.
"Ken, here is the latest: BO has cut off my tech support IDs, claiming we have no valid license ... Clearly a tactic to harass us and put us out of business."
According to BO's counter suit, CTTS violated the terms of the EULA by providing consulting services and training, so their software licenses were revoked. Then it appears that this was used as the basis for canceling their support contracts. So, if you are a former partner or are trying to be an independent consultant you should be aware of this as a possible new tactic by BO.
The original suit is still working its way through the court. It appears that the court is going to ask an independent jurist to review the case and make an initial assessment. The case, if it goes to trial, will probably not be resolved until at least this summer.
I am now Ken Hamady, LLC:
Same quality service, just a different tax ID starting January of 2007. Those of you who provide me with a 1099 form will use the original tax ID for 2006 and get an updated w-9 for 2007. Call me if you have questions.
Business Objects acquires Nsite Software:
Business Objects has pushed further into the Software-as-a-Service market by acquiring NSite Software. Nsite allows users to access web applications from any browser. Some of their most popular applications include Contact Management, Quote and Proposal Management, Expense Reports, Time sheets and IT Service Request tracking. You can even design your own applications.
You can try it for free with 2 users but to go beyond that costs $40 per user per month. If you go beyond 100 users ($4,000 per month), additional users are only $10 per month. With numbers like these you can see why SaaS is such an attractive area for vendors. And some small or mid-size companies may feel that this is less expensive than trying to maintain a comparable IT department.
By folding BI into the NSite product mix, BO may be able to move from being the tail end of the process (just pulling the data out) to owning the entire process. And, this may finally convince users that the reporting tool is not just a product that they buy (once), but a service that they should pay for on an ongoing basis.
BO's first SaaS offering - Crystal Reports.com - has had limited success, but it is not designed to access the database in real time. Nsite would not have this problem, since the BI tools can be integrated directly into the application services. Go to Nsite.com to learn more.
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
Linking tables vs Appending tables:
New users often assume that just because two tables share a common field, that the tables can be linked in a report. But this is only true when the two tables can be successfully made into one composite table. And, there are only two logical ways that two tables can be combined into one: Linking them (horizontally) or appending them (vertically).
1) Linking is by far the most common. I describe it as horizontal because linking adds the columns of one table to the columns of the other. For instance you have an orders table with 4 fields, Customer, Date, Quantity and Product ID. You want to add a new column to show the product name field, but the name is stored in the product master table. So, you use the product field to link the Orders table and the Product table. this makes the columns of the Product table available to the report. Your available data just got 'wider'.
2) Appending, which works vertically, is less common. It is also harder to do in Crystal. Say you have the same orders table described above and also have inventory adjustments table. You want your report to show both the Orders and the Adjustments for each Product. Both tables have the Product ID but this won't provide a valid link for these tables. Your report should show both the Inventory Adjustment records and the Order Records as separate rows on the report. So, if there are 100 orders and 20 adjustments you need 120 rows. In other words, you need to append the 20 rows to the 100 and then sort them by product.
So, how can you tell if you can link or need to append? Usually by checking for a one-to-many (or lookup) relationship between the tables. Examine the linking fields of each table separately, before they are linked. At least one of the linking fields should not contain duplicates but should have all unique values. The field without duplicates is usually the "one" end of the one-to-many relationship. It allows you to "lookup" values related to that field; values that would have been redundant if stored in the other table. In our example above the Products table is the "one" end of the one-to-many relationship. There is one product to many sales.
If you find duplicates in BOTH of the linking fields, such as our second example, then you probably have an invalid link. The Product ID shows up multiple times in both tables so these tables do not have a one-to-many relationship. Trying to link these tables will generate redundant and typically useless data. They could be appended but, unfortunately, there isn't a native Crystal feature for appending one table to another. To do this requires that you combine the tables in your database or create a SQL command that does a UNION of the two tables.
If you have questions about linking tables or creating a UNION please drop me a line.
Correction for Holidays formula:
Most of you are probably already aware of the FORMULAS page on my site. These are commonly requested formulas that I post for others to use. Most can be copied and pasted into your reports with minimal modification.
Formula #26 (written by Mike Cook) generates a list of the most common US holidays, based on the year you input. This is useful if you have to do business days calculations and don't want to manually maintain the list of holidays. I recently received a message that the formula doesn't pick the correct date for Thanksgiving in years where November starts on a Thursday (like in 2007). I have posted a corrected formula. If some of you are using this formula in your reports then please update the line for Thanksgiving (element 6).
Thanks to Francis Moran for pointing out the error.
Contact Information:
Ken Hamady
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