Using summary and calculated fields in forms and reports

You can add summary fields and calculated fields to forms and reports. You can use summary fields to perform various calculations on the values in a single field. For example, you can calculate the average of all the values in a single field. You can add calculated fields to perform calculations on the values from two or more fields. For example, to calculate revenue, you can multiply a listing price field by a listing items sold field.

In this section, you'll learn about

  defining summary fields in forms and reports
  
  defining calculated fields in forms and reports
  
  using a field name in a calculation
  
  using number methods in calculated fields
  
  performing date calculations
  
  aligning a calculated field with data in a table frame
  
  controlling page report numbering using calculated fields
  
  printing calculated fields
  
For more information about summary and calculated fields, see "Reference: Summary types and scopes."

Defining summary fields in forms and reports

You can define summaries to sum, count, or average the values in a single field of a form or report. You can also find the minimum, maximum, standard deviation, and variance of values in a field. If you want a field to calculate a value based on more than one field, you will need to use a calculated field.

Before you apply a summary operator to a set of records, you must define the scope of the summary. The scope specifies the values on which you want the summary to operate. In reports, the scope is determined by the location of the summary field. For more information, see "Summary scope on single-table reports" and "Summary scope on multi-table reports."

In a report that uses tables that are linked in a one-to-many relationship, you must link the tables backwards in the data model to perform a summary operation that is relative to a page and based on a field in the detail table.

For example, if a report is based on two detail tables, you may have more than one page of detail data in the report. Because of the links, the records are automatically grouped in the detail table. Summary operations on a field within a group do not respect page breaks; they return a value for the group, not for each page on which the grouped data display. Therefore, you must create a link from the detail table to the master table. You can then group on a particular field, or even a set number of records, and have a summary value returned relative to a particular page.

To define a summary field

1	Right-click the field on which you want to perform the summary operation, and click Define field.

2	In the table's list box, double-click the field on which you want to perform the summary operation.

3	Choose a summary type from the Summary list box.

Note

  For more information about summary types, see "Summary types."
  
To perform a summary operation on a report that is based on a one-to-many relationship

1	With a form or report open in the design window, click Format  Data model.

2	Right-click the link you want to redraw, and click Unlink table.

3	Click a field in the details table, and drag it to the master table.

4	Click OK.

5	Right-click the field on which you want to perform the summary operation, and click Define field.

6	In the table's list box, double-click the field on which you want to perform the summary operation.

If a field is selected, the field name displays at the top of the Define field object dialog box.

7	Choose a summary type from the Summary list box.

Notes

  For more information about creating links in data models, see "Linking tables in data models."
  
  For more information about summary types, see "Summary types."
  
Defining calculated fields in forms and reports

Paradox lets you define calculated fields in forms and reports. A calculated field allows you to perform calculations within one field on the values of one or more fields, but it is generally used to perform calculations on two or more fields. The calculation is an expression which might have several components or terms, that must resolve to a single data value. After you add a calculated field to a form or report, you can then define the fields and calculations you want to use.

For more information about applying calculations, see "Using number methods in calculated fields," "Using a field name in a calculation," and "Performing date calculations."

For a detailed example of using a calculated field, see "Example of calculating with a field and a constant."

To define a calculated field in a form or report

1	Right-click the field on which you want to perform the calculation, and click Define field.

2	In the table's list box, double-click the field on which you want to perform the calculation.

If a field is selected, the field name displays at the top of the Define field object dialog box.

3	Enable the Calculated check box.

Note

  For information on what can be included in a calculated field, see Calculated field operators.
  
Using a field name in a calculation

You can use a field name in a calculated field. The field object in a form or report design is not the same as the actual field in the table that the field object represents and contains. This distinction is important to remember when you use field names in calculations. For example, the expression Qty * Price performs a calculation on the field objects named Qty and Price, and the expression [LINEITEM.Qty] * [LINEITEM.Price] performs a calculation on the values in the actual Qty and Price fields in the Lineitem table.

The field object in the design and the field in the table to which the form is bound are usually equivalent. However, a field object is not associated with a table; for example, it might be another calculated field. In addition, a field object might be unbound, which means it is not associated with a field in any table and not defined as a calculated or special field. A field from a table in the data model is not displayed in the design, but you must reference its field value. A field object might not have the same name as the field to which it is bound.

