Array

The Array functions are all used to summarize field data in several different ways.

• Any()
• Average()
• Count()
• DistinctCount()
• Every()
• Maximum()
• Minimum()
• PopulationStdDev()
• PopulationVariance()
• StdDev()
• Sum()
• UBound()
• Variance()

Any()

Function

• Any(array_variable);
• Any(field_variable);

Description

This function is used to check whether there is at least one element in the argument is true. If there is, return true; if there isn't, return false.

Arguments

• field_variable
  field_variable is a special variable in formula. It refers to a group of values of a database field or of a formula field.
• array_variable
  array_variable refers to an array.

Return type

Returns a Boolean value.

Examples

• Boolean x = Any([false, false, false, true, false, false])
• Boolean x = Any(@isExcellent)
• Boolean x = Any(@formula1) (@formula is a formula which returns a Boolean array.)
• Boolean x = Any(@@ctf1) (@@ctf1 is a crosstab formula which returns a Boolean array.)

Average()

Function

• Average(array_variable);
• Average(field_variable);
• Average(field_variable, groupby);

Description

This function is used to return the average value of a group of values referred to by the first argument. The second argument, if there is one, returns the group by field name.

Arguments

• field_variable
  field_variable is a special variable in Formula. It refers to a group of values of a database field or of a formula field. Whatever source they come from, database or formula, they should be of Number or Currency data type.
• array_variable
  array_variable refers to an array whose element is of the Number or Currency value.
• groupby
  groupby can be a constant String which indicates the groupby field name or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Return type

The result type relies on the first argument. It can be of the Number or Currency data type, if the data type of each element in the first argument is also of the Number or Currency data type respectively.

Examples

• Number x = Average([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
• Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0]; Number y = Average(x)
• Currency x = Average([$1, $2, $3.0, $4.5, $45, $67])
• Currency x[] = [$1, $2, $3.0, $4.5, $45, $67]; Currency y = Average(x)
• Number x = Average(@dbfield)
• Number x = Average(@formula)
• Number x = Average(@dbfield, "group_field")
• Number x = Average(@dbfield, @"group_field")
• Number x = Average(@dbfield, @parameter)
• Currency x = Average(@dbfield)
• Currency x = Average(@formula)
• Currency x = Average(@formula, "group_field")
• Currency x = Average(@formula, @"group_field")
• Currency x = Average(@formula, @parameter)

Count()

Function

• Count(array_variable);
• Count(field_variable);
• Count(field_variable, groupby);

Description

This function is used to count the number of a group of values referred to by the first argument. The second argument, if there is one, returns the group by field name.

Arguments

• field_variable
  field_variable is a special variable in Formula. It refers to a group of values of a database field or of a formula field.
• array_variable
  array_variable refers to an array.
• groupby
  groupby can be a constant String to indicate the groupby field name, or a field variable on which the grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Return type

Returns a Number value.

Examples

• Number x = Count([1.0, 2.0, 3.0, 4.5, 45.0, 67.0]).
• Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
Number y = Count(x).
• Number x = Count(["str1","str2","str3","str4","str5","str6"]).
• Number x = Count(@dbfield).
• Number x = Count(@formula).
• Number x = Count(@dbfield, "group_field").
• Number x = Count(@formula, @"group_field").
• Number x = Count(@dbfield, @parameter).

DistinctCount()

Function

• DistinctCount(array_variable);
• DistinctCount(field_variable);
• DistinctCount(field_variable, groupby);

Description

This function is used to count the number of distinct values referred to by the first argument together. The second argument, if there is one, gives the group by field name.

Arguments

• field_variable
  field_variable is a special variable in Formula, and refers to a group of values from a database or formula field.
• array_variable
  array_variable refers to an array whose element should be a Number.
• groupby
  groupby can be a constant String to indicate the groupby field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Return type

Returns a Number value.

Examples

• Number x = DistinctCount([1.0, 1.0, 3.0, 4.5, 4.5, 67.0]) // result is 4
• Number x[6] = [1.0, 1.0, 3.0, 4.5, 4.5, 67.0];
Number y = DistinctCount(x)
• Number x = DistinctCount(["str1","str1","str3","str4","str4","str6"])}
• Number x = DistinctCount(@dbfield)
• Number x = DistinctCount(@formula)
• Number x = DistinctCount(@dbfield, "group_field")
• Number x = DistinctCount(@formula, @"group_field")
• Number x = DistinctCount(@dbfield, @parameter)

Every()

Function

• Every(array_variable);
• Every(field_variable);

Description

This function is used to check whether each element in the argument is true. If yes, return true; if not, return false.

Arguments

• field_variable
  field_variable is a special variable in formula. It refers to a group of values of a database field or of a formula field.
• array_variable
  array_variable refers to an array.

Return type

