- •Welcome to Seagate Crystal Reports
- •Welcome
- •Two kinds of Hands-On tutorials
- •Command, button, key, and control conventions
- •Using Seagate Crystal Reports documentation
- •Seagate Crystal Reports online Help features
- •If you need more help...
- •Installation Requirements
- •Installing Seagate Crystal Reports
- •Installing on a network workstation
- •Upgrading from a previous version
- •Quick Start
- •Subreports expand report usefulness
- •Query Designer adds ad-hoc querying capabilities
- •Parameter fields mean multi-purpose reports
- •Text objects give you text with intelligence
- •Preprinted-form reports easier than ever
- •More powerful formulas extend your capabilities
- •Web solution serves up variety of online reports
- •HTML exporting simplifies Web activities
- •New database support improves data access
- •Running totals made easy
- •Smart Navigation
- •Learning Seagate Crystal Reports
- •User’s Guide
- •Online Help
- •Books Online
- •Sample Reports
- •Glossary
- •Sample Data - CRAZE.MDB
- •Suggested learning paths
- •The application window
- •Menu bar
- •Standard toolbar
- •Supplementary toolbar
- •Format bar
- •Status bar
- •Shortcut menus
- •Cursors
- •Design Tab
- •Preview Tab
- •Other fundamentals
- •HANDS-ON (Report Design Environment)
- •How to add, delete, and move guidelines
- •How to move and position objects using guidelines
- •How to turn the grid on/off
- •How to zoom your report in and out
- •How to undo/redo activities
- •How to drill down on summarized data
- •HANDS-ON (Sections and Areas)
- •How to add, delete, move, and merge sections
- •How to split and resize sections
- •Basic report design
- •How to design a prototype
- •Concepts in reporting
- •Beyond basic reports
- •HANDS-ON (Report Creation and Design)
- •How to select data and begin creating a report
- •How to add and link multiple tables
- •How to insert database fields
- •How to insert special fields
- •How to insert a page n of N field
- •How to insert text objects
- •How to use a database field in a text object
- •How to insert a picture
- •How to select, move, and resize objects
- •How to hide parts of the report
- •HANDS-ON (Finishing Your Report)
- •How to insert page headers and footers
- •How to add a title page to your report
- •How to add summary information to your report
- •Printing considerations
- •Design solutions for printing/distributing
- •Report creation checklist for distributed reports
- •Updating printer drivers
- •Report distribution
- •HANDS-ON (Distributing Your Report)
- •How to export reports
- •How to fax a report
- •How to request reports from a web browser
- •How to specify parameter field values
- •How to log on to a database
- •How to view plain HTML reports
- •Overview
- •Getting started
- •Record Selection
- •Grouping and sorting
- •Completing the report
- •Introduction
- •Working with Arbor Essbase data
- •HANDS-ON (Reporting on OLAP data)
- •How to create a cross-tab with Essbase data
- •Using multiple sections in reports
- •HANDS-ON (Multiple Section Reports)
- •How to work with text objects
- •How to create a form letter using a text object
- •How to format objects conditionally
- •How to print conditional messages in form letters
- •How to alternate background colors for rows
- •How to eliminate blank lines
- •How to add blank lines conditionally
- •Formatting concepts
- •Absolute formatting
- •Types of formatting properties
- •Conditional formatting
- •HANDS-ON (Absolute Formatting)
- •How to add color, shading, and borders
- •How to add/edit lines and boxes
- •How to change margins
- •How to add/delete white space between rows
- •How to set page orientation and paper size
- •HANDS-ON (Conditional Formatting)
- •How to flag values that meet certain conditions
- •Record selection
- •Group selection
- •Record selection formula templates
- •HANDS-ON (Record and Group Selection)
- •How to create a record or group selection formula
- •How to use record/group selection templates
- •How to select the top or bottom N groups
- •Sorting, Grouping, and Totalling Overview
- •Creating custom groups
- •HANDS-ON (Sorting, Grouping, and Totalling)
- •How to do a single field sort
- •How to do a multiple field sort
- •How to group data
- •How to sort records within groups
- •How to summarize grouped data
- •How to subtotal grouped data
- •How to sort based on summarized group values
- •How to create multiple levels of subtotals
- •How to group data in intervals
- •How to calculate a percentage of the grand total
- •How to create group headers
- •What are formulas?
- •Other formula conventions
- •Formula syntax
- •How formulas are evaluated - Order of precedence
- •HANDS-ON (Formulas 101)
- •How to insert a formula in your report
- •How to delete formulas from your report
- •How to copy formulas from online Help
- •How to copy formulas from one report to another
- •How to create if-then-else formulas
- •How to format text with formulas
- •How to use variables in formulas
- •How to declare a variable
- •How to assign a value to a variable
- •How to conditionally assign values to variables
- •How to use an array in a formula
- •How to use a range in a formula
- •How to use semicolons in formulas
- •How to fine tune group selection formulas
- •How to fine tune record selection formulas
- •How to debug a formula
- •Introduction
- •HANDS-ON (Advanced Totalling)
- •How to maintain running totals in a list
- •How to subtotal running totals within groups
- •How to subtotal without grouping
- •How to subtotal true A to B, A to C reports
- •Parameter field objects overview
- •Multiple parameter fields
- •Parameter field considerations
- •HANDS-ON (Parameter Field Objects)
- •How to create a parameter field
- •How to use a parameter field in a formula
- •How to respond to parameter field prompts
- •How to use wildcards with parameter fields
- •How to set a report title using parameter fields
- •How to set sort order using parameter fields
- •Graphing Overview
- •Choosing a graph or chart type
- •Where to place your graph
- •Data you can graph on
- •Before you create your graph
- •HANDS-ON (Graphing)
- •How to graph on a summary or subtotal field
- •How to graph on a details field
- •How to graph on a formula field
- •How to graph on cross-tab summaries
- •How to edit graphs using PGEditor
- •How to use the underlay feature with graphs
- •OLE Objects Overview
- •Inserting OLE objects in your reports
- •Linked vs. Embedded Objects
- •The dynamic OLE menu commands
- •OLE and the Picture command
- •General OLE considerations
- •HANDS-ON (OLE Objects)
- •How OLE objects are represented in your report
- •How to use OLE - General Overview Tutorial
- •How to insert a graphic/picture as an OLE object
- •What are subreports?
- •Unlinked vs. linked subreports
- •How subreport linking works
- •HANDS-ON (Subreports)
- •How to insert a subreport
- •How to preview your subreport
- •How to combine unrelated reports using subreports
- •How to use subreports with unlinkable data
- •Cross-tab overview
- •Cross-tab components
- •HANDS-ON (Cross-Tab Objects)
- •How to create a cross-tab object
- •How to format a cross-tab
- •How to print cross-tabs that span multiple pages
- •The Crystal Query Designer
- •HANDS-ON (Queries)
- •How to create a new query
- •How to add tables to a query
- •How to link tables and specify a join type
- •How to add fields to a query
- •How to identify unique values in a query
- •How to summarize data with aggregate functions
- •How to sort records according to field values
- •How to specify records to be included in a query
- •How to select groups to be included in a query
- •How to create an SQL expression
- •How to create a query from another Crystal Query
- •How to select a query for a report
- •How to use a parameter field in a query
- •Dictionaries Overview
- •HANDS-ON (Dictionaries)
- •How to create a new dictionary
- •How to add a data file
- •How to open an SQL or ODBC data source
- •How to link multiple tables
- •How to select tables and fields for users
- •How to add/create formulas
- •How to move fields/field headings within the list
- •How to update the location of a database table
- •How to add a new field heading
- •How to add Help text
- •How to add a graphic
- •How to create sample data for users to browse
- •How to edit an existing dictionary
- •How to convert a 3.x or 4.x dictionary file
- •How to select a dictionary for a report
- •Databases Overview
- •For additional information
- •HANDS-ON (Working With Databases)
- •How to open Access queries through DAO
- •How to open Access queries through ODBC
- •How to open Access parameter queries
- •How to set up an ODBC data source
- •How to check settings for an ODBC data source
- •How to log on to an ODBC data source
- •How to add an ODBC database table to a report
- •How to log on to MS SQL Server via ODBC
- •How to log off an ODBC data source
- •How to set up an A to B, A to C link
- •How to edit an SQL query
- •How to use an ACT! database
- •How to open the NT Event Log
- •Introduction
- •Four types of data
- •Direct access database files
- •ODBC data sources
- •Crystal Query Designer files
- •Crystal Dictionary files
- •Multi-pass reporting
- •Product support
- •Web support
- •E-mail support
- •Fax support
- •Telephone support
- •Extended technical support policy
- •Product registration
- •Product return policy
- •Product replacement policy
- •Glossary
What are formulas?
In many cases, the data you want to appear on your report already exists in fields in database tables. To prepare an order list, for example, you simply need to place the appropriate fields on your report.
By placing these fields...
You get this kind of report.
Sometimes, however, you need to put data on your report that does not exist in any of the data fields. In such cases, you need to create a formula. For example, to calculate the number of days it takes to process each order, you need a formula that determines the number of days between the order date and the ship date.
322 |
Seagate Crystal Reports User’s Guide |
Seagate Crystal Reports makes it easy for you to create such a formula.
Click the INSERT FIELDS button on the standard toolbar. The Insert Fields dialog box appears.
Click the Formula Tab to activate it.
3 Click the New button.
4 Type the name you want to identify the formula.
#When you click OK in the Formula Name dialog box, the Formula Editor appears. Use the Formula Editor to create, test, and modify your formula.
For more information on the Formula Editor, see How formulas are created - An introduction to the Formula Editor, Page 327.
$To build the processing time formula, enter the Ship Date field, the Subtraction operator, and the Order Date field.
Formulas 101 |
323 |
7 Double-click the Ship Date field in the Fields list.
8 Double-click the subtraction operator in the Operators list.
9 Double-click the Order Date field from the Fields list.
The formula components are entered as you choose them.
|
|
|
Automatically checks syntax |
Inserts the highlighted list |
|
|
||
|
|
|
|
|||||
|
|
|
and accepts the formula for |
item into the formula. |
|
|
||
|
|
|
insertion into report. |
|
|
|
||
|
|
|
|
Checks the formula syntax |
|
Shows the values in a |
|
|
|
|
|
|
|
highlighted field and lets |
|
|
|
|
|
|
|
and identifies any errors. |
|
|
|
|
|
|
|
|
|
you paste values. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
NOTE: When you double-click an item in the Fields, Functions, or Operators list box, it is placed in the Formula text box complete
with the brackets, punctuation, and other syntax items the Formula Editor needs to process them correctly (see Formula
syntax, Page 334). If you enter your formula manually, using the
keyboard, you have to make certain you enter those syntax items yourself. It is generally safer and quicker to build a formula by choosing list box items.
324 |
Seagate Crystal Reports User’s Guide |
NOTE: The list of values in the Browse Field Data dialog box represents only a subset of the available records.
When you are finished, click the Check button. The program checks the formula syntax.
If the syntax is correct, click the Accept button. The Formula Editor closes and you are returned to the Insert Fields dialog box.
Click the Insert button to place the formula field object in your report.
13 When the object frame appears, place it where you want the formula to appear in your report.
You get this kind of report.
The formula subtracts the Order Date from the Ship Date and then prints the result here.
Formulas 101 |
325 |
This is just one of many of ways you can use formulas to create powerful reports.
Some of the other typical uses for formulas are:
● creating calculated fields to add to your report,
{orders detail.UNIT PRICE}*.85
«Calculates a price discounted 15%.»
● formatting text on a report,
UpperCase ({customer.CUSTOMER NAME})
«Changes all the values in the Customer Name field to uppercase. See How to format text with formulas, Page 349.»
● pulling out a portion, or portions, of a text string, and
{customer.CUSTOMER NAME} [1]
«Extracts the first letter of the customer name. See How to group based on first letter of company name, Page 301, and search for Subscript in online Help.»
● pulling out a portion of a date.
Month ({orders.ORDER DATE})
«Determines what month an order was placed.»
These examples just scratch the surface. If you have a need for specialized data manipulation, chances are you can do it with a formula.
Related Topics
Advanced Formulas, Page 345
326 |
Seagate Crystal Reports User’s Guide |
How formulas are created -
An introduction to the Formula Editor
Creating a formula in Seagate Crystal Reports is much like creating one in your favorite spreadsheet application. You can use:
● fields:
({customer.CUSTOMER LAST NAME}, {customer.LAST YEAR'S SALES}, etc.),
● numbers:
(1, 2, 3.1416),
● text:
(“Quantity”, “,”, “:”, ’your text’, etc.),
●operators:
(+, -, etc.),
●functions:
(TrimRight (x), Length (x), etc.),
●group field values:
(Average (fld, condFld), Sum (fld, condFld, “condition”), etc.), or
●other formulas:
(@GrossProfit, @QUOTA, etc.).
You combine these components into working formulas using the Formula Editor. The Formula Editor allows you to type the components into the formula directly, or, in the case of fields, functions, operators, group field values, and other formulas, to select them from lists of those items that are available.
The Formula Editor requires you to enter the various components according to a specific set of rules called syntax, such rules as:
●text enclosed in quotation marks,
●arguments enclosed in parentheses (where applicable),
●referenced formulas identified with a leading @ sign,
●and so on.
Formulas 101 |
327 |
The Formula Editor checks the syntax and helps you debug (fix) problems before you enter the formula into the report.
|
Using the Formula Editor you combine elements to create a |
|
working formula. The three list boxes at the top of the Formula |
|
Editor contain the primary formula components. |
Fields box |
The Fields box contains all the fields you can use as formula |
|
arguments. |
Fields already used in the report.
Formulas used/defined in the report.
Parameter fields used or defined in the report.
All active database fields.
HOW TO INSERT FIELDS IN YOUR FORMULA
You can insert fields in your formulas in two ways:
1. by placing the I-beam cursor where you want the field to appear in the Formula text box of the Formula Editor, setting the insertion point and then double-clicking the desired field in the Fields list box, or
328 |
Seagate Crystal Reports User’s Guide |
2.by placing the I-beam cursor where you want the field to appear in the Formula text box and then typing it in manually.
NOTE: Be sure to review the correct syntax for using fields in your formulas. If you do not include the file name, leave out the separating period, or fail to surround the field in braces, the program will generate a Formula Compiler Error message detailing your error. See Formula syntax, Page 334, and search for
Error Messages and Formula Compiler Warnings in Seagate Crystal Reports online Help.
Functions box The Functions box lists the dozens of functions that are included with Seagate Crystal Reports. These functions are prebuilt procedures that return values. They perform such calculations as average, sum, count, etc. All functions available are listed in this box with their arguments and arranged by their use.
Function name.
Arguments you must supply.
HOW TO INSERT FUNCTIONS IN YOUR FORMULA
You can insert functions in your formulas in the same manner as fields:
●via the Functions box, or
●by typing them in manually.
See HOW TO INSERT FIELDS IN YOUR FORMULA, Page 328.
NOTE: Be sure to review the correct syntax for using functions in your formula. If a function has required arguments, all arguments must also be entered. If any required arguments are not entered, the program will generate a Formula Compiler Error message detailing the error. See Formula syntax, Page 334, and search for
Error Messages and Formula Compiler Warnings in Seagate Crystal Reports online Help.
Formulas 101 |
329 |
Operators box
The Operators box lists the “action verbs” you can use in your formulas. Operators include such things as + (add), / (divide), -x (negate), etc. Search for Operators and Variables in Seagate Crystal Reports online Help.
Model showing how operator is used.
Operator name.
Formula text box
HOW TO INSERT OPERATORS IN YOUR FORMULA
You can insert operators in your formulas in the same manner as fields and functions:
●via the Operators box, or
●by typing them in manually.
See HOW TO INSERT FIELDS IN YOUR FORMULA, Page 328.
The Formula text box is where you actually enter the formula.
You can:
●enter the formula manually, using the keyboard,
●choose your formula components from the Fields, Functions, and Operators list boxes, or,
●combine the two methods, choosing some of the components from the list boxes and entering other parts of the formula manually.
330 |
Seagate Crystal Reports User’s Guide |