The following is a list of element attributes that can be modified using scripts.

In addition to modifying these properties for the current cell, you can also retrieve these properties from other report elements. You can retrieve the value from a report element in the same manner that you do in formulas either {Column Field}, @Formula, :Parameter, etc, or using the id() function for other cells or labels. For more information about this, please see Section 1.9.2.3 - Using Formulas.

You can retrieve all other attributes using the following syntax ATTRIBUTE(Report Element). For example, FONTCOLOR(id(RPT_HDR_LB0)) would return an integer array [R,G,B] representing the font color of a label in the Report Header section. You can use this to run comparisons based on attributes or to match the attributes of the current cell with those of another report element. For example, BGCOLOR=BGCOLOR({ProductName}); would change the current cell's background color to match that of the ProductName column.

Depending on which type of element the script is being applied to, certain formatting actions may have no effect. For example, you can set scripts on lines and rectangles, but you can only modify their bounds and colors.

1.9.2.1.1. Scripting Image URLs

Utilizing the if else conditionals and the Image URL formatting option, you can create image URL scripts that change images dynamically based on data in your report. Suppose you want to include two simple images to help readers determine if the sales for a particular category has achieved the desired goal. Let's say that the sales goal for each category is 100 units sold. Using this information, you can write the following script to display the success image when the goal is met and the failed image when the goal is not met.

if(@SUM_Quantity > 100) {
    value = "http://www.quadbase.com/images/green.jpg";
} else {
    value = "http://www.quadbase.com/images/red.jpg";
}

Create a new formula cell that has a string data type (you can simply use "" for the content) and apply this script to the cell. Then set the data formatting for this cell to be "Image Url". More information about data formatting can be found in Section 1.5.7.3 - Data Formatting for Formulas and Column Fields. Here is how the report will look like using this script:

Scripted Image URL

In addition to the if else statements, you can also define variables and arrays in a script. Variables and arrays are defined at the beginning of the script (before any statements).

1.9.2.2.1. Variables

If you have a constant or expression that you intend to use in multiple places in a script, it can often be easier to define a variable rather than re-typing the constant or expression in every place where it is used. A variable can be declared at the beginning of a script using the following syntax: DataType VariableName;. The data type is the type for the variable value and can be either Number, Boolean, String, or Date.

	Note
	DateVar is used as the data type declaration for Date variables. This avoids confusion with the date() function. The variable name can be anything except an operator or function name.

The following example uses variables which contain an expression.

String Line1;
String Line2;
Line1 = {Address1} + "\n";
Line2 = {City} + ", " + {State} + " " + {Zip};

if ({Address2} == NULL) {

    VALUE = Line1 + Line2;
} else VALUE=Line1 + {Address2} + "\n" + Line2;

This script pieces together several report fields to create an address entry. The first two lines of the script declare the variables. The script then concatenates fields and strings depending on whether there is an Address2 entry or not. Using the variables prevents having to re-type the expressions. Note that “\n” is used to break the text to a new line.

You can also assign a value to a variable as part of an action in a script. You can use this to modify the variable value based on some conditions and then return the variable value to the cell. This is useful if you only want to write one value statement. The following example assigns a value to a variable:

if ({Flags} == "M") {
    LinkAppend = "Main";
} else if ({Flags} == "R") {
    LinkAppend = "Remote";
} HYPERLINK = ["StatPage.html#" + LinkAppend, "", ""];

In this example, a variable is defined without an initial value. The value of the variable is assigned based on a string flag in the report. At the end of the script a hyperlink is defined for the element that uses the variable value to specify a page location.

String[] MonthNames = ["January","February","March","April", 
"May","June","July","August","September","October",
"November","December"];

VALUE = MonthNames[({Month} - 1)];

You can use any of the EspressReport formulas from the main report or any sub-reports (for more on accessing sub-report formulas, see Section 1.8.3 - Subreport Formula Access) in both the condition and action components of the script, as well as for variable values. Most commonly formulas are used to retrieve values either from the current element or other report elements. You can retrieve the value from column fields using {Column Field}, the value from existing report formulas using @FormulaName and the user supplied values for query and formula parameters using :ParamName and ?ParamName respectively.

In addition to these methods, there is one additional function that can be used in scripts.

You can also use the formulas to build expressions for comparison or to change an element's value. For more information about EspressReport formulas and their syntax, please see Section 1.8 - Using Formulas & the Formula Builder.

Using cell scripts you can check for null values within report columns. To specify a null as a constant, simply use the word null without any delimiters. So a simple script that checks for nulls would look like this:

if (this() == null) {
    VALUE="yes";
}

	Note
	Even if you have set the null data handler in Report Designer, you can still use script in this manner. This is because the script will execute before the null handler has a chance to convert the content of the cell.

