Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
The
Crystal Reports Underground News - Volume 2007.11 (November 2007)
an independent source
for Crystal Reports Information by Ken Hamady
Contents
for November 2007:
**
Crystal Reports 2008 (Version 12)
**
SAP purchases BO/IBM Purchases Cognos
** Free Crystal Reports Viewer (with scheduling and Email)
**
Open enrollment classes December 2007
**
Free Trial of "Mass Verify"
**
PDF eXPLODE fails on Vista
**
*NEW* Guide to SQL Expressions, Options and Commands
**
Create a formula library in Phrase Express
**
Free MICR font (Turn blank security paper into check stock)
**
Store a value today and use it in a different report tomorrow
**
Read back issues at http://www.kenhamady.com/news.html
Crystal
Reports 2008 (Version 12):
Usually
a new version of CR is a mixed bag. There are always new features and
improvements but there are usually some removed features or things that
get worse. So far what I have seen of Crystal Reports 12 is a handful
of improvements and nothing made worse. Here are the new things you can
do:
1)
Add individual non-summary columns to a cross-tab:
You
can now
manually insert rows or columns anywhere in a cross-tab. These can do
calculations using values from other cells in the cross-tab, including
other manually created cells. The new values are even available for
charting.
2)
Make a parameter optional:
Now
users can
skip a parameter. There is even a function that allows a
formula
to check if a parameter was skipped, so it can know what to do. This
means we can do things like have users skip a date parameter and have
the report use today, yesterday or any other date calculation as the
default (skip) behavior. This feature even applies to dynamic
parameters so we now can say a skipped parameter means ALL, or some
other default value without resorting to a union query.
3)
Change a parameter value without refreshing:
You
get to
assign parameters to an interactive side panel. Selection parameters
can be changed and applied to the saved data, which allows you to
select subsets without refreshing. Formatting parameters can change the
behavior of the report on the fly.
4)
Add sort controls to the report:
These
objects allow you to interactively change your sort fields and sort
directions in preview mode.
5)
Disconnect the size of the design area from the printer:
This
is great
for those wide reports that are designed solely for export.
We no
longer need to configure a fictitious wide carriage printer to get a
wide design area.
6)
Change the page orientation within a single report:
This
is especially handy for subreports that need to be landscape inside a
main report that is portrait.
7)
Create a duplicate formula or running total:
If
you need a
series of similar formulas (and I do all the time), just right click on
the first one formula and select "duplicate". A second formula is
created that is identical except for the name, which will have a number
append to the formula name. Each time you "duplicate" you get
the
next number in the sequence. I believe that this feature was also
available in XIr2.
8)
Built-in bar code font:
Right
click any "bar code-able" value and have it display the field using a
bar code font.
9)
Auto-complete for field names in the formula editor:
Just
type an
open curly bracket in the formula editor to get a list of all available
fields. Type the first few characters to narrow the list to field names
that start with those characters.
10)
Save Lock Password:
Requires
the
user to enter a password before saving over the original report (from
within Crystal). Allows saving under a new name without the
password.
11)
Search all report formulas for a field, formula or parameter.
(This
should have been added long ago.)
12)
Page Break after N Visible Groups.
I
see what it does, but it must solve a problem I have never had to
solve.
Then there
are places where they fixed things that they broke in XI:
* You can now copy (static) parameters from one report to another.
* Date parameters now have a decent calendar control again.
* Date parameters can now use normal date format again.
Then there
are the things I haven't played with yet:
* More powerful XML exports
* Flash integration
* Adobe Flex Integration
* Xcelsius content embedded in reports
Backward
Compatibility:
Version
12
reports can be opened by older versions of Crystal Reports,
all
the way back to version 9. Any new features will not
appear. Any new function used in a formula will generate an
error. Surprisingly, a cross-tab with the new manual columns
will
still run without error in older versions of Crystal. The
manually added columns disappear but no errors are generated.
Licensing
changes:
Crystal
12 is
still using Named User licensing. That means you need a valid license
for every report designer, regardless of how often they use the
product. See my recent blog article for more explanation on Named User
Licenses.
There is
only
one edition of Crystal Reports 12 so everyone gets the same tools and
rights to deploy applications. Thick client applications can be
deployed, as always, without restrictions. Same for deploying server
based applications internally. Providing server based apps to your
customers requires that each customer own a copy of CR 2008.
The EULA
for the
CR 2008 BETA had a set of restriction taken directly from the
Enterprise license. That made all consulting and training on CR12 a
violation of the license. After I pointed this out a new EULA
was
posted on the website. The new license is also included with
the
product trial download. I'm not sure if the new EULA made it
into
the boxed product or not, but you can download the updated EULA from
the BO website.
The only
remaining restriction is for training. The EULA says that you need BO's
written permission if you use the software to deliver training. They
have given me written permission with no conditions, but they have yet
to explain how the process will work for others. Stay tuned
for
more on this issue.
SAP
purchases Business Objects / IBM Purchases Cognos:
Crystal
Reports has changed hands again, this time BO was swallowed by yet a
bigger entity - SAP. This is considered a friendly takeover so it will
remain to be seen if/how this affects the Crystal product line. The
general opinion is that it is a good fit. And SAP has decided that BO
will operate as a separate unit so I don't expect any significant
changes in the near term.
On a
related
note - IBM just purchased Cognos this week and Hyperion was recently
purchased by Oracle. So the big 3 BI vendors are now all part
of
something bigger.
Free
Crystal Reports Viewer with, Scheduling and Email Distribution:
SaberLogic
of Wadsworth, OH has developed a new Crystal Reports viewer. It allows
reports to be refreshed, scheduled, exported, printed and even sent out
via Email. It is called Logicity and is compatible with all versions of
Crystal from 8.5 through XI. They have released both a free version and
a PRO ($45) version with extra features. My readers get a
discount
on the PRO version till 12/31/07. See my BLOG article
for details.
Open
enrollment classes December 2007:
You
need information to run your business, so stop struggling with Crystal
Reports and learn how to use it fully. 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.
We even include material on cross-tabs, charts and 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 and the next round
is
mid-December 2007. See my web site for more details or call
me at
(540) 338-0194.
So what
makes my
class different? I have written my own course materials and
have
used them to teach over 2,000 satisfied students. And, I give
you
a toll-free number so you can call me after class with questions.
Or, if you
want
to schedule a class at your office, using your data, that is my
specialty. I have even found several other top-notch
instructors
around 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.
Mass
Verify now has a Trial Download:
David
Martinko of Redeemed Software has added a trial download for Mass
Verify, and encourages you to try it. Mass Verify performs
the
"Verify Database" step for multiple reports in one step. See
my LINKS
page for more info and a discount.
PDF
eXPLODE fails on Vista:
Not
all of the third party utilities I use are are fully compatible with
Vista. Some need to have their compatibility settings adjusted to be
run in XP mode. Some need to be run as Administrator. Some do not run
at all. For example, PDF eXPLODE does not currently work on Vista (but
they are working on it).
If you run
into any compatibility issues with Vista and Crystal Reports or a
Crystal utility, I would love to hear about it.
Coming!
The Expert's Guide to SQL Expressions, Options and Commands
Use
these SQL tools to improve performance, solve data problems and add
capabilities to your queries. This new guide, ready in
January,
covers all versions from 8.5 through 12. It shows when and
how
you can change the reports SQL, and how to use your own SQL as the
basis of a report.
In the
section
on SQL expressions I show 16 different SQL calculations that you can
pass directly to the database. These include string
manipulations, boolean tests, data type conversions and date
calculations. Many of these demonstrate useful functions that
are
not listed in the SQL expression editor. I even explain the
rules
for writing subqueries within a Crystal SQL Expression, something that
you won't find documented anywhere else. And each of these
expression examples is shown in 4 different SQL flavors (SQL Server,
Oracle, MS Access and MySQL).
If this is
something that interests you let me know and I will notify you as soon
as it is ready.
Create
a formula library in Phrase Express:
I
have been testing out a handy little utility called Phrase Express for
a few months. Phrase Express lets you store a library of phrases that
you can quickly paste into Email messages and other documents. I use it
primarily to help with Email phrases that I type several times a day
and it is great. It works with any text based application.
So I had to
slap
my forehead when Tim Garner (College of the Sequoias)and Ido Millet
(Millet Software) suggested that I use it for storing Crystal Reports
formulas. You wouldn't believe how often I visit my own web
site
just to copy a formula. So I took the formulas I use most often and
stored them in Phrase Express. I can now paste them directly into the
formula editor with a few clicks. I could even assign hot key
combinations or 'auto correct' abbreviations to call up my
favorites. You should try it if you have a list of formulas
that
you use regularly.
It costs
only $19.95 and you can try it for free.
Technically you can continue to use it for free for 'non-commercial'
use. (I guess that is for those of you who use Crystal Reports strictly
for fun.) I am even working on library of my top 20 formulas
to
share, so other users can import my library directly into their own
Phrase Express library.
So, let me
know if you try Phrase Express, what you think of it, and if you are
interested in the library.
Free
MICR font (Turn blank security paper into check stock):
Many
of my customers use Crystal Reports to print checks. This can be a
tricky endeavor, especially aligning the report output with the check
stock.
One
solution is
to incorporate all of the check stock information into the report
itself and then to print the check on "blank" security check stock
paper. Printing the static lines, boxes and text is pretty
straightforward. But one part of the check keeps most people from ever
trying this. That would be the MICR characters, (Magnetic Ink
Character Recognition). That is the special line of numbers at the
bottom of each check. Printing this line requires a special font and a
special magnetic toner.
However,
one of
my readers,Erik Rasmussen, has successfully done this with Crystal
Reports and sent me a link for a free MICR Font that you can use to
print that line. The font is called GnuMICR and it was created from
scratch by Eric Sandeen of Saint Paul, Minnesota. See my BLOG article
for more information on using this font.
Store
a value today and use it in a different report tomorrow:
Those
of you who have worked with subreports know that you can pass a value
from the subreport to the main report and back again. But have you ever
needed to create a value in one report and use that value in a
completely different report? Or maybe you need to store a value from a
monthly report and use it in next month's report? Here are 2 ways to
accomplish this using 3rd Party dll files:
Chelsea
Technologies has just released the Share UFL that allows you to store a
value to an external XML file. The value can be then retrieved in any
report - even the next instance of the same report. And you can store
as many values as you need. A free trial version is available and the
cost to purchase is roughly $100.
A less
expensive
option is to use the CUT Light DLL provided by Millet Software. This
has been around for years and I just remembered that it could be used
to do the same thing. The main difference is that CUT Light stores your
values in an INI file rather than an XML file. CUT Light also provides
a dozen other useful functions in the same DLL.
For Links
and syntax examples see my BLOG article.
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
2007 by Ken Hamady
All
rights reserved - Republishing this material requires written permission