Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
The
Crystal Reports Underground News - Volume 2002.12 (December
2002)
an independent source
for Crystal Reports Information by Ken Hamady
This months contents:
** NEW! The Expert's Guide to Crystal Reports
Formulas
** Download a complete list of all of Crystal's Technical Briefs
** Expert on-site training or consulting
** Converting MS Access Reports to Crystal Reports, automatically
** Crystal Desk 3.0 and a New Report Viewer
** Public classes in Leesburg, Baltimore and Philadelphia
** Using Cross-tabs to Increment
Running
Totals
** A New Formula Debug/Trace Window in Crystal v9
** Warning! Crystal v9 Commands can now Read and Write
** My Quick Reference to Crystal Reports in Visual Basic
** Read back issues at http://www.kenhamady.com/news.html
NEW! The Expert's Guide to Crystal Reports Formulas:
Let
me
take you "under the hood" with my new book, "The Expert's Guide to
Crystal
Reports Formulas". The guide explains Crystal Formulas from the
simplest
to the most complex. And, you get more than just examples of
syntax.
I explain HOW the various techniques work, WHY they work and WHEN to
use
them. I cover all of the major topics, including tips and tricks
that
are rarely taught in classes. So, if you want a clear explanation
of
variables, arrays, looping logic and select case statements, this guide
is
for you. See the full topics list on my web site.
The guide includes 28 annotated reports that demonstrate the techniques
discussed
in the material. These reports are saved with data so you can
immediately
preview them, modify them and experiment. The guide contains 45
no-nonsense
pages of detailed explanations and practical examples. It is
applicable
to all versions of Crystal, although the sample reports will open only
in
versions 7 through 9.
The cost is only $36 and you can usually have it within an hour.
For
more details, visit my website at:
http://www.kenhamady.com/form00.html
Complete list of Crystal technical briefs:
Each
month I scan Crystal's website to find new and useful technical
documents.
Now you can download a complete directory of all of the technical
documents
on the site. The directory is contained in several PDF files that
list
all of documents. Each list shows the name of the document, a
short
description and a direct link to download the document.
The Links page on my website has a the link to the directory PDF files.
Expert On-Site Training or Consulting:
I
have
personally taught over 1400 satisfied students in more than 30
states.
Training with the customer's data is my specialty, and I charge about
half
of the "List Price" for Crystal Training.
Do I know my stuff? Check out the Crystal Reports Forum at
Tek-Tips.com
and you will find me listed as the top Crystal Reports expert out of
over
21,000 members:
http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/100/pid/149
Can I teach? Many students have said that my classes are among
the
best technical classes they have taken. Ask to speak to a recent
customer
as a reference. Call for more information at (540) 338-0194.
Converting MS Access Reports to Crystal Reports:
Diamond
Edge, a small outfit in Utah, is selling the first tool on the market
that
can convert an MS Access Report into a Crystal Report. It is a
part
of a product that they sell called the Access Converter that is
designed
to convert Access applications into a VB/Java applications. This
is
great news for anyone with a heavy investment in MS Access.
The tool costs $395, and requires that you have CRv8.5 Developer
Edition
installed on the same PC. It does NOT currently work with CRv9
Developer
Edition, but this may change in the near future. I was told by
one
of the Diamond Edge staff that the reports may need some minor
tweaking,
especially if you use complex joins. And, since Crystal Reports
allows
formulas to be written in either Crystal or Basic syntax, the Converter
leaves
all expressions in basic syntax.
You can download a trial version, but it will only convert the
application
and reports in the MS Access sample database, Northwind.MDB. I wish I
could
tell you how well it worked, but since I have version 9 of Crystal, I
couldn't
run it. But, even if this tool provides only a rough conversion,
it
could still be a huge timesaver, especially if you have to convert many
reports.
You will find the Access Converter on the Links page of my web site,
listed
under utilities.
Crystal Desk 3.0 and Report Viewer 9.0:
CrystalDesk
has just released Report Scheduler v3.0, an update to the original
Crystal
scheduler program. It allows you to run reports on a regular
schedule
or on a one time basis. The new version adds PDF export
capability
and has a more user-friendly interface. See my links page for
details.
Price - Single User License $229, Site License $998
CrystalDesk has also added another report viewer to the market, called
Report
Viewer 9.0 (compatible with CRv9). It allows you to View, Print,
Export
and Email any Crystal Report. It can view reports with saved data
or
connect to your database and refresh the data. It even allows
command
line control so that you can use it with applications or batch files.
Prices - Single User License $48, 5-Pack $189, Site License $948.
Don't just take a class, learn from a Crystal Expert:
Baltimore,
MD
Introductory class January 29-30
Advanced class February 25-26
Philadelphia area, PA
Introductory class January 27-28
Advanced class February 27-28
Leesburg, VA
Introductory class February 6-7
Advanced class January 13-14
Each class is $675 per student.
Course outlines and registration info are on my website at:
http://www.kenhamady.com/public.html
Or, call for more information at (540) 338-0194.
Using Cross-Tab cells to create running totals:
I
read
an article on Crystal's website this week that has opened up a whole
new
area to explore. If you you have worked with variables in Crystal
Reports,
particularly running totals, you know that the location of a formula
determines
how often it "fires" the variables. These formulas are usually
located
in one of the 7 sections of the report. But, you can also fire a
variable
in the formulas that control conditional formatting.
What I learned this week is that you can fire variables in the
conditional
formatting formulas of every cell in a cross-tab. These formulas
give
you access to the values in the cross-tab, using the
"CurrentFieldValue"
function. That means that you can take the values inside a
cross-tab
and pass them to formulas later in the report. The example given
in
the article showed that you could recreate a running total in a
cross-tab
located in the group header, and display the running total before the
details
of the group. This is something that isn't normally possible in a
report.
The variables that you can fire in the cross-tab can't be displayed
within
the cross-tab itself, but they can be used in other formulas in the
report.
Starting with version 8 you can also store the row and column heading
values
of any cell, and then pass them to other formulas. So, for
example,
I could write a formula that checks each cell and stores the highest
value
in the cross-tab. When I find it, I could also store its row and
column
labels, and use display these values outside the cross-tab.
The cells that you choose to conditionally format will determine how
often
your formula will fire. In general, the formula will fire each
time
the formatted cell appears in preview, and most cells appear more then
once.
A basic cross-tab, with one row field and one column field, has 8 cells
in
design mode. If you format the center cell, your variable will
fire
on every instance of that cell in preview. In other words, it
will
fire on each of the interior cells. If you format the middle cell of
the
bottom row, your variable will fire at the bottom of each interior
column
(not the first or last).
The one surprise came when I tried to figure out which cells of the
cross-tab
would fire first. To check, I formatted every cell in my
cross-tab
to append its current value onto the end of a single long string
variable.
The order of the items in the string told me which cells had fired in
which
order. The order I discovered, shown below, wasn't what I
expected:
First all of
the
cells with totals:
1) The first column of
totals
from the top total down to the bottom.
2) The next column of
totals from
the top total down to the bottom.
3) The other columns of
totals,
in turn, going from left to right.
Then the cells with
labels:
4) The lower left corner
cell
that has the label "Total".
5) The row labels (first
column)
starting at the bottom and going up.
6) The upper right corner
cell
that has the label "Total".
7) The column labels (top
row)
starting at the right and going left.
One note, you may want to make the Cross-tab invisible, however this
takes
a bit of work. If you simply suppress the cross-tab, or the
section
holding the cross-tab, it won't run. None of your formatting
formulas
will fire. You can, however, suppress the cells and lines of the
cross-tab,
making it invisible. And then underlay the section so that it
doesn't
take up much space. For a more detailed explanation, and a
sample
report, you can purchase my "Expert Techniques
Volume
I" which shows this technique and 29 other Tips and Tricks.
Debug window in v9:
Many
of you have worked with complex formulas. You know how
frustrating
it can be to get a message that says:
"the subscript must be between 1 and the size of the
array"
and not have the faintest clue which of your twenty subscripts is
causing
the problem. Crystal v9 now provides some help. There is a
new
debug/trace window that appears when a formula generates and
error.
When the error occurs, the Formula Workshop opens, showing you the
formula
that generated the error. The last few steps of processing are
now
shown in a pane to the left of the formula window. Each step
shows
the field, variable or expression as it was processed. Next to
each
item is the value of that object at that point in the process.
Variables
show their current value(s), even if the value is an array or a
range.
Fields show the value returned from the current record in the
table.
Expressions show the calculated result of the expression. This is
a
great time saver for complex formulas.
Warning! Crystal v9 Commands can now Read and Write:
One
of
the selling points for Crystal Reports has always been that you can
give
it to end users without having to worry about them destroying
data.
Unlike MS Access, Crystal has never provided the ability to write data
back
to an existing table. Even when using the Export feature, an
export
to ODBC was always forced to create a new table, so that it could never
affect
the data.
While this is still essentially true in v9, there is one way that
Crystal
can be considered a read/write tool. This is in the area of SQL
commands.
Crystal v9 allows you to run your report using any SQL statement as a
data
source. This can include statements pasted into Crystal from
another
source, like an existing stored procedure. This adds significant
power
to Crystal Reports, but comes with a new and significant risk.
The risk is that SQL can both read and write to the database. If
you
copy a SQL statement from another source, you might not notice that it
makes
changes to the data tables. If you run this statement from
Crystal,
and you have read/write access to the database, you could unknowingly
corrupt
your database. Of course, this has always been true in MS Access,
but
this is new with Crystal.
Thanks to Bruce Ferguson at Chelsea Technologies for pointing this out.
VB Quick Reference Guide, updated for v9:
If
you
need to incorporate Crystal Reports into a VB application, you should
get
my Quick Reference Guide. It gives an overview of the integration
methods,
with syntax examples for the most important commands. It comes
with
source code examples including a simple report viewer. It also
includes
troubleshooting tips. It is only $16 and can be Emailed to you, usually
within
an hour. Follow the link below for more details:
http://www.kenhamady.com/vbref.html
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.
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 2002 by Ken Hamady
All rights reserved - Republishing this material requires written
permission