Using scripts, you can retrieve and modify values for specific rows of column fields. Normally when you retrieve or replace the value of a column field you will get/replace the first value from the column or the current row if the script is applied to a field in the Table Data section. However, you also have the option of specifying which row you would like to get/replace. To do this, you can append a column field with the following syntax: .row(Row Index). The row index is the index value associated with each row in a report starting with 0. For example {ProductName}.row(3) would return the fourth value of the ProductName field.

You can obtain the current row index as well as the total number of rows in the report using the getRowIndex() and getTotalRowIndex() functions respectively. The following example uses this feature to hide repeated values in a column field.

if (({Category}.row(getRowIndex()) == {Category}.row(getRowIndex()-1)) AND (getRowIndex() <> 0)) {
    FONTCOLOR=[255,255,255];
}

This example assumes that the background color for the report is white and hides the field by adjusting the font color accordingly.

In addition to conditional if else statements, EspressReport also allows you to add loops to scripts. Used in conjunction with variables and row-specific options/formatting, loops are useful if you need to perform running totals/calculations independent of the current row or section of the report. The basic syntax for loops is:

while (Condition) {Action(s);}

The specified actions will be performed as long as the condition (Boolean expression) is true.

For example, say you have the following report:

Adding a formula that calculates the total quantity to the Table Footer (sum({Quantity})) would return 66. However, say you only wanted to show the total quantity for chairs in the Table Footer. To do this, you write a loop. Applying the following script to the aggregation.

Number i; 
Number s; 
i = 0;
s = 0;

while (i < getTotalRowIndex()) {
    
    if ({Product}.row(i) == "Chair") {

        s = s + {Quantity}.row(i);
                
    }
    
    i = i + 1;

}

VALUE = s;

will return 45 - the sum of the quantity column for Chair. The script loops through each row in the report and checks to see if the value for the Product column is “Chair”. If it is, the Quantity value for that row is added to the variable s.

In addition to individual cells, scripts can also be applied to report sections. Based on conditions within the report data, individual section options can be turned on/off and section background colors can be set. To add a script to a report section, click on the button with the section name on the left side of the Designer. You can also right click on a blank portion of the section field and select Scripting from the pop-up menu.

Writing section scripts is exactly the same as writing scripts for report elements as they use the same syntax. The only difference is that instead of Cell Attributes folder in the Formula Builder, you will see Section Attributes folder containing various formatting actions that can be performed with section scripts.

1.9.2.7.1. Formatting Actions for Section Scripts

The following is a list of section attributes that can be modified using scripts (not all options are available for every report section).

BGCOLOR:

The action syntax that allows you to change the background color of the element is BGCOLOR=[R,G,B]; where R is a number for the red value of the new color, G is a number for the green value of the new color and B is a number for the blue value of the new color. You can automatically generate the RGB array using the Insert Color button in the Formula Builder.

PRINT_ON_NEW_PAGE:

This action allows you to turn on/off the print on new page section option. The syntax is PRINT_ON_NEW_PAGE=True/False; For more information about the print on new page feature, please see Section 1.5.3 - Section Options.

RESET_PAGE_NUMBER:

This action allows you to turn on/off the reset page number section option. The syntax is RESET_PAGE_NUMBER=True/False; For more information about the reset page number feature, please see Section 1.5.3 - Section Options.

VISIBLE:

This action allows you to control whether a section is visible or not. The syntax is VISIBLE=True/False; Setting visible to false will make the section invisible. Using this attribute coupled with the nested section feature (Section 1.5.1.1 - Nested Sections) allows you to create different headers/footers to display depending on the data.

HEIGHT:

This action allows you to set the height of a section. The syntax is HEIGHT=Size; (in inches or centimeters depending on which measurement is selected). For example HEIGHT=0.25; would set the height of the section to 0.25 inches/centimeters.

	Note
	Scripts that set or refer to section attributes can only be applied to sections. They cannot be applied to other report elements.

You can also add comments to a script to explain its design and operation. A comment is preceded by two slashes //. Anything following this on the same line is treated as a comment. You can break comments into multiple lines, but each line must begin with //.

If you have scripts applied to cells in your report, you can access these values using the syntax .getScriptValue(). For example, you may have this data:

String, int
Month, Revenue
January, 3567
"February", 6854
"March", 5421
"April", 8425
"May", 5611
"June", 2356
"July", 6543
"August", 2563
"September", 4432
"October", 8841
"November", 1023
"December", 2265

If you wish to take this data and create a year to date revenue column, you could use the following script, which would display the scripted value from the previous row added to the revenue from the current row. This script is written to be put on a formula consisting of the number 0 as a column in the table data section, refered to within this script as {formula1}.

if(getRowIndex()==0)

    VALUE={revenue};
else
    VALUE={revenue} + {formula1}.getScriptValue(getRowIndex()-1);

This would yield a report like this: