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
     vmonth_to_date currency =  (IFEQ(imon(transdate), imon(.#date),
                                (.vmonth_to_date + invoicetotal),
     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 /
     vco text = company in customer where custid = custid
     vmtx_print = (IFEQ(.vmonth_to_date,0,'', format(.vmonth_to_date,
     vytd_print = (IFEQ(.vyear_to_date,0,'',format(.vyear_to_date,
     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 
     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
     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.