Previous Topic  Next Topic

Formula Syntax

The Report formula syntax is a syntax with simple flow control and other statements. You should follow the syntax when writing formulas in Report. This topic introduces this syntax in detail.

The Report formula syntax supports the sequence and selection control structure. You can use it to control the execution of the statements. In this syntax, you can use a parameter as variable, and then use its value in computation or making decisions. At runtime, users need to specify the value of the parameter before running the report, so the report can respond to user input and generate different results.

This topic contains the following sections:

• Formula Statements
  • Declare Statement
  • Expression Statement
  • Assignment Statement
  • If-Else Statement
  • Return Statement
  • For Statement
  • While Statement
  • Select Statement
• Formula Expressions
  • Values in Formula Expressions
  • Value Data Types
  • Report Level Global Variables

Formula Statements

The statement is the smallest executable unit in the formula syntax. A formula is a sequence of statements. You need to separate statements by semicolon (;). There are many kinds of statements, such as the "declare" statement, "expression" statement, "assignment" statement, "if-else" statement, and "return" statement.

Declare Statement

You can use the "declare" statement to declare variables.

• Syntax: VariableType VariableName;

  Example: String sql;

  This statement declares a local String type variable called "sql".

• Syntax: global VariableType VariableName;

  Example: global integer i;

  This statement declares a global Integer type variable called "i". You can only use global variables in page reports.

Expression Statement

You can use the "expression" statement to calculate the value of an expression.

Assignment Statement

The "assignment" statement calculates the expression on the right side of the assignment operator and assigns the value to the variable on the left side.

Syntax: Variable = Expression

The type of the expression's value must be compatible with the type of the variable.

Example: total = amount * price

In this example, where the total, amount, and price are declared variables. The statement calculates the "amount * price" and assigns the value to "total".

If-Else Statement

The "if-else" statement provides conditional control of an action. Normally, Report Engine executes the "if-else" statements one by one sequentially. The "if-else" statement enables alternative actions that depend on the evaluation result of the expression.

Syntax:

• if (expression) {
      statement(s);}
• if (expression) {
      statement(s);}
  else {
      statement(s);}

expression must be a logical expression and have a Boolean value. The "if" clause checks the condition presented by the logical expression. If the condition is true, Report Engine executes the statement following the "if"; otherwise, it executes the statement following the "else".

Example:

If (score >= 60) {
    return "PASS";}
else {
    return "FAIL";}

In this example, if the score is greater than or equal to 60, the result is pass; otherwise, the result is fail.

You should not use more than 97 "else if" clauses in an "if-else" statement.

Return Statement

The "return" statement stops the execution of a procedure and returns a value. The "return" statement is the last statement executed.

Syntax: return or return expression;

Report Engine calculates the expression before the procedure returns.

Example: return results;

For Statement

The "for" statement provides a compact way to iterate over a range of values.

Syntax:

• for (initialization; termination; increment) {
      statement;
  };
• for (initialization; termination; increment) statement;

The initialization expression initializes the loop - Report Engine executes it once at the beginning of the loop. The termination expression determines when to terminate the loop. Report Engine evaluates this expression at the top of each iteration of the loop. When the expression evaluates to "false", the loop terminates. Finally, Report Engine invokes the increment expression after each iteration through the loop. All these components are optional.

Examples:

integer i=0, j=0;
For(i=0;i<100;i=i+1){
    j=j+1;
};
return j;

Any time there is a single statement, you do not need the curly braces "{}" so the following formula works the same.

integer i=0, j=0;
For(i=0;i<100;i=i+1) j=j+1;
return j;

Both examples return 100 as the result.

integer siteint=0;
string stite="&_isMultiple_jrs.param$Site=true";

if(@P_int==0) then
    stite=stite+"&jrs.param$Site=%07"
else {
    for(siteint=0;siteint<@P_int;siteint=siteint+1)
    stite= stite+"&jrs.param$Site=" + @P_int
};
return stite;

While Statement

The "while" statement continually executes a block of statements while a condition remains true.

Syntax:

• while (expression) do {
      statement;
  };
• while(expression) do statement;

