Formula examples

The following are some formula examples.

Example 1: TerritoryUSA

if (@State in ["CT", "ME", "MA", "NH", "NY", "RI", "VT"]) {
  "NorthEast, USA";}
else if ( @State in ["DC", "DE", "MD", "NJ", "PA", "VA"]) {
  "MidAtlantic, USA";}
else if ( @State in ["CO", "ID", "KS", "MT", "NE", "NV", "NM", "ND", "SD", "UT"]) {
  "Rockies, USA";}
else if ( @State in ["AZ", "AR", "LA", "MO", "OK", "TX"]) {
  "South, USA";}
else if ( @State in ["AL", "FL", "GA", "MS", "NC", "SC"]) {
  "SouthEast, USA";}
else if ( @State in ["AK", "CA", "HI", "OR", "WA"]) {
  "West, USA";}
else if ( @State in ["IL", "IN", "LA", "KY", "MI", "MN", "OH", "TN", "WI", "WV", "WY"]) {
  "MidWest, USA";}

Purpose: In your sales report, you want JReport to print out which territory the customer belongs to according to the abbreviated state names stored in the State field.

Explanation: In the six else-if statements, JReport will retrieve the value of the State field, and compare it with the values in brackets []. If a value matches the value in brackets, the formula will return the territory name.

Example 2: DateToMonth

Number m = Month ( @"Order Date" ) ;
String str = "";
if ( m == 1 ) {
  str = " January Sales";}
else if ( m == 2 ) {
  str = " February Sales";}
else if ( m == 3 ) {
  str = " March Sales";}
else if( m == 4 ) {
  str = " April Sales";}
else if( m == 5 ) {
  str = " May Sales";}
else if ( m == 6 ) {
  str = " June Sales";}
else if ( m == 7 ) {
  str = " July Sales";}
else if( m == 8 ) {
  str = " August Sales";}
else if ( m == 9 ) {
  str = " September Sales";}
else if( m == 10 ) {
  str = " October Sales";}
else if ( m == 11 ) {
  str = " November Sales";}
else if ( m == 12 ) {
  str = " December Sales";}

Purpose: In your sales report, you want JReport to print out the month name of the orders, so that you can compare the orders of each month in a year.

Explanation: Since the database field Order Date is of the TimeStamp data type, the built-in function Month (DateTime) is first used to extract the month portion of Order Date. Then, the else-if statement is used to decide the month according to the return value of the function, and assigns a String value which contains a relative month name to the pre-declared string variable str.

Example 3: SectionInvisible

boolean s;
if (pagenumber==1){
  s=true;}
else {
  s=false;}

Purpose: This formula is used to control the Suppressed property of a certain section such as Page Header, so that the section will be suppressed on the first page of the report.

Explanation: Since it is only required to suppress the section on the first page, if only the Suppressed property of the section is set to true in the Report Inspector, then it will be suppressed on every page. In this formula, a Boolean type variable is first declared. Then, an else-if statement is used to decide whether or not the page is the first page. If the statement is true, true is assigned to "s". If the statement is false, false is assigned false to "s". In this formula, pagenumber is a special field.

Example 4: Running total of page

You can use the running total function to calculate the sum of one page. And to use the running total function, you have to create a set of formulas.

1. Create a formula named Formula1 as follows to initialize a global variable as the sum variable. And insert it into the BandedPageHeader panel. The reference to PageNumber forces the calculation to run after the page break. For more information, see Formula levels.

   pagenumber;
global number pagetotal;
pagetotal=0;

2. Create a formula named Formula2 as follows to accumulate the value. And insert it into the Detail panel.

   pagetotal=pagetotal+@"YTD Sales";

3. Create a formula named Formula3 as follows to display the sum of one page. And insert it into the BandedPageFooter panel.

   return pagetotal;

Example 5: Running total of group

This example will accumulate the total of the successful and failed shipments for each group.

1. Create a formula named Formula1 as follows to initialize the value. And insert it into the group header.

   global integer iFail; global integer iSuccess; iFail=0; iSuccess=0;

2. Create a formula named Formula2 as follows to accumulate the value of the successful and failed shipments, and insert it into the Detail panel.

   if(@Shipped) { iSuccess=iSuccess+1; iFail=iFail; } else { iSuccess=iSuccess; iFail=iFail+1; }

3. Create a formula named Formula3 as follows to return the successful shipments and the failed shipments, and insert it into the group footer.

   return "Total successful shipments is "+ iSuccess +" and total failed shipments is "+ iFail;