The Crystal Reports Underground News

Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide

(For recent updates read my BLOG, The Crystal Reports Underground )

The Crystal Reports Underground News - Volume 2005.01 (January 2005)

an independent source for Crystal Reports Information by Ken Hamady

Contents for January, 2005:

** Highlights of Crystal Reports 11
** The end of concurrent licenses
** Public Intro and Advanced classes in Frederick
** Expert's Guide to Subreports, Parameters and Alerts (New)
** My Library of Crystal Reports Materials:
    Expert's Guide to Formulas
    Expert Techniques Volumes I and II
    Quick Reference to Crystal Reports in Visual Basic
    Quick Reference to Crystal Reports in VB.NET
** Selecting an ODBC Connection at runtime
** Data Link Viewer 4.5001 Released
** Read back issues at http://www.kenhamady.com/news.html

Highlights of Crystal Reports 11:

Business Objects has released their XI product, line which includes Crystal Reports 11. I am happy to report that unlike the upgrade to version 10, the upgrade to version 11 has some significant improvements for report designers. Here are my favorites:

Dynamic Parameters:
One of the longstanding complaints about Crystal parameters is that they can't generate the list of default values at runtime. The 'pick list' is static and saved with each report. The only work-arounds were to
   1) create your own front end in VB or
   2) Use a third party add-on

Now Crystal Reports allows you to generate the 'pick list' at runtime, directly from your data. You can specify a field in the report and Crystal will query the database at runtime for current values. You can even create a command (SQL statement) to generate the list.

Cascading Parameters:
You can now have one parameter prompt determine the list in another prompt. For instance, when trying to select an employee you could first select a department. Then Crystal would show you a list that only included employees from that department.

Images read from a stored path:
Up through v10, the only way to have a different image for each record was to physically store the image as a BLOB field in the database (or do some fancy programming in VB). Many databases however, store the file path to the image. Now Crystal will read the path in a database field to find these images and place them in the report.   What's more this path can even be a URL, making every image on the web available to your reports.

Conditional Ascending/Descending for Groups:
We have a new condition button for choosing Ascending or Descending order for the groups in a report. That allows you to let the user choose Ascending or Descending from a parameter at runtime. For some reason Crystal didn't provide this condition button for sorting - just for grouping. So, if you need to do conditional sort order get a copy of my "Expert Techniques Volume I". My method is a bit more work but it can be used for both sorting and grouping, and it can be used in older versions of Crystal.

Conditional "N" in a TopN report:
Crystal has added another condition button to select the number "N" in a TopN report. So TopN can now be determined by parameter or by formula. Again, if you need to do this in an older version of CR you can get my "Expert Techniques Volume I" where I show a work-around method for this. I also show you how to let your users choose the summary field that will be used in the TopN.

Backward Compatible:
Also note that a report created in version 11 can be opened in version 10 and even version 9.   If you open a v11 report in an older version it will pop up a warning to let you know that any new v11 features will not work under the older version.

Compliments:
    They now allow us to disable AutoLinking without going into the registry.
    They have FINALLY made the "Field Clipping" feature default to OFF instead of ON

Complaints:
    The calendar for date parameters has always been really nice, and now it is both slow and ugly.
    We still can't loop back through the report expert more than once. This is really handy in v8.5 for getting several fields neatly onto the detail band.
    The refresh button has been moved and is no longer the lighteningbolt. It now looks like something like the Yin-Yang symbol.
    When inserting a chart you now place the chart on the report canvas before you design it. If you do this in preview mode it will run a default chart before you get a chance to change it.

Detailed list of other minor features added (let me know if I have missed any):
A special HTML Preview, which is only useful if you are implementing CE.
The "Workbench" which lets you group reports into projects, mainly for publishing into Enterprise.
Formatting Fields - a new property to repeat on horizontal pages (wide cross-tabs).
Formatting Fields - Hyperlinks can now read directly from a field for Email or web sites.
File / Options / Formula Editor Tab - The ability to set how the formula editor deals with null values

New Math Functions
    RoundUp - Rounds Up
    MRound - round to the nearest non-1 value
    Ceiling - Round Up to the nearest non-1 value
    Floor - Round Down to the nearest non-1 value

New Financial Functions
    A whole bunch of new very specific functions here.

New String Functions
    URL Encode/ URL Decode for dealing with UTF-8, ISO-8859 and Shift-JIS coding schemes

New Date Functions
    ShiftDateTime - for adjusting between time zones

Functions-PrintState
    Heirarchy Level - for knowing your position in the heirarchical grouping if using this feature.