First, the "while" statement evaluates expression, which must return a Boolean value. If it returns "true", the "while" statement executes the statement associated with it. The "while" statement continues testing the expression and executing its block until the expression returns "false".

Examples:

• integer i=0;
While(i<100) do {
    i=i+1;
};
return i;
• integer i=0;
While(i<100) do i=i+1;
return i;

Both examples return 100 as the result.

Report also provides another statement that is similar to the "while" statement, the "do-while" statement.

Syntax:

• do {
      statement(s);
  } while (expression);
• do statement while(expression);

Instead of evaluating the expression at the top of the loop, "do-while" evaluates the expression at the bottom. Thus, Report Engine executes the statements associated with a "do-while" at least once.

Examples:

• integer j=0;
Do {
    j=j+1;}
while(j<100);
return j;
• integer j=0;
Do j=j+1 while(j<100);
return j;

Both examples return 100 as the result.

Select Statement

Usually, you use the "select" statement in the case when the value of a single variable may determine one of a number of different choices.

A "select" statement is given a variable and compares its value to all cases in the switch. If there is a case that matches the value, Report Engine executes all statements in the matching case; if none match, and a default is given, Report Engine executes all statements following the default keyword.

Syntax:

Select (variable name){
    case expression_1: statement
    case expression_2: statement
    ...
    default: statement
};

expression_1 and expression_2 should be variables or constants. The statement in each should be a single statement or multiple statements (compound statement). When you use multiple statements, you need to enclose them by "{}".

• Report Engine only evaluates the value of "select" one time even when a case modifies the tested value.
• Report does not require the "break" statement. The logic flow does not continue through the following "case" statements.

Examples:

• integer i = 0 ; string j = 'a';
i = @"Customers_Customer ID";
select(i) {
    case 1: j='bb'
    case 2: j='cc'
    default: j ='dd'
};
return j;
• string state="",result="";
if (IsNull(@Customer_State)) then
    state="Others"
else
    state = @Customer_State;
select (state) {
    case "BC","ID","MT","WA":result="Northwest"
    case "CA","AZ","TX","NM":result="Southwest"
    case "ME","MA","NH","NY":result="Northeast"
    case "FL","NC","GA","SC":result="Southeast"
    default: result="Rest of Country"
};
return result;

Back to top

Formula Expressions

The expression of a formula is a combination of values, operators, and functions that produce a result. The value in the expression can be a literal value or a variable; the operator defines the operation between values; the function performs an action and returns a value.

Values in Formula Expressions

The value you specify in an expression can be a literal value or a variable value. The Report formula syntax supports seven data types of value.

Literal Values

A literal value represents a value such as a Number, String, or Date, for example, "Name", 98.6. Report Engine applies the literal value exactly as it displays.

Variables

A variable is a named storage unit that stores a value. You can use the identifier (name) of a variable to refer to the value of the variable or refer to the storage space of the variable. In an expression, you can use the identifier to refer to the value, and in the "assignment" statement, you can use the identifier on the left to refer to the storage space.

• Undeclared variables
  The undeclared variables include parameters, DBFields, special fields, and summaries in the catalog.
• Declared variables
  The declared variables are another type of variables which you must declare in the "declare" statements before use. A declared variable is a real variable and you can assign it in the "assignment" statement. There are two types of declared variables:
  • Local variable
    A variable that is only valid in the formula where it is declared.
  • Global variable
    A variable that is valid in all the formulas once it is declared. You can use global variables in page reports only.

You cannot use the following Report reserved keywords to specify variables in your formula expressions: all, arguments, binary, boolean, break, case, chart_category, chart_series, children, continue, crosstab_column, crosstab_row, currency, current, datacontainer, date, datetime, default, do, else, enumeration, false, firstmember, for, from, global, if, image, import, in, integer, lastmember, like, longstring, number, jsonarray, jsonobject, range, report, request, response, return, select, string, then, time, to, to_, _to, _to_, true, upfrom, upfrom_, upto, upto_, while.

Value Data Types

The following table lists the date types the Report formula syntax supports.

