phone: (540)338-0194
email: ken@kenhamady.com


Individual Instruction by Ken Hamady

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