Document Properties
    PrintTimeZone - Time Zone of the Print Date
    DataTimeZone - Time Zone of the Data Date
    ContentLocale - reads your locale from regional settings
    Group Level - Lets a formula determine what group level a field has been assigned to.

The End of Concurrent licenses

Thank you to all of the folks who were able to confirm that Business Objects will not be selling Concurrent Licenses for the Crystal Enterprise product line starting in 2005. You are now required to choose between named users or a processor license.

Public Intro and Advanced classes in Frederick:

My specialty is teaching at your office, with your data, but not everyone has the 3 or 4 students to make this cost effective. If you want to take one of my classes and don't have enough people for an on-site class, come to Frederick, MD and take my public class. The classes are always small and informal with lots of room for discussion. For details see:

http://www.kenhamady.com/public.html

Is it worth the trip? I have personally taught 1875 satisfied students including students at the National Institutes of Health, the Library of Congress and the Executive Office of the President. I am also the all-time, top ranked Crystal Reports expert at Tek-Tips.com. For more information on public or on-site classes you can call me at (540) 338-0194.

Expert's Guide to Subreports, Parameters and Alerts:

Why not let a master teacher walk you through these 3 important (and sometimes confusing) Crystal Reports topics. I have explained these features to hundreds of students and my explanations and exercises have been refined and rewritten through 10 years of teaching Crystal. Put that experience to work for you through my new e-book, The Expert?s Guide to Subreports, Parameters and Alerts. The material includes an in depth discussion of each feature, example reports that illustrate the techniques, and exercises that walk you thorough each step. The exercises are the key. They have been written to provide practical examples that are easy to understand and follow.

The material is condensed into 34 no-nonsense pages and is backed up with 22 sample reports to demonstrate the techniques discussed in the material. The exercises provide keystroke by keystroke instructions for implementing each of the discussed features. All for only $28. See the topics covered on the Library page of my web site.

The material is applicable to versions 8.5, 9, 10 and the sample reports can be opened with any of these Crystal versions. An update for v11 will be available shortly to cover the new parameter features in that version. The update will be sent automatically to everyone who purchases the material.

My complete Library of Crystal Reports Materials:

http:/www.kenhamady.com/tools.html

    Expert's Guide to Formulas ($36)
    Expert's Guide to Subreports, Parameters and Alerts ($28)
    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)


Selecting an ODBC Connection at runtime:

Some users have reports that need to be run against several different databases. Crystal parameters don't allow you to set the database at runtime. One solution is to create a VB front end that can change the location. However if you are using Crystal standalone you might have resorted to creating a separate report for each database. A better work-around is to simply create the report using a temporary ODBC connection (or DSN). Then make sure that DSN doesn't exist at runtime. When Crystal tries to run a report and can't find the original DSN, it will open the list of all available DSN's on that PC. You can select a different DSN each time you run the report.

This is especially handy if you have a report that is based on a command. One truly annoying feature of commands is that Crystal provides no way to set the location of a command from Crystal Reports. This can only be done if you have configured a repository in Crystal Enterprise. So if a report uses a command that points to your test server and you want to point that report to your production server, you will have a problem. Fortunately you can use the technique described above to select the correct DSN at runtime. If you don't save the report after selecting an existing DSN, it will always ask for a DSN at runtime. If you select a DSN and then save the report, it will remember the new DSN. If you are running the report from a viewer program you might try exporting the report to RPT format. This should also create a report looking for the new DSN.

Data Link Viewer Update:

Millet Software has added a few enhancements to DLV since the last time I discussed it. One is the ability to select the ODBC connection at runtime. You could use the technique I described in the last article, but if you are using DLV you don't need to mess with temporary DSNs. You can run the report with its default DSN or override that DSN and select another at runtime.

Another recent feature is Integrated Authorization, which allows you to store a user name and password in DLV to avoid repeated logins. The information is encrypted and tied to a particular windows user ID on a particular PC for added security.

Last is the ability to automatically filter report data based on the user's login, providing automatic row level security for your reports.

Features like these set DLV apart from the other viewers that I have seen. And DLV is still on of the least expensive viewers out there. See the LINKS page of my site for more information on Data Link Viewer, or contact me if you would like to request a free copy for evaluation purposes.

Removal instructions:

I have sent you my newsletter because you are a former client, or because you have contacted me regarding Crystal Reports. If I am mistaken, or if you don't wish to receive the newsletter, please reply to this message with the word 'unsubscribe' in the header. I do not share these Email addresses with anyone else.

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 2005 by Ken Hamady
All rights reserved - Republishing this material requires written permission