Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide
The
Crystal Reports Underground News - August 2002
an independent source
for Crystal Reports Information
by Ken Hamady, MS
Contents for August:
** Evaluating your Crystal Instructor
** New Utility to Search/Modify an entire batch of Crystal Reports in
one
step
** Reporting on Data that Isn't There.
** My Quick Reference to Crystal Reports in Visual Basic
** Additional CALs now affect Broadcast License
** CUT Utility goes visual
** Public classes in Leesburg, Baltimore and Philadelphia
** Is there a subreport hidden in this report?
** Read back issues at http://www.kenhamady.com/news.html
Evaluating your Crystal
Instructor:
A Crystal Reports class can be a
significant
investment, so it makes sense to see who will be teaching it. If
you
are planning on taking a Crystal class, or having a class on-site, I
recommend
that you find out the name of the instructor and ask the following 3
questions:
1) How long has this instructor been working full-time with Crystal
Reports?
2) Roughly how many students has this instructor taught?
3) How often does this instructor teach a class using the student's own
data?
I have been working with CR for 6 years (11 years if you count other
reporting
tools) and I have personally taught Crystal to over 1300
students. Training
on location is my specialty and I don't charge extra to incorporate the
student's
data into the lessons. Many students write that my class as
the
best technical class they have taken. That is probably because
Crystal
is all I teach and also because I wrote the materials that I use.
I currently spend almost half of my time either writing reports for
real
customers or solving reporting problems for users. This includes
a few
hours each week answering questions at Tek-Tips.com, the busiest
Crystal Reports
forum on the web. You will find me ranked as the top expert out
of
over 23,000 members.
http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/100/pid/149
Search/Modify multiple Crystal
Reports
with RPT Inspector:
RPT Inspector is a new utility
that
allows you to modify an entire batch of reports in one step.
Batch
modifications can be made to almost any property - including both
report
properties and individual object properties. For example, I was
able
to search for and find all of the reports that used a particular
function
in a formula. I was then able to change these formulas in the
entire
batch in one step. I could see this being a huge time saver if
you manage
many reports.
RPT Inspector even exposes some properties that you can't otherwise
modify
in a Report. For instance, it allowed me to change the name of a
subreport
AFTER it was inserted - something that normally requires hex-editing
the RPT
file. It also exposes the height property of each section as a
number,
which makes it possible to give several sections the exact same
height.
This has always been a needlessly difficult task in CR.
See my LINKS page above for a link to this product's web site.
Reporting on data that isn't
there:
Crystal will not create a group
unless
there is at least one record for that group in the report. This
is also
true for Cross-tabs and Bar/Line Charts. However, it is not
unusual
for users to request that missing groups show up with a zero.
There
are several ways to do this and the approach to use depends on a number
of
factors. I will give a brief description of 6 different
techniques
below. If you need help applying these techniques to a specific
report,
drop me a line.
As an example, lets say that you need a report to list total sales for
each
salesman in a month. We want each salesmen listed - even those
with
no sales in the month. Here are your options:
1) Manually enter a zero sales record for each salesman each month:
This is not usually practical, but it is the easiest short term
solution
if the report is a one time item. Charts and cross-tabs will
incorporate
the zero groups automatically.
2) Expand your selection criteria to include irrelevant records to
create
the groups:
Assume that the transaction table includes both sales and calls, and
that
every salesman has at least one call per month. Your report could
select
the records for BOTH sales and calls. Then you can write a
formula like:
if {Type} = "Sale"
then {Amount} else 0
When you subtotal this formula by salesman you will have a group for
every
salesman, but a total of only the sales transactions. If you are
printing
the details you would suppress the call details and only show the sales
details.
Again, charts and cross-tabs will includes the zero groups
automatically.
3) Join a primary table that has all groups, and use an outer join:
If you have table that lists all of the salesman, you can use this
table
as the reports primary table. Link this table to your
transactions with
a "Left Outer" join, which tells the database that you want all records
from
the primary table and only matching records from the
transactions. Again,
charts and cross-tabs will includes the zero groups.
However, there is a 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, when you select transactions for one month, you
would then
lose all salesmen who had no sales in that month. To keep the
outer
join behaviour you have to eliminate ALL criteria from the 'Outer'
table.
That means including ALL transactions for ALL months. You can
suppress
the details of the records you don't want to see or use, but if your
database
is large, this makes for a very slow report.
4) Hard code a conditional total for each salesman:
If you don't have a primary table for salesman you could hard code the
list
into a set of formulas. Say you have 10 salesman. You could
create
10 conditional formulas that each look like this:
If {Sales.Salesman} = "Ken Hamady"
Then {Sales.Amount} else 0
You write one formula for each Salesman and then create a grand total
of
each Formula. You can arrange the totals in the Report
footer
in any way you like, with labels next to them. The report no
longer
requires a group.
This technique is often used with dates or numbers in a series.
Say
you wanted to display the 14 days in a user specified 2-week period,
including
empty days. You could create 14 formulas that calculate the 14
required
dates. Each formula would calculate a different date by adding a
number
of days to the start date selected by the user. Each of these
formulas
could then be used in a conditional formula like the one shown
above.
You would add a corresponding grand total for each formula.
The main downside to this technique is that you have to change the
report
every time you add a new Salesman or change the number of days.
Another
down side is that these 'groups' can't be used as row / column fields
in Cross-tabs
or as groups in Group Charts. They can be used in advanced
charts,
but the chart labels will include either the words "Sum of " or the @
sign.
5) Use Subreports to look for the details.
You can create a simple report from the Salesman master table that
includes
one record per salesman. This report could then have a linked
subreport
on the detail section which would launch a new subreport to get
each
salesman's transactions. The main report would show every
salesman even
if the subreport was empty.
The downside is that running multiple subreports is inefficient.
If
the list was long, the time needed to process all of the subreports
might
be prohibitive. Also, data from multiple subreports cannot be
used to
create a cross-tab or any type of chart.
6) Create imposter sections that fill in gaps:
This technique works when you have a consecutive series that can be
calculated,
like dates or numbers. You group by the date or numeric field and
then
you create several extra group footer subsections. Group
footer
"B" will print only when the gap between one group and the next is
greater
than one. It displays the missing value by adding one to
the current
group value in a formula:
{table.DateOrNumber} + 1
If you add a zero next to the formula, the two objects can be formatted
and aligned to look like the group name field and the subtotal from the
original
group footer. To allow for a gap of 2 or 3
consecutive items,
you would add group footer 'C' to display when the gap is greater than
two,
and a similar formula. Keep adding sections until you have
accounted
for the largest gap that will likely occur. Of course these
imposter
groups will not show up in cross-tabs and charts of your data.
VB Quick Reference Guide:
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 sample report viewer. It is only $16 and can be Emailed to you
within
24 hours. Follow the link below for more details:
http://www.kenhamady.com/vbref.html
Additional CALS affect
Broadcast
License limit
Crystal Enterprise v8.5 was
released
last month. In the new license, Crystal Decisions has modified
their
approach with regard to the Crystal Broadcast License (CBL). They
plan
similar changes for the CRv9 license which will be released later this
year.
The CBL is now called the RDL or Report Distribution License.
The new license for CEv8.5 starts out the same as before. It
allows
you to distribute report output to up to 50 users for free. But
in Crystal
Enterprise this is now tied to your 5 free Concurrent Access Licenses
(CALs).
You can go beyond 50 if you increase your CALs, and each CAL is worth
10
external users in your distribution. So, buying an additional 5
CALs
allows you to add 50 more external users to your broadcast, or 100 in
total.
We will have to wait and see what happens with CRv9, but they are
talking
about dropping the annual fee in favor of a one time fee.
Companies
using CRv8.5 or CEv8 are still under the old CBL license model.
My personal
opinion is that restricting the use of the static output of Crystal
Reports
is an aggressive approach to licensing. Follow the link below to
see
how the CBL provision in the current Crystal Reports license is
interpreted
by the licensing team at Crystal Decisions:
http://www.kenhamady.com/license.html
CUT Utility goes "Visual"
In the June newsletter I
mentioned the
CUT UFL for E-mailing, exporting, electronic bursting of your
reports.
Millet Software has now released a visual application that has similar
capabilities,
called Visual Cut. The original CUT UFL gives you more control
when
assembling an Email text message, but the Visual application is much
simpler
to use and includes scheduling. Read about them or download the
user
manuals at:
http://www.milletsoftware.com/
Don't just take a class, learn
from
a Crystal Expert:
Click the "Public Classes" link above for dates.
Each class is $675 per student.
Or, call for more information at (540) 338-0194.
Is there a subreport hidden in
this
report?
Have you ever opened a report
created
by someone else and wondered if they used any subreports or how many
there
were? Sometimes small subreports are tucked in the middle of
sections
and are easy to miss. Here are 3 ways to to quickly find
subreports
in any report.
1) Export to "report definition" format and search the resulting text
file
for the word 'Subreport'. It will tell you what section the
subreport
is in.
2) Right click on any subreport and select "Change Subreport
Links".
At the top of the window you will see a selector box that will list ALL
of
the subreports in the report. If can't find any subreport to
right-click
on, add a simple subreport yourself, just to open this window.
3) Drag the expanding cursor box (or lasso) to select all of the
visible
objects as a single group. Then hit the delete key. If the
objects
go away with no warning message, then there were no subreport objects
selected.
If there is a subreport you will get a warning telling you that you
won't
be able to to undo what you are about to do.
Of course options 2 and 3 should be done with a copy of the report, not
the original. You don't want to accidentally hit "Save".
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