When you add a calculated field to a form or report, the table name that the field references is saved as part of the form or report file. If you open the form or report with a different table, the calculated fields may not be updated to reflect the change. Therefore, the calculation tries to reach information in a table (or tables) that is not bound to the document.

To use a field name in a calculation

1	Right-click the field on which you want to perform the calculation, and click Define field.

2	In the Define field object window, choose the field on which you want to perform the calculation from the table's list box.

If a field is selected, the field name displays at the top of the Define field object dialog box.

3	Enable the Calculated check box.

4	Click Copy field to place the field name in the Calculated box.

If the field name you want to use contains punctuation, you must enclose the field name in quotations.

Note

  Punctuation marks, particularly periods, are reserved characters in Paradox. Paradox uses dot notation to reference objects within forms and reports, and it uses periods (dots) in field names to invalidate this process. If the field name has periods in it, such as Total.Invoice, Paradox looks in the report or form for an object named Total containing another object called Invoice. Consequently, field names with punctuation can cause errors when you use them in a calculated field.
  
Using number methods in calculated fields

You can apply various number type methods to a calculated field. You can use number methods to do such things as round numbers to a specific number of decimal places or to the nearest whole number. You can also format a decimal value as a whole number or derive a fractional part of a numeric value. In addition, you can find the maximum or minimum of two values.

In addition, there are other number type methods that you can apply to calculated fields. For a complete listing of number methods, see "Number type methods."

To round a value to a specific number of decimal places

1	Right-click the field on which you want to round a value, and click Define field.

2	Enable the Calculated check box.

