Formulas 2

[Ethelk2044]

I need to create a formula that will count the number if times an item happens. For example if the class = Windows XP it would add a 1 to the field. It would total the field depending on how may items a person is trained on that particular class. Any suggestions.


Class Trained
AS400 0
WindowsXP 1
Windows 2000 1
Citrix 0
Total 4

 

[JC442]

Ethelk2044,
Using count and the conditional field may help. Here is the help for count:
Count (fld), Count (fld, condFld), Count (fld, condFld, cond), Count (x)
Basic and Crystal syntax.

Arguments
fld is any valid database or formula field that can be evaluated by the function.
condFld is a field used to group the values in fld by.
cond is a String indicating the type of grouping for condFld. You only specify this argument when condFld is a Date, Time, DateTime or Boolean field. For more information on the valid strings for this argument, see Conditions for summary functions.
x is an array of values that can be evaluated by the function being used.
Returns
Fractional Number

Action
Enables you to count the values that appear in your report (for a specified field). For example:

If a sales report includes all orders made and the amount of each order, you can compute the total number of orders that appear on the report (a grand total count). For information on this kind of counting, see SummaryFunction (fld).
If you break orders into groups (for example, orders grouped by the state that they come from), you can compute the number of orders per group (in this case, per state). For information on this kind of counting, see SummaryFunction (fld, condFld).
If you break orders into date or Boolean groups (for example, orders grouped by the month in which they were placed), you can compute the number of orders per group based on a particular change in the date or Boolean field (in this case, per month). For information on this kind of counting, see SummaryFunction (fld, condFld, cond).
If you specify a set of individual values, you can compute the number of values in the set. For information on this kind of counting, see Array summary functions (x).
Examples
The following examples are applicable to both Basic and Crystal syntax:

Count({orders.AMOUNT}, {orders.CUSTOMER ID})

This formula counts the number of orders in each group of orders in the Amount field (the total orders for each month). The orders are separated into groups whenever the value in the Customer ID field changes.

Count({orders.ORDER AMOUNT}, {orders.ORDER DATE}, "monthly")

Counts the number of orders in each group of orders in the Amount field (the total orders for each month). The orders are separated into groups whenever the value in the Date field changes to a new month.

The following examples are applicable to Crystal syntax:

If Count({orders.ORDER ID}) >= 100 Then


"Congratulations on meeting your quota!"


Else


""


Prints the congratulatory message if the number of orders is 100 or more, and prints nothing if the number of orders is less than 100.

Count([1,2,3,4,5])

Returns 5. Counts the total number of values in the array.

Note: Using this function in a formula forces the formula to be evaluated at print time.

For more information on evaluation time considerations, see Evaluation Time functions.

 

[kenhamady]

You can't do a logical condition inside a summary function. The condition field is only for a group "condition". To do logical conditions you should do something like this:

if {class} = "Windows XP"
then 1
else 0


Now do a Grand Total sum of this field for your first total. Do another formula and grand total for each class. Or you could do a cross-tab, but that won't show you the zero value classes.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com