Type		Description
Integer	64-bit two's complement	A data type that holds large integers.
Number	64-bit IEEE 754	This data type defines an 64-bit IEEE standard 754 floating-point field. You can define floating-point fields as single or double (default) precision based on the value of integer. If the value of integer is between 22 and 53 inclusive, the type is double precision floating-point, which requires 8 bytes of storage. If a field is specified as NOT NULL WITH DEFAULT, Report Engine replaces null values by zero.
Currency		This data type contains a dollar amount between; -1012 and 1012 and is displayed in a user-defined format.
String		A data type that holds character information.
Boolean	true or false	True or false.
DateTime	yyyy-MM-dd hh:mm:ss	Report displays this combination Date and Time data type in the format "yyyy-MM-dd-HH: mm.ss.nnnnnn". yyyy An Integer from 0001 to 9999, representing a year. MM An Integer from 1 to 12 representing a month. dd An Integer from 1 to 31 (maximum depends on the month and year) representing the day of the month. HH An Integer from 0 to 23, representing the hour in day. hh An Integer from 1 to 12, representing the hour in am/pm. mm An Integer from 0 to 59, representing minutes. ss An Integer from 0 to 59 representing seconds (default 0). nnnnnn An Integer (up to 6 digits) representing microseconds. If any trailing digit is omitted, 0 is assumed.
Date	MM/dd/yy MM/dd/yyyy	Dates designate a point in time according to the Gregorian calendar. Historical dates may not follow this calendar. The standard input format for this data type is "MM/dd/yy" or "MM/dd/yyyy" (automatic conversion is performed between these two formats).
Time	HH:mm:ss hh:mm:ss	Report defines the Time field data type by the standard input format of "HH:mm:ss" (using hour in day 0--23) or "hh:mm:ss" (using in am/pm 1--12).

Report Level Global Variable

To control the formula more flexibly, Report offers you one more global variable scope to operate besides the component level global variable, which is the report level global variable. You can use "report" as the keyword to define the global variable in a formula. However, you can use report level global variables in page reports only.

For the two levels of global variable, the following definitions help you distinguish them:

• Component level
  Its lifetime is the same as the component's lifetime. In its lifetime, whenever Report Engine executes a formula, it executes the variable if you use the variable in the formula.
• Report level
  Its lifetime is the same as the report's (primary report) lifetime. Even in different components (using the same data resource), the same report level global variable has only one instance. Report Engine can pass the report level variable to different components (using the same data resource). The components include banded objects, tables, charts, tabulars, crosstabs, and subreports (including many layers of subreports).

When using the keyword "global" to define a global variable in a formula, if you add no words before the keyword, the global variable is on the component level; if you add "report" before the keyword, the global variable is a report level global variable.

The following example uses the report global variable to perform calculation in a report.

1. Create three formulas using the report global variable.
   • Formula1: report global number a =0;
   • Formula2: a = a+1;
   • Formula3: return a;
2. Create a page report which contains two banded objects, Banded Object 1 and Banded Object 2, using the same dataset.
3. Insert Formula1, Formula2, and Formula3 to Banded Object 1 (15 records).
   • Banded Header: Formula1
   • Detail Panel: Formula2
   • Banded Footer: Formula3 - Returns 15
4. Insert Formula2 and Formula3 to Banded Object 2 (25 records).
   • Detail Panel: Formula2
   • Banded Footer: Formula3 - Returns 40

• For nested data components, report level global formulas may lead to an unexpected result where the formulas are not calculated until the end of the group or report. You can add the PageNumber special field in the formula, so the calculation is in the sequential order as in the report template and you can get the result you expect.
• Although one report can have two or more data resources, report level global variable cannot span across data resources; otherwise, the formula cannot run correctly. Thus, even the report global variables with the same variable name in different data resources are different in one report.
• If there is a report level global variable report global integer g, and you use it both in the primary report and subreport, this "g" variable can communicate with each other.
• If you want to calculate the record number of the subreport and primary report separately, you can define a different report level variable from the primary report. However, if you insert one subreport into one primary report more than once, you are unable to correctly use the variable separately. In such circumstance, you can change the report level variable into component level variable to solve the problem.

Back to top

Previous Topic  Next Topic