3	Type the following expression in the Calculated box:

	round([fieldname],# of digits accuracy)

Example

Suppose you have a value of $555.94 in the total invoice field of an orders table. If you type the following expression in the Calculated box

round([ORDERS.Total Invoice],1)

the value $555.90 is returned.

To round a value to the nearest whole number

1	Right-click the field on which you want to round a value, and click Define field.

2	Enable the Calculated check box.

3	Type the following expression in the Calculated box

	round([fieldname]),# of digits accuracy)

Example

Suppose you had a value of $555.94 in the total invoice field of an orders table. If you type the following expression in the Calculated box

round([Orders.Total Invoice],0)

the value $555.00 is returned.

To format a decimal value as a whole number

1	Right-click the field you want to format, and click Define field.

2	Enable the Calculated check box.

3	Type the following expression in the Calculated box

	LongInt([fieldname])

Example

Suppose you had a value of $555.94 in the total invoice field of an orders table. If you type the following expression in the Calculated box

LongInt([ORDERS.Total Invoice])

the value $555 is returned.

To derive a fractional part of a numeric value

1	Right-click the field on which you want to perform the calculation, and click Define field.

2	Enable the Calculated check box.

3	Type the following expression in the Calculated box

	fraction([fieldname])

Example

Suppose you had a value of $555.94 in the total invoice field of an orders table. If you type the following expression in the Calculated box

fraction([ORDERS.Total Invoice])

the value 0.23 is returned.

To find the maximum or minimum of two values

1	Right-click the field on which you want to perform a calculation, and then click Define field.

2	Enable the Calculated check box.

3	Type one of the following expressions in the Calculated box

	max(value1, value2)

	min(value1, value2)

Example

Suppose you have a value of $555.94 in the total invoice field of an orders table, and a value of $480.60 in the amount paid field of the same table. To find the higher of two values, type the following expression in the Calculated box

max([ORDERS.Total Invoice], [ORDERS.Amount Paid])

the value $555.94 is returned.

Performing date calculations

Paradox allows you to perform calculations with dates. You can use a calculated field to determine the difference, in days, between two dates as long as they are in the same format.

You can also create a field to calculate a date based on the current date. For example, you can print a report that includes a list of transactions that is current up to the last day of the prior month.

To find the difference, in days, between two dates

1	Right-click the field on which you want to perform the calculation, and click Define field.

2	Enable the Calculated check box.

3	Type one of the following expressions in the Calculated box:

  number(date1-date2)
  
This assumes date1 and date2 are field objects of the same type.

If date1 is 5/10/95 and date2 is 5/5/95, this example returns 5.00.

  number(date(date1)-date(date2))
  
This example assumes date1 and date2 are strings that represent the appropriate date format.

If number(date("5/10/95")-date("5/5/95")), this example also returns 5.00.

To create a field that calculates a date based on the current date

1	Right-click a field, and click Define field.

2	Enable the Calculated check box.

3	Type the following expression in the Calculated box:

today()-day(today())

Today() is today's date, and day(today()) is the number of days since the beginning of the month. When you subtract the number of days since the beginning of the month from today's date, the last day of the prior month is returned.

Aligning a calculated field with data in a table frame

If you place a calculated field in a table frame column, for example, at the bottom of a column of numbers-you must align the decimal points in the calculated field with the decimal points in the numbers in the column.

To align a calculated field with data in a column of a table frame

1	Click the field region of the table frame.

2	Hold down CTRL, and click the edit region of the calculated field.

3	Click Format  Alignment  Align right.

4	Right-click the edit region of the calculated field, and click Properties.

5	Click the Run time tab.

6	Disable the Fit width check box.

Controlling report page numbering using calculated fields

Calculated fields allow you to control the value at which page numbers begin in a report. This is useful if you want to start the report page numbering at a value other than 1.

To control report page numbering using a calculated field

In this example, a calculated field is added to a report and an expression is used to start the report page numbering at 10. The number increases by one for each following page. When you print the report, specify that the page number field matches the page background color, and make the value invisible. Instead of the usual page numbering, the controlled page numbering displays in the calculated field.

To add a page number field to a report

1	In the report design window, click the page header or footer band.

2	Click Insert  Field.

3	Right-click the field, and click Define field.

4	Choose Page number from the Special field list box.

5	Click OK.

To match the page number field to the page background color

1	Right-click the edit region of the page number field, and click Properties.

2	Click the General tab.

3	Choose Edit from the Display type list box.

4	Click the color on the color palette that matches the color of the page background.

For example, if the background color is white, click white on the color palette.

5	Click the Font page.

6	Click the color on the color palette that matches the color of the page background.

7	Click Apply.

To insert a calculated field where the page number will display

1	Click the page header or footer where you placed the page number field, and click Insert  Field.

2	Right-click the field, and click Define field.

3	Enable the Calculated check box.

4	Type the following expression in the Calculated box:

	Page_number.value + #

Page_number is the default object name given to this calculated field. # represents the number (above 1) with which you want the page numbering to begin. For example, to start numbering on page 10, replace # with 9.

Note

  For information about adding a field object to a report, see "Defining summary fields in forms and reports."
  
Printing calculated fields

Calculated fields allow you to specify certain outputs when printing a form or report. You can capitalize certain fields and records, and you can print the formulas of calculated fields. When you define a field as a calculated field, the formula becomes an ObjectPAL source. This means that the formula prints with the other ObjectPAL methods connected to the document. To view the document source, you must have a calculated field in the form or report that includes ObjectPAL code.

To print a field as all uppercase letters

1	With a form or report open in the design window, click Insert  Field.

2	Right-click a field, and click Define field.

3	Click the Data model button.

4	In the Date model dialog box, choose a table from the table list.

5	In the Define field object dialog box, enable the Calculated check box.

4	Type the following expression in the Calculated box:

	upper([tablename.fieldname])

You can also	Type

Convert all names to lowercase	lower([tablename.fieldname])

Return an initial capital letter on the name of a field	format("CC",[tablename.fieldname])

To print only the formulas of calculated fields of a form

1	Click Tools  Settings  Developer preferences.

2	Click the General tab.

3	Enable the Show developer menus check box.

4	With a form open in the design window, click View  Document source.

A temporary report that lists all the ObjectPAL code in that report is created based on a temporary table called PAL$SRC.DB.

5	Click View  Design report.

6	Click Format  Filter.

7	Type calcField in the MethodName box.

8	Click File  Print   Report.

9	Click Print.

To print only the formulas of calculated fields of a report

1	Click Tools  Settings  Developer preferences.

2	Click the General tab, and enable the Show developer menus check box.

3	Click File  Open  Report.

4	Enable the Open as a form check box.

5	Enable the Edit the form design option.

6	Choose the file and folder where the report is stored.

7	Click Open.

8	Click View  Document source.

A temporary report is created listing all the ObjectPAL code in that report. This report is based on a temporary table called PAL$SRC.DB.

9	Click File  Print.

Reference: Summary types and scopes

This section contains reference information pertaining to calculated fields and summary fields.

Summary types

Paradox includes the following summary operators:

Summary	Description

Sum	Sum of non-blank values to use with number types

Count	Number of non blank values to use with all data types

Min	Minimum value to use with alpha, number, money and date data types

Max	Maximum value to use with Alpha, number, money and date data types

Avg	Divides the total of all non null values by the number (count) of all non-null values to use with number data types

Std	Standard deviation of values to use with number data types

Var	Statistical variance of values to use with number data types

First*	First value to use with all data types

Last*	Last value to use with all data types

Prev*	Previous value to use with all data types

* These operators are not available in forms.

Summary scope on single-table reports

When you place summaries in a single-table report, location affects the scope as follows:

  Corresponding band headers and footers calculate to the same value. This means you can place a summary in either the report header or report footer and get the same result. Likewise, a calculation in either the page header or page footer yields the same result.
  
  In a table frame, the scope of the calculation is over records in the table (if it is a detail table, the scope is all records in the detail set).
  
  In a report band (either the header or the footer area), the scope of the calculation is all values contained by the report band-all records for the table.
  
  In the page band (either the header or the footer area), the scope of the calculation is all values contained by the page band-all records on the page.
  
  In a group band (either the header or the footer area), the scope of the calculation is all values contained by the group band-all records for the group.
  
In a record band, a summary behaves differently depending on the situation. In a report without a group band, Paradox performs the summary on all records in the table. In a report with a group band, Paradox performs the summary on all records in the group. In a tabular or multi-record report, if the Run Time property Show All Records of the table frame (or multi-record object) is disabled, Paradox performs the summary on the number of records that fit in the table frame or multi-record object. In this case, the table frame or multi-record object acts as if it were a band defined as a number of records.

Summary scope on multi-table reports

When you place summaries in a multi-table report, location affects the scope as follows:

Summaries on master tables

When you place a summary field on the master table of a multi-table report, the scope of the summary is the innermost group of data.

When you place a summary field in the record band of a report based on tables with a one-to-many relationship, the summary can calculate only on the current record of the master table. In this case, the current master record behaves like a group band and groups the detail records.

When Paradox works with a data model that contains one-to-many or many-to-one relationships, Paradox joins the two tables in the data model before it performs the summary, and then treats the joined tables as a single table.

Summaries on detail tables

When you place summaries on the detail table of a multi-table report, the record, page, and group band rules for summaries on single-table reports remain true. In addition, if you place a summary in the record band, Paradox performs the summary on all detail records of the current master record. If you embed a summary within a table frame or multi-record object defined as the master table, Paradox performs the summary on each record of the master table.

Summaries on unlinked tables

When you place a summary on an unlinked table in a multi-table report, the sum is performed for the whole table. 

Calculated field operators

Calculated fields can include the following operators:

  arithmetic operators +, -, *, /, and ()
  
  logical operators AND, OR, and NOT
  
  comparison operators <, >, <>, =, >=, and <=
  
  summary fields (sometimes called aggregates). For example:
  
SUM([table.fieldA] + [table.fieldB])

SUM([table.fieldA]) + SUM([table.fieldB])

  object references, such as the name of an object on the form. (Object names are always unique.)
  
  numeric constants
  
  alphanumeric strings
  
  any of the ObjectPAL mathematical, statistical, string manipulation, and date/time methods that return a single value
  
  custom ObjectPAL methods or procedures that are defined in forms or contained in libraries and accessed by forms. (Custom methods are not accessible by reports.)
  
  combinations of any of the above
  
Example of calculating with a field and a constant

Calculated fields can be used to perform calculations that include a field and a constant. For example, suppose you want to show what the selling price of line items will be if you raise all prices by 25%.

1	Create a form bound to the Lineitem table; click Form  New  Data model.

2	In the Data model dialog box, choose Lineitem.db.

3	Choose the tabular layout and the fields for the form.

The form opens in the design window.

4	Click Insert  Field.

5	Change the field label to the following:

	Selling Price increased by 25%:

(To change the field label, click three times on the field to place the cursor inside the text, and type the new label.)

6	Right-click the field object, and click Define field.

7	Enable the Calculated check box.

8	In the Calculated box, type the following:

	[LINEITEM.Selling Price] * 1.25

When you run the form for each record in the table, the Selling Price field shows the current price, and the calculated field shows the price with the proposed increase.




