=====================================================================
Comparing Crystal Reports and R:BASE Reports
=====================================================================
Product: Crystal Reports Version: 2.0
=====================================================================
Area: Reports Catalog: Forms, Reports, and Labels
=====================================================================
Microrim recently announced and started shipping Crystal Reports for
R:BASE, a Windows report writer. It offers many of the same reporting
features available in the R:BASE report writer but in a Windows
environment. Crystal has features that are not available in the
R:BASE report writer; conversely, the R:BASE report writer has
features that are not available in Crystal. To gain a better
understanding of how the two differ and how they are the same let's
look at creating the same report in Crystal and in R:BASE.
We'll create a report that displays month-to-date and year-to-date
sales by customer. The report also includes the percentage the
monthly amount is of the year-to-date total. The data for the report
is drawn from the Transmaster and Customer tables in the Concomp
sample database.
The data is grouped by custid. The report title is printed in a Page
Header section, the "detail" for each customer is printed in a group
footer section. Blanks are printed where there is no monthly total
for a customer. The currency values are printed as whole dollars
only. The date is formatted to display fully spelled out.
Creating the group
In R:BASE, a new custom report automatically has a report header,
page header, detail, page footer and report footer sections. Sections
that aren't needed are deleted from the report layout. The column to
group by is selected by choosing Layout|Create breakpoints. Once a
breakpoint has been defined, break header and footer sections can be
added to the report using Go to on the menu or the F7 and F8 function
keys. Only a break footer section, F1, is added to the report layout
for this report.
In Crystal, a new report automatically has page header, detail, and
page footer sections. Sections can't be deleted, instead they are
formatted to hide when printing. The group is specified by choosing
Insert|Group Section. The group header and footer sections are
automatically added to the report layout. Both sections are always
added. Fields are located in the group footer section for this report
and the group header section is hidden.
For this example, select Custid as the breakpoint or grouping column.
In both products, defining a breakpoint or group lets you do
sub-totals and print out data once for each group.
Defining the variables
Crystal and R:BASE are quite different here. In R:BASE we can choose
Variables from the Report building menu and define all our variables
at one time, or we can define them as we locate fields. Defined
variables do not need to be associated with a field located on the
report. All defined variables are shown at one time. When variables
are listed, both the result variable and the expression are shown.
In Crystal, each variable must be associated with a located field.
We can only define a variable or expression (called a formula in
Crystal) when locating a field. Only one variable can be shown at a
time, and only the formula is displayed. The associated field name
is shown on the top bar of the Edit formula window. A Crystal formula
can be more complex than an R:BASE variable definition, and Crystal
has many built in functions that make defining formulas easy.
Here are the variables we need to define in our R:BASE example
report.
vmonth_to_date currency = (IFEQ(imon(transdate), imon(.#date),
(.vmonth_to_date + invoicetotal),
.vmonth_to_date))
vyear_to_date currency = (sum(invoicetotal) in transmaster
where custid = custid and
transdate <= .#date
vpercent integer = (IFEQ(.vyear_to_date,0,0,(nint((.vmonth_to_date /
.vyear_to_date)*100))))
vco text = company in customer where custid = custid
vmtx_print = (IFEQ(.vmonth_to_date,0,'', format(.vmonth_to_date,
'[>]999,999')))
vytd_print = (IFEQ(.vyear_to_date,0,'',format(.vyear_to_date,
'[>]999,999,999')))
This is what the variables do:
vmonth_to_date - accumulates the month to date total for the customer.
If the month of the transaction date is the same as
the month of the system date, the invoicetotal
amount is added in.
vyear_to_date - looks up the year to date total for each customer.
vpercent - calculates the percentage the monthly total is of the
yearly total. It first checks to see if the year to
date total is zero, if it is the result is set to
zero. This prevents errors from a divide by zero
situation.
vco - looks up the company name
vmtd_print - formats the month to date total for printing so zero
values will print as blanks.
vytd_print - formats the year to date total for printing so zero
values will print as blanks
In Crystal we use the built-in MonthToDate and YearToDate functions.
Two formulas are needed to calculate the month to date and year to
date. Choose Insert|Formula Field or click on the function button.
Crystal prompts for a formula field name and then places you in the
formula editor.
Define two formula fields, mtd and ytd, in Crystal. Note that Crystal
always prefaces columns with an alias name. The default alias is the
table name.
mtd =
if {transmaster.transdate} in MonthToDate then
{transmaster.invoicetotal} else 0
ytd =
if {transmaster.transdate} in YearToDate then
{transmaster.invoicetotal} else 0
Crystal automatically compares the transaction date to the current
system date and calculates if it is in the current month or the
current year. These formula fields are located in the detail section.
They aren't doing any summing, they are just determining if the date
is in the required range. These are like the first two arguments of
the IFEQ functions defined in R:BASE for the month to date and year
to date. The summing expressions are added later.
The percent formula, pct, is defined as
if Sum ({@ytd},{transmaster.custid} ) = 0 then 0 else (Sum
({@mtd},{transmaster.custid}) / Sum ({@ytd},{transmaster.custid} ))
* 100
As with the R:BASE expression for the percent, the year to date total
is first checked to see if it is zero. Then the sums are divided and
multiplied by 100 to calculate a percentage. In Crystal, you need to
use the conditional SUM and tell Crystal the grouping column in the
expression itself.
Formula names in Crystal are automatically prefaced with an @ to
differentiate them from columns. After a formula is defined, the
field must be placed on the report.
Locating fields
For the example report in R:BASE, we locate the system variable,
#DATE, in the page header section along with text for the column
headings. The text is typed directly onto the report layout.
Use a picture format of '[<]MMM+ DD, YYYY' with #DATE . This left
justifies the date and spells out the month name.
The other fields are located on the break footer line: the column
custid and the variables, vco, vmtd_print, vytd_print and vpercent.
Only vpercent has a picture format, use '990%'. The variables
vmtd_print and vytd_print are formatted for printing as part of their
variable definition. In R:BASE, to print blanks instead of zeros you
need to define variables.
In Crystal, the fields are located as the formulas are defined. @ytd
and @mtd are located in the detail section. They were not defined to
actually accumulate the customer totals, they just determined if the
value was in the range. To accumulate the totals, select @mtd, then
Insert|Subtotal. Crystal automatically creates a variable to sum the
values and places the sum in the group footer section. Do the same
thing for @ytd. Format the sub-total locations to suppress zeros when
printing and to display whole dollars only.
Locate @pct in the group footer section and format right justified
with rounding at 1 and decimals at 1.. The percent sign is placed as
a text field just to the right of the @pct location.
Choose Insert|Print Date Field and locate it in the page header
section. Format the date to spell out the month. The other text is
placed as text fields in the page header. Placing text as fields
rather than typing it directly into the report layout gives you more
control over formatting and placement.
Choose Format|Section and hide both the detail and group header
sections. The fields located in the detail section do not print, but
the formulas still calculate and determine if the date is in the
range.
Lookups
There's one item missing from our Crystal report that we have in our
R:BASE report - the company name.
In R:BASE, values are looked up from other tables by defining a
variable. A report can only have one table associated with it, to
print values from other tables lookup variables are defined. An
alternative to lookups in R:BASE is to define a view that contains
all the needed columns. Our example report is just getting one column
value so a lookup is defined in the report, the vco variable.
Crystal formulas don't allow us to lookup column values from other
tables. But a Crystal report is not limited to just one table. To do
lookups, add the lookup table to the report. Choose, Database|Add
Table to Report. You are prompted to select the table from the list
of tables and views in the database. After selecting the table to
add, you define the link between the tables. The columns from the
lookup table are available for placing on the report. Add the
Customer table to the report and link with the Transmaster table by
the column custid. Locate the company column from the Customer table
in the group footer section.
So you see, Crystal Reports and R:BASE are similar yet different. You
will find uses in your applications for reports designed by Crystal
as well as regular R:BASE reports. We didn't use Crystal's additional
formatting features in this example, but you can easily include
colors and different fonts in your report.