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