6

7

3

4

5

The most basic form of a running total is a single running total maintained throughout a list. In this tutorial you create this kind of report by setting up a running total for a list of order amounts.

NOTE: Running total fields are prefixed by the # sign.

1 To get started, create a report using the sample database xtreme.mdb, link the Customers and Orders tables, and then place the following fields from left to right in the Details section:

{customer.CUSTOMER NAME} {orders.ORDER ID} {orders.ORDER AMOUNT}

2 Choose RUNNING TOTAL FIELD from the Insert Menu. The Insert Fields dialog box appears.

Select sum from the Type of summary drop-down list.

In the Evaluate section of the dialog box, click the On change of field option, and select {orders.ORDER ID} as the “change of” field. The running total will execute each time this field changes.

Click NEW. The Create Running Total Field dialog box appears. Enter the name "TotalOrders" in the Running Total Name text box.

Select {orders.ORDER AMOUNT} from the Available Tables and Fields list box, and use the first arrow button to enter that field in the Field to summarize text box.

Running Total

2 = 0 + 2

6 = 2 + 4

14 = 6 +8

HANDS-ON (Running Totals)

How to maintain running totals in a list

Running totals are totals that are displayed generally on a record by record basis. They total all records (in the report, in the group, etc.) up to and including the current record. For example, if your first three records have values of 2, 4, and 8, the running total for each of the three records would be as follows:

Values

2

4

8

Running Totals

343

8In the Reset section of the dialog box, click the Never option (this gives you a running total that never resets; that is, the running total continues throughout the report).

9Click OK to save the running total field. The program returns you to the Insert Fields dialog box.

10Insert the running total field in the Details section of the report, just to the right of {orders.ORDER AMOUNT}.

The value in each record is added to the sum of the previous value in the report.

The total continues, unbroken, throughout the running list.

Related Topics

How to create a running total for a group, Page 345

Introduction to Reporting, Page 85

344	Seagate Crystal Reports User’s Guide

How to create a running total for a group

Another common use for running totals is tallying items in a group. The running total starts with the first item in the group and ends with the last. Then it starts all over again for the next group, then the next, and so on.

In this tutorial, you will create a report that:

●maintains a running total of customer orders,

●groups customer orders and resets the running total for each group, and

●displays the subtotal for each order (the last running total for that order).

To set up this report, create a running total that resets for each new group so that you have a subtotal for each group. This subtotal will take the last value in the running total field and display that value on the report (for example, if the numbers 2, 4, and 8 were tallied by the running total, the subtotal will be 14). This will give you a running total and a subtotal for each group.

1To get started, create a report using the sample data, xtreme.mdb. Link the Customers and Orders tables and place the following fields from left to right in the Details section:

{customer.CUSTOMER NAME} {orders.ORDER ID} {orders.ORDER AMOUNT}

2Group on the {customer.CUSTOMER NAME} field.

3Choose RUNNING TOTAL FIELD from the Insert Menu. The Insert Fields dialog box appears.

4Click NEW. The Create Running Total Field dialog box appears.

5Enter the name "GroupRunningTotal" in the Running Total Name text box.

6Select {orders.ORDER AMOUNT} from the Available Tables and Fields list box, and use the first arrow button to enter that field in the Field to summarize text box.

7Select sum from the Type of summary drop-down list.

8In the Evaluate section of the dialog box, click the For each record option.

9In the Reset section of the dialog box, choose to reset on change of Group #1.

Running Totals

345

10Click OK to save the running total field. The program returns you to the Insert Fields dialog box.

11Place the running total field in the Details section of your report, just to the right of the {orders.ORDER AMOUNT} field.

How to create a conditional running total

There may be times when you have a list of ungrouped values, and you only want to subtotal some of the values in the list. For example:

●you have a list that contains both Canadian and U.S. customers,

●you want to keep customer records sorted alphabetically based on customer name,

●you do not want to break the data into groups based on the country, but

●you want a total of the values from just the Canadian records, and

●you also want a total of the values from just the U.S. records.

To accomplish this, create two running totals, one to keep a running total of the U.S. records, and one to keep a running total of the Canadian records.

●USTotal

Maintains a running total of the U.S. records.

●CanadaTotal

Maintains a running total of the Canadian records.

1To get started, create a report using the sample data, xtreme.mdb. Place the following fields from left to right in the Details section:

{customer.CUSTOMER NAME} {customer.COUNTRY} {customer.LAST YEAR'S SALES}

2Sort the records based on the {customer.CUSTOMER NAME} field.

3Choose RUNNING TOTAL FIELD from the Insert Menu. The Insert Fields dialog box appears.

4Click NEW. The Create Running Total Field dialog box appears.

5Enter the name "USTotal" in the Running Total Name text box.

6Select {Company.LAST YEAR’S SALES} from the Available Tables and Fields list box, and use the first arrow button to enter that field in the

Field to summarize text box.

346	Seagate Crystal Reports User’s Guide

7Select sum from the Type of summary drop-down list.

8In the Evaluate section of the dialog box, click the Formula option button and then click the Formula button. The Running Total Condition Formula Editor appears.

9Enter the following formula in the Formula text box:

{Customer.Country} = “USA"

This tells the program to evaluate the running total each time it comes to a record where {Customer.COUNTRY} is equal to "USA." The running total will ignore all other records (such as records for Canada).

10In the Reset section of the dialog box, click the Never option.

11Click OK to save the running total field. The program returns you to the Insert Fields dialog box.

12Place the running total field in the Details section of your report.

13Now create the CanadaTotal running total field using the process outlined in steps 3-11. The only difference is that this time you will set the Evaluate formula to:

{Customer.Country} = "Canada"

14When you are finished, place the #CanadaTotal field in the Details section of your report, just to the right of the {Customer.LAST YEAR’S SALES} field.

15Place the two running total fields you created in the Report Footer section of your report.

Your report should look similar to the following:

NOTE: This sample report has been designed to illustrate concepts only, not the actual look of the finished report.

Running Totals

347

How create a running total in a one to many linking relationship

The term A to B, A to C report is used to refer to any report in which a primary table is linked to two lookup tables (see Methods of looking up tables (direct access databases), Page 524). However, in a true A to B, A to C link, a single field in the primary table is used to link to both of the lookup tables.

In a true A to B, A to C relationship, one of the two lookup tables usually has more records than the other. If you group these records based on a field in the primary table, values in the smaller lookup table are repeated for each value in the larger lookup table. The following table shows data for an A to B, A to C relationship. The Customer table is linked to the Credits table and again to the Orders table. Notice that Jones has only one Credit ID, but the credit and its amount are repeated, once for each of Jones’ two orders.

	Name	Credit ID	Credit	Order ID	Order
			Amount		Amount
	Jones	1	-10.00	1	10.00
	Jones	1	-10.00	2	12.00
	Smith	2	-23.00	3	20.00

348	Seagate Crystal Reports User’s Guide

	Name	Credit ID	Credit	Order ID	Order
			Amount		Amount
	Smith	3	-45.00	4	30.00

In this example, a standard subtotal is used for both the Order Amount field and the Credit Amount field. Thus, Jones’ single credit is counted twice, and the subtotal displays an inaccurate value of -20.00.

	Name	Credit ID	Credit	Order ID	Order
			Amount		Amount
	Jones	1	-10.00	1	10.00
	Jones	1	-10.00	2	12.00
			-20.00		22.00
	Smith	2	-23.00	3	20.00
	Smith	3	-45.00	4	30.00
			-68.00		50.00

NOTE: Notice that the total Credit Amount for Jones is incorrect.

This problem also occurs in the Order Amount field if Jones had two different Credit Amounts and only one Order Amount. Avoid this problem by creating a running total for each field you want subtotaled.

1To get started, create a report using the sample data, ORDRCR.MDB. Place the following fields from left to right in the Details section:

{cust.NAME} {credits.CREDIT ID} {credits.CREDIT} {orders.ORDER ID} {orders.ORDER AMOUNT}

NOTE: The sample data in ORDRCR.MDB has been specially designed to demonstrate a true A to B, A to C link. Use the Visual Linking Expert to make sure that the {credits.CUST} field is linked to both the {orders.CUSTOMER} and {cust.NUMBER} fields.

2In the Design Tab, highlight the {orders.ORDER AMOUNT} field and insert a subtotal, grouping on the {cust.NAME} field.

3Choose RUNNING TOTAL FIELD from the Insert Menu. The Insert Fields dialog box appears.

4Click NEW. The Create Running Total Field dialog box appears.

Running Totals

349

5Enter the name "CreditsSubtotal" in the Running Total Name text box.

6Select {credits.CREDIT} from the Available Tables and Fields list box, and use the first arrow button to enter that field in the Field to summarize text box.

7Select sum from the Type of Summary drop-down list.

8In the Evaluate section of the dialog box, click the On change of field option and choose the field {credits.CREDIT ID}.

NOTE: For customer Jones there is only one Credit ID, so the running total will take the credit amount of $10.00.

9In the Reset section of the dialog box, click the On change of field option and choose the field {cust.NAME}.

NOTE: The Running Total resets for the second customer (Smith). For Smith there are two Credit ID’s (2 and 3), so the running total sums the credit amounts ($23.00 and $45.00).

10Click OK to save the Running Total field. The program returns you to the Insert Fields dialog box.

11Place the running total in the Group Footer section of the report.

Related Topics

Introduction to Reporting, Page 85

350	Seagate Crystal Reports User’s Guide

16	Parameter Fields

What you will find in this chapter...

Parameter field objects overview, Page 352

Multiple parameter fields, Page 353

Parameter field considerations, Page 353

HANDS-ON (Parameter Field Objects), Page 354

Parameter Fields

351

Parameter field objects overview

Parameter fields are fields that prompt you to specify a value each time you refresh the data in a report. When you supply a value, the program runs the report using that value. By using parameter fields in formulas, selection formulas, and in the report itself, you can create a single report that you can modify whenever your needs change. Parameter Fields can also be used in subreports and compiled reports. See Introduction to Formulas, Page 291.

For example, you may create a report in which you want to include only California records. Without parameter fields, you could enter a record selection formula similar to this:

{customer.REGION} = “CA”

This formula tests the records in the {customer.REGION} field to see if they contain the value “CA.” If a record contains that value, the formula prints that record in the report. If a record does not contain the value, then the formula rejects that record. The report runs exactly the way you want it to - if you always want to run reports using only California records. However, to run reports using records from other states, you have to edit the formula and hard code your changes. For example, if you want to run the report using Arizona records, you would have to change “CA” to “AZ” in the Record Selection Formula Editor or the Select Expert. See

How to set up record selection using the Select Expert, Page 238.

Using a parameter field in place of the state field value allows you to make changes “on the spot,” without hard coding a new value. Here is how it works:

1Let’s say you set up a parameter field by choosing the PARAMETER FIELD command from the Insert Menu. For the purpose of this example, call this parameter field Region.

2Then choose the parameter field in their selection formula, instead of using the formula:

{customer.REGION} = “CA”, create a formula similar to this:

{customer.REGION} = {?Region}

NOTE: {?Region} is the parameter field created in Step 1. The program uses the {?ParameterFieldName} format for parameter fields in formulas and Experts.

352	Seagate Crystal Reports User’s Guide

3Whenever you refresh the data in the report, the program prompts them to either supply a value for the parameter field or accept the default value. The program selects the records for the report using the value specified.

With parameter fields, you can create a single report that can be customized quickly to meet a variety of needs.

Multiple parameter fields

Formulas and record selection formulas can contain multiple parameter fields. When this is the case, the program prompts you for each parameter before it refreshes the data. So, you could use a selection formula similar to the following:

{customer.REGION} = {?Region} AND {customer.LAST YEAR’S SALES} <= {?Sales}

This particular formula will prompt you first for the region you want to report on, and then for the amount against which you want to compare last year’s sales. This means that the Enter Parameter Values dialog box will contain multiple tabs, one for each parameter field.

When you supply the parameter values, the selection formula will then limit the report to records in which the region is equal to the value you specified for the {?Region} parameter field, and in which last year’s sales is equal to the value you specified for the {?Sales} parameter field.

Parameter field considerations

There are a number of things to keep in mind when working with parameter fields:

●A parameter field does not have to be placed in a report in order to be used in a record or group selection formula. You just create the parameter field and then enter it in your formula as you would any other field.

●The program supports parameter fields in the following data types: String, Number, Currency, Boolean, Date, Time, and DateTime.

●Parameter field prompting text can be up to two lines long with approximately 40-50 characters per line (depending on character width, up to the 255 character limit). The program performs automatic word wrap on prompts more than one line long.

Parameter Fields

353