Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
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