Returns a Boolean value.

Examples

• Boolean x = Every([false, false, false, true, false, false])
• Boolean x = Every(@isExcellent)
• Boolean x = Every(@formula1) (@formula is a formula which returns a Boolean array.)
• Boolean x = Every(@@ctf1) (@@ctf1 is a crosstab formula which returns a Boolean array.)

Maximum()

Function

• Maximum(array_variable);
• Maximum(field_variable);
• Maximum(field_variable, groupby);

Description

This function is used to pick up the maximum value from a group of values referred to by the first argument. The groupby argument specifies the group by field name.

Arguments

• field_variable
  field_variable is a special variable in Formula, and refers to a group of values from a database or formula field.
• array_variable
  array_variable refers to an array whose element should be Number.
• groupby
  Groupby can be a constant String to indicate the groupby field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Return type

The result type relies on the first argument. It may be Number, Currency, String, Date, Time, DateTime, or Boolean if the datatype of each element in the first argument is Number, Currency, String, Date, Time, DateTime or Boolean respectively.

Examples

• Number x = Maximum([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
• Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
Number y = Maximum(x)
• Number x = Maximum(@dbfield)
• Date x = Maximum(@formula)
• Time x = Maximum(@dbfield, "group_field")
• DateTime x = Maximum(@dbfield, @"group_field")
• String x = Maximum(@dbfield, @parameter)
• Currency x = Maximum(@dbfield)
• Boolean x = Maximum(@formula)
• Date x = Maximum(@formula, "group_field")
• Currency x = Maximum(@formula, @"group_field")
• Currency x = Maximum(@formula, @parameter)
• String x = Maximum(['1', '2', '3.0', '4.5', '45', '67'])
• Date x = Maximum([toDate(1998, 1, 1), toDate(1997, 7, 1), toDate(1999, 1, 16)])

Minimum()

Function

• Minimum(array_variable);
• Minimum(field_variable);
• Minimum(field_variable, groupby);

Description

This function is used to pick up the minimum value from a group of values referred to by the first argument together. The second argument, if there is one, gives the group by field name.

Arguments

• field_variable
  field_variable is a special variable in Formula, and refers to a group of values from a database or formula field.
• array_variable
  array_variable refers to an array whose element should be a Number.
• groupby
  groupby can be a constant String to indicate the groupby field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Return type

The result type relies on the first argument. It may be Number, Currency, String, Date, Time, DateTime, or Boolean if the data type of each element in the first argument is Number, Currency, String, Date, Time, DateTime or Boolean respectively.

Examples

• Number x = Minimum([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
• Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
Number y = Minimum(x)
• Number x = Minimum(@dbfield)
• Date x = Minimum(@formula)
• Time x = Minimum(@dbfield, "group_field")
• DateTime x = Minimum(@dbfield, @"group_field")
• String x = Minimum(@dbfield, @parameter)
• Currency x = Minimum(@dbfield)
• Boolean x = Minimum(@formula)
• Date x = Minimum(@formula, "group_field")
• Currency x = Minimum(@formula, @"group_field")
• Currency x = Minimum(@formula, @parameter)
• String x = Minimum(['1', '2', '3.0', '4.5', '45', '67'])
• Date x = Minimum([toDate(1998, 1, 1), toDate(1997, 7, 1), toDate(1999, 1, 16)]

PopulationStdDev()

Function

• PopulationStdDev(array_variable);
• PopulationStdDev(field_variable);
• PopulationStdDev(field_variable, groupby);

Description

This function is used to find the population standard deviation of a group of values referred to by the first argument. The second argument, if there is one, gives the group by field name.

Arguments

• field_variable
  field_variable is a special variable in Formula, and refers to a group of values from a database or formula field. Whatever kind of source the data comes from, database or formula, it should be of the Number data type.
• array_variable
  array_variable refers to an array whose element should be Number value.
• groupby
  groupby can be a constant String to indicate the groupby field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Return type

Returns a Number value.

Examples

• Number x = PopulationStdDev([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
• Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
Number y = PopulationStdDev(x)
• Number x = PopulationStdDev(@dbfield)
• Number x = PopulationStdDev(@formula)
• Number x = PopulationStdDev(@dbfield, "group_field")
• Number x = PopulationStdDev(@dbfield, @"group_field")
• Number x = PopulationStdDev(@dbfield, @parameter)

PopulationVariance()

Function

• PopulationVariance(array_variable);
• PopulationVariance(field_variable);
• PopulationVariance(field_variable, groupby);

Description

This function is used to find the population variance of a group of values referred to be the first argument. The second argument, if there is one, gives the group by field name.

Arguments

• field_variable
  field_variable is a special variable in Formula, and refers to a group of values from a database or formula field. Whatever kind of source the data comes from, database or formula, it should be of the Number data type.
• array_variable
  array_variable refers to an array whose element should be Number value.
• groupby
  groupby can be a constant String to indicate the groupby field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Return type

Returns a Number value.

Examples

• Number x = PopulationVariance([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
• Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
Number y = PopulationVariance(x)
• Number x = PopulationVariance(@dbfield)
• Number x = PopulationVariance(@formula)
• Number x = PopulationVariance(@dbfield, "group_field")
• Number x = PopulationVariance(@dbfield, @"group_field")
• Number x = PopulationVariance(@dbfield, @parameter)

StdDev()

Function

• StdDev(array_variable);
• StdDev(field_variable);
• StdDev(field_variable, groupby);

Description

This function is used to find the standard deviation of a group of values referred to by the first argument. The second argument, if there is one, gives the group by field name.

Arguments

• field_variable
  field_variable is a special variable in Formula, and refers to a group of values from a database or formula field. Whatever kind of source the data comes from, database or formula, it should be of the Number data type.
• array_variable
  array_variable refers to an array whose element should be a Number value.
• groupby
  groupby can be a constant String to indicate the groupby field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Return type

Returns a Number value.

Examples

• Number x = StdDev([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
• Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
Number y = StdDev(x)
• Number x = StdDev(@dbfield)
• Number x = StdDev(@formula)
• Number x = StdDev(@dbfield, "group_field")
• Number x = StdDev(@dbfield, @"group_field")
• Number x = StdDev(@dbfield, @parameter)

Sum()

Function

• Sum(array_variable);
• Sum(field_variable);
• Sum(field_variable, groupby);

Description

This function is used to add a group of values (referred to by the first argument) together. The second argument, if there is one, gives the group by field name.

Arguments

• field_variable
  field_variable is a special variable in Formula, and refers to a group of values from a database or formula field. Whatever kind of source the data comes from, database or formula, it should be of the Number or Currency data type.
• array_variable
  array_variable refers to an array whose element should be Number or Currency value.
• groupby
  groupby may be a constant String to indicate the groupby field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Return type

The result type relies on the first argument. It may be Number or Currency if the data type of each element in the first argument is Number or Currency respectively.

Examples

• Number x = Sum([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
• Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
Number y = Sum(x)
• Currency x = Sum([$1, $2, $3.0, $4.5, $45, $67])
• Currency x[] = [$1, $2, $3.0, $4.5, $45, $67];
Currency y = Sum(x)
• Number x = Sum(@dbfield)
• Number x = Sum(@formula)
• Number x = Sum(@dbfield, "group_field")
• Number x = Sum(@dbfield, @"group_field")
• Number x = Sum(@dbfield, @parameter)
• Currency x = Sum(@dbfield)
• Currency x = Sum(@formula)
• Currency x = Sum(@formula, "group_field")
• Currency x = Sum(@formula, @"group_field")
• Currency x = Sum(@formula, @parameter)

UBound()

Function

• UBound(array_variable);

Description

This function is used to return the number of elements in an array.

Argument

• array_variable
  array_variable refers to an Array value, expression or variable.

Return value

Returns a Number value.

Examples

• The return value of the following statement is 5.

  integer a[] = [1,1,1,1,1];
UBound(a)

• The return value of the following statement is 6.

  UBound([$1, $2, $3.0, $4.5, $45, $67]).

• if(isNull(@"Customer Name"))then
{
return "No Data";
}
else{
string s[]=StringSplit(@"Customer Name");
string s1="Java";
string s2[]=filter(s,s1);
if (UBound(s2)!=0) then
return right(s2[0],4)
else
return "No matching data ";
}

  This formula searchs for the customer names containing the word "Java".

Variance()

Function

• Variance(array_variable);
• Variance(field_variable);
• Variance(field_variable, groupby);

Description

This function is used to find the variance of a group of values referred to by the first argument. The second argument, if there is one, gives the group by field name.

Arguments

• field_variable
  field_variable is a special variable in Formula, and refers to a group of values from a database or formula field. Whatever kind of source the data comes from, database or formula, it should be of the Number data type.
• array_variable
  array_variable refers to an array whose element should be Number value.
• groupby
  groupby can be a constant String to indicate the groupby field name, or a field variable on which grouping occurs, or a parameter variable that can accept a different value before each execution of a report.

Return type

Returns a Number value.

Examples

• Number x = Variance([1.0, 2.0, 3.0, 4.5, 45.0, 67.0])
• Number x[6] = [1.0, 2.0, 3.0, 4.5, 45.0, 67.0];
Number y = Variance(x)
• Number x = Variance(@dbfield)
• Number x = Variance(@formula)
• Number x = Variance(@dbfield, "group_field")
• Number x = Variance(@dbfield, @"group_field")
• Number x = Variance(@dbfield, @parameter)