Crystal Reports Training by Ken Hamady, MS, Reporting
and Training Nationwide
The
Crystal Reports Underground News Volume
2009.05
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for May 2009:
** Free DLL with new Crystal formula functions
** Expert's Guide to Formulas updated for CR 2008
** My Library of Crystal Reports materials
** Make your Crystal Reports Pornography before you upgrade!
** Learn Crystal Reports (on the cheap)
** On-Demand Subreports that use saved data (no refresh needed)
** Reporting on data that isn't there Part 2
** Other recent blog articles:
Moving values backward in a report (Wormhole)
Showing wide cross-tabs in a subreport
Calculate your share of a billion dollar expenditure (US taxpayers)
Free DLL with new Crystal formula functions
One of my readers tipped me off to a web site that has a handy Crystal Reports DLL
that you can download for free. This DLL adds several custom
functions to your formula editor's function list. These allow
your formulas to do all sorts of new things (and some I don't recommend
that you try).
There are about 40 functions in the DLL that allow your formulas to interact with the operating system. Here are some examples:
* Store and retrieve entries in an INI file
* Check a registry value
* Check the name of the user logged in to the PC
* See if a file exists, then either append to that file or delete it.
* Launch a windows program
* Play a sound file
* Copy text from the report to the clipboard
This last one is now in one of my reports and saves me a few keystrokes
whenever I have to manually invoice a customer. But there is
another function that I only mention as a warning - a function that
allows you to write a registry value. That one could be dangerous
if used incorrectly or accidentally.
The page mentions version 9 but the functions work in all three
versions of Crystal that I tested: v8.5, v10 and v12. It
also mentions an install script, but all I did was put the DLL in
C:windowsCrystal and it was recognized by all three versions.
If some of these functions sound familiar it is because many of them
are available in a tool I have written about called Cut Light by Millet
Software. Cut Light Costs $50 but has 2 advantages. First, since
Cut Light is a commercial product you can expect more responsive
support. Second Cut Light doesn't include a "Write to the
registry" function which eliminates some of the risk. You can
find a link to Cut Light on my LINKS page. And don't forget to request a free review of my favorite tools using the autoresponder email on that page.
Expert's Guide to Formulas updated for CR 2008:
After updating all three volumes of my Expert Techniques
series I turned my attention to the The Expert's Guide to Crystal
Reports Formulas. I found a handful of improvements to make and
decided to include two new functions that I find myself using more and
more. They are Split() and DrillDownGroupLevel().
If you purchased this volume after April 1, 2009 you should have the
updated volume. If you purchased this guide before then, please let me
know (along with the Email address that you used to purchase the guide)
and I will send you the update.
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)
Expert Techniques Vol. III($19)
Quick Reference to Crystal Reports in Visual Basic ($16)
Quick Reference to Crystal Reports in .NET ($14)
You will find these on the LIBRARY page of my site.
Make your Crystal Reports Pornography before you upgrade!
For
those of you who have been putting off making that pornographic film
featuring Crystal Reports, you had better do it before you upgrade to
Crystal Reports 2008 Release 2. After that point you will be restricted
from using Crystal Reports images in your pornography. You don't
believe me? Well read the new license for yourself. SAP has told several of my readers that the license for Eclipse
is identical to the one for CR 2008 R2. (The page says "Log-in
required" but at least today it works without a log-in.) Read the
restrictions in section 4.3 item b). If the first 'license' link
moves, use the link on the 'Eclipse' page.
Besides restricting your cinematic options, the part most of you should be complaining about is section 4.3 item a). As I mentioned in March,
this is aimed directly at removing the last independent courseware
vendor, Vision Harvest, from the market. If Vision Harvest stops
producing Crystal Reports course books, and if everyone else becomes
convinced that they have to get SAP's permission to use their
screenshots, then SAP will be able to charge any fee they want
for the "right" to publish a coursebook. I predict course books
costing $150 -$200 per book, if they succeed.
It will surprise some of you to learn that this license change has no
direct effect on me. As many of you know, I have never found it
necessary to use screenshots in my course materials. My concern
is that this hidden attempt to fleece users will backfire and damage
the Crystal Reports brand in the long term. So do yourself a
favor and let the folks at SAP/BO know what you think before this
license takes affect. Send an Email to Beth Christopher (first name ,
period, last name @SAP.com).
And if you do make that film let me know.
Learn Crystal Reports (on the cheap)
Even
with budgets tight there is no need to miss out on Crystal Reports
training. You still need information to do your job, so stop struggling
with Crystal Reports and learn what it can do. The most cost-effective
way to be taught Crystal Reports is in my individual trianing
program. It is ideal for people who:
Can't take 2 days off for a regular class.
Want to learn in their own database.
Need to cover only a few topics
Want to Learn Crystal Reports from someone who has taught 2,500 satisfied students.
Remember, the cost of a typical 2-day Crystal class will cover 7 hours
of individual time with me. You can start with 2 hours and get
free course materials with exercises. Do as much work as you like
on your own and use your prepaid time to work with me by phone and
remote connection. We can review lessons, discuss questions or even
troubleshoot existing reports - my time is yours. For details see
the "Individual Training" page on my web site.
Or, if you want to schedule a class at your office, using your data, I
can save you money there as well. I have discovered several
top-notch instructors all over the US, UK and Canada that can deliver
my class at your location for a very competitive price. Call or
visit my web site for more details.
"On-Demand" Subreports that use saved data (no refresh needed)
I hate
being wrong, but it isn't as bad when I learn something new and
useful. One of my students wanted to use a row of small
"on-demand" subreports in one of their reports. The only problem was
that the report had to be deployed without access to the
database. That meant relying on saved data with no refresh
allowed. Of course a true on-demand subreport requires the
ability to refresh.
But in his experimenting he did something that I was convinced would
not work. He took off the "on-demand" check mark, but left the
subreport the same small size. The subreport ran at the same time
as the main report, but the subreport was only partly visible. You
could only see what was visible through the small 'keyhole' object on
the main report. So he went into the subreport and added a text object
to the top, so that it would appear through that keyhole. Now in
the main report it looks just like the original "On-Demand" subreport.
And if you double click on the object, it opens up to show the full
subreport on a new preview tab, much like the original "On-Demand"
subreport would do. And since the subreport data was already generated
and saved, there is no need to query the database again.
Now I know that double clicking on the preview of a subreport opens
that subreport in its own tab. What surprised me was that the
subreport would preview in full width, when it was a narrow object on
the main report. The design mode of the subreport was only as
wide as the keyhole, leaving most of the subreport objects floating in
empty space to the right. So I expected those objects to be
invisible in the subreport's preview. But, apparently that preview is
not affected once you drill down. I even went back and tested this in
v8.5 and found the same behavior.
So, thanks to Craig Wright for teaching me a new trick.
Reporting on data that isn't there - part 2
This
picks up on the previous article that showed how to "fill in the
gaps" in a report that is missing a product, salesman or
date. The second solution is to add a primary table to the report
that has all the values you want. You then link from this table
to your existing tables, using an outer join (usually a Left Outer
Join).
Lets take the example from the last newsletter where we want ALL
salesmen to show up, even if they have no sales. We find a table
that includes all of the salesman and use this table as the reports
primary table. We link this table to your transaction table with
a "Left Outer" join. This tells the database that you want all
records from the primary table and matching records from the
transaction table. Now all salesmen will show up and will also be
available for cross-tabs and charts.
However, there is a serious weakness to this approach. If you put ANY
criteria on the transaction fields, you cancel the effect of the outer
join. So in our example, if you were to select transactions for a
specific month, you would then lose all salesmen who had no sales in
that month. To keep the outer join behavior in place you have to
eliminate ALL criteria from the 'Outer' or optional table. That
means including ALL transactions for ALL months in the report.
You can suppress the details of the records you don't want to see and
you can write formulas to make sure that only those records in the
month are included in your totals. But if your database is large,
this makes for a very slow report.
And be careful if someone tells you that you can get around this by
adding and IsNull rule to the selection formula in Crystal. For
instance having this criteria:
IsNull ({Date}) or {Date} in LastFullMonth
This will now include salesmen who have no records (ever).
However, it will not pick up the salesman who has one record January
and no records in April. His one record isn't null and it isn't
in the correct period so he is excluded. This confuses a
surprising number of people. There are
methods in more advanced SQL to filter your outer joins correctly but
you can only implement them in the SQL. You can read about some
of these, and a full explanation of Joins, in my book:
The Expert's Guide to SQL Expressions, Options and Commands.
Other recent blog articles:
Moving values backward in a report (Wormhole)
Showing wide cross-tabs in a subreport
Calculating your share of a billion dollar expenditure (for US taxpayers)
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
2009 by Ken Hamady
All rights
reserved - Republishing this material requires written permission