##### Columns tab The Primary column for a table is defined on the Type tab. You define the remaining columns on the Columns tab. To define the columns in your report, you can: Add each individual column Copy an existing column Replicate a column (Trend Data only)
Click for details below To add a column To copy a column To replicate a Trend Data column Expressions Functions Operations Icons
###### To add a column Click Add. Enter or select options in the first four fields that appear. See table below. Select an option in the Column data is from field. See the gray rows in the table below for a description of the options. NOTE If you change your initial selection, click Change to have your new selection take effect. Select or enter information for the option you chose in step 1. See table below. Click Accept or Apply.
Field | Notes
--- | ---
The following four fields are common to all of the options from step 1 above.
Display name | The name that will be shown in the report as the column's header.
ID | A unique ID for the column (letters, numbers, underscores, and hyphens only; no spaces or special characters).
Render data as | Value | Shows a value in the report.
| Hidden | Hides the column in the report. The column's data can be used to produce a value for another cell.
| Color | Uses the column's value to determine a color on a color map. Set the Column data is from field to Expression or Function, and then enter the appropriate information that returns a color value.
| Icon | Shows an icon to indicate a certain condition. Set the Column data is from field to Expression, and then enter an expression that says what icon filename to show for a particular condition. You can use the icons included with your system or you can create custom icons. See Icons for more information.
Column format | Lets you define the column's alignment, width, and format of digits. NOTE Column format does not apply if you select Hidden or Color in the Render data as field.
The following fields are based on your selection in the Column data is from field.
Path | The column's output will be based on a path to a value in the WebCTRL® system.
Path | Enter the path to the value you want. See Defining WebCTRL® paths.
Show value as text | Check to have the value reported as text instead of its numerical value. For example, show the word On instead of 1.
Expression | The column's output will be based on the result of an expression.
Trend Data | The column's output will be based on a value calculated from a range of trend data.
Trend path | Do one of the following: Click the Select Trend Path button to choose the trended point. Typically, you want the full (absolute) path, but if needed, you can choose to get the relative path. Type the path to the trend that you want the report to pull data from. See Defining WebCTRL® paths.
Operation | Select the type of value or calculation that you want the column to show. See Operations for a description of each option.
Interval sample | If the selected operation allows, you can choose how to handle the first and last sample of the time period. For example, Include start time / exclude end time.
Database trends only | Check to include only trends saved in the database, not those in the controller.
Show time of sample | Check to include the time of the sample in the column.
Time range | From primary column | You can use this option if the report's primary column is a date range.
| From column | You use this option if your report began with an embedded external report that has a column containing date ranges.
| Value | A time period specified by entering a Start date and End date.
| Past | Enter a number of days, weeks, months, quarters, and years in the past. You can select whether or not to include the current time period.
| NOTE You can use a variable for a Time range count or date field. The variable must be defined on the Variables tab.
Function | The column's output will be based on the value or manipulation of the value from another column.
Input column | The column that you want to perform a function on.
Function | Select an option in the drop-down list. See Functions.
Arguments | A statement that contains the criteria of the function. See Functions for argument formats and examples. NOTE You can use a variable name in the argument. The variable must be defined on the Variables tab.
NOTES To delete a column, select the column in the table at the top of the page, then click Delete. To change the order of the columns, select a column and then click or to move the column.
###### To copy a column Select the column you want to copy in the table at the top of the Columns tab. Click the Copy button. Change the column's fields as needed. See field descriptions in To add a column. NOTE The column's ID is incremented by 1.
###### To replicate a Trend Data column When you have defined all the criteria for a trend column, you can quickly reproduce that column for other trend sources. Select the column in the table at the top of the Columns tab. Click the Replicate Column button. Select whether you want the Trend Path for the new columns to be the full (absolute) path or the relative path. Typically, you will leave this set on Full path. See Defining WebCTRL® paths. In the left column, select a location. The right column displays all trend sources at or below the selected location. Select the trend sources that you want. A column will be added for each instance of the selected trend sources at or below the selected location. Repeat steps 4 and 5 for any additional locations and points that you want in you report. Click Apply. Click Close. Change the each column's fields as needed. See field descriptions in To add a column.
###### Expressions On the Report Editor's Columns tab, you can specify that a column's data is from an expression. WebCTRL® expressions are similar to expressions used in spreadsheet programs. The most basic expression is a math calculation, but an expression can also manipulate text. An expression generally consists of at least one item in dollar signs and an operator. See table below. The item in dollar signs can be: Another column's ID A path to an item in your system A variable defined on the Report Editor's Variables tab Static text in an expression must be enclosed with single quotes. Any item that results in text should also be enclosed with single quotes. This example shows both situations: 'Filter is ' + '$filter_status$' Example of a simple expression to compute the average value of min_temp and max_temp columns Expression: ($min_temp$ + $max_temp$) / 2 To verify that the expression you entered is formatted correctly, click Check Syntax. The result appears to the right of the button. NOTE The result of checking an expression with a variable may not be accurate since variables can be used in such a wide variety of ways. Operators An operator defines how each piece of an expression is to be handled. The following table lists operators that can be used in expressions.
Operators that return true/false (1/0)
< | Less than | Compares numeric data. Returns true if the value to the left of the operator is smaller than the value to the right.
> | Greater than | Compares numeric data. Returns true if the value to the left of the operator is larger than the value to the right.
<= | Less than or equal to | Compares numeric data. Returns true if the value to the left of the operator is smaller than or equal to the value to the right.
>= | Greater than or equal to | Compares numeric data. Returns true if the value to the left of the operator is larger than or equal to the value to the right.
! | Not | Evaluates the expression and returns the opposite. Example: !$zone_temp$ > 72 If zone_temp is greater than 72, the expression is false. If zone_temp is not greater than 72, the expression is true.
== | Equal to | Compares data. Returns true if the value on both sides of the operator are equal.
!= | Not equal to | Compares data. Returns true if the value to the left of the operator does not match the value to the right.
&& | And | Combines expressions. Returns true if the expressions on both sides of && result in true.
|| | Or | Combines expressions. Returns true if the expression on either side or both sides of the operator results in true.
Operators that return a numeric value
+ | Add | Adds numeric data, expressions, or values.
- | Subtract | Subtracts numeric data, expressions, or values.
* | Multiply | Multiplies numeric data, expressions, or values.
/ | Divide | Divides numeric data, expressions, or values.
% | Modulus | Finds the remainder in the division of numeric data, expressions, or values.
Other operators
( ) | Parentheses | Use to nest expressions. Operations in parentheses are evaluated before those outside parentheses.
if |  | Syntax: if (expression, true value, false value) Expression is evaluated and if 1/true, the true value is returned, otherwise the false value is returned
Combining expressions Example 1: Expression: $zone_temp$ < 60 || $zone_temp$ > 75 Translation: True if the current zone temperature is less than 60 or greater than 75 Example 2: Expression: ! ( $ai1/locked$ || $ai1/present_value$ > 100 ) Translation: True if ai1 is not locked and al's present value is not greater than 100 Example 3: Expression: if ($zone_temp$ < 60 || $zone_temp$ > 75, 'out of range', 'good') Translation: If zone temperature is less than 60 or greater than 75, show out of range. Otherwise, show good. Math functions
Function | Description
--- | ---
abs (a) | Returns the absolute value of a value.
acos (a) | Returns the arc cosine of a value; the returned angle is in the range 0.0 through pi.
asin (a) | Returns the arc sine of a value; the returned angle is in the range -pi/2 through pi/2.
atan (a) | Returns the arc tangent of a value; the returned angle is in the range -pi/2 through pi/2.
atan2 (y, x) | Returns the angle theta from the conversion of rectangular coordinates (x, y) to polar coordinates (r, theta).
cbrt (a) | Returns the cube root of a value.
ceil (a) | Returns the smallest (closest to negative infinity) value that is greater than or equal to the argument and is equal to a mathematical integer.
cos (a) | Returns the trigonometric cosine of an angle.
exp (a) | Returns Euler's number e raised to the power of a value.
floor (a) | Returns the largest (closest to positive infinity) value that is less than or equal to the argument and is equal to a mathematical integer.
hypot (x, y) | Returns sqrt(x2 +y2) without intermediate overflow or underflow.
IEEEremainder (f1, f2) | Computes the remainder operation on two arguments as prescribed by the IEEE 754 standard.
log (a) | Returns the natural logarithm (base e) of a value.
log10 (a) | Returns the base 10 logarithm of a value.
max (a, b) | Returns the greater of two values.
min (a, b) | Returns the smaller of two values.
pow (a, b) | Returns the value of the first argument raised to the power of the second argument.
random () | Returns a value with a positive sign, greater than or equal to 0.0 and less than 1.0.
rint (a) | Returns the value that is closest in value to the argument and is equal to a mathematical integer.
round (a) | Returns the closest long to the argument, with ties rounding to positive infinity.
sin (a) | Returns the trigonometric sine of an angle.
signum (float f) | Returns the signum function of the argument; zero if the argument is zero, 1.0f if the argument is greater than zero, -1.0f if the argument is less than zero.
sqrt (a) | Returns the correctly rounded positive square root of a value.
tan (a) | Returns the trigonometric tangent of an angle.
toDegrees (angrad) | Converts an angle measured in radians to an approximately equivalent angle measured in degrees.
toRadians (angdeg) | Converts an angle measured in degrees to an approximately equivalent angle measured in radians.
Text functions
Function | Description
--- | ---
char (code) | Returns a single character string for the given Unicode character code. For example, char(36) will create the string "$".
charAT (s, pos) | Returns the character and the position.
compareTo (s1, s2) | Compares two strings. <0 if s1 <s2, 0 if s1 == s2, >0 if s1 > s2
compartToIgnoreCase (s1, s2) | Compares two strings ignoring case. <0 if s1 <s2, 0 if s1 == s2, >0 if s1 > s2
concat (s1, s2, ...) | Concatenates the two or more strings together. Same as "s1 + s2 + "
dateDiff (s1, s2) | Returns the difference between two dates, in days. Parameters may be date variables or strings of format 'yyyy/mm/dd'
endsWith (s1, s2) | Returns "1" if s1 ends with the string s2, else "0".
equals (s1, s2) | Returns "1" if strings are equal, else "0".
equalsIgnoreCase (s1, s2) | Returns "1" if strings are equal ignoring case, else "0".
indexOf (s1, s2, start) | Returns the index (position) of the first occurrence of the second string in the first string after "start" position. Use 0 to start from beginning of string. It returns -1 if S2 is not found.
lastIndexOf (s1, s2) | Returns the index (position) of the last occurrence of the seconds string in the first string. It returns -1 if S2 is not found.
length (s1) | Returns the length of the strings.
replace (s1, s2, s3) | Replaces all occurrences in "s1" of "s2" with "s3".
startsWith (s1, s2) | Returns 1" if s1 starts with s2.
substring (s1, i2, i2) | Returns subset from string s1 starting at index i1 to index i2. (i2 must be >= i1)
toLowerCase (s) | Converts string to lower case.
toUpperCase (s) | Converts string to upper case.
trim (s) | Removes white space from the beginning and end of the string.
###### Functions On the Report Editor's Columns tab, you can specify that a column's data comes from one of the following functions that returns another column's value or manipulation of that value.
Function | Description
--- | ---
Valid Column | Returns true/false if input column is valid
Default Value | Returns the column's value if it is a valid value, otherwise returns the argument.
Format | Formats a value using Java String format function. For more information, search the Internet for "string format with java 8".
Format Duration | Formats a trend duration value. Argument formats: %d%, %h%, %m%, %s% (clock based) %D%, %H%, %M%, %S% (total count rounded down) Example 1: %ddd% days %hh%:%mm% = 003 days 13:50 Example 2: %M% min = 283 min
Convert Values to Text | Converts a number to a text value. Argument format: Define a set of comma separated statements. Format of each statement: lower limit=value Example 1: 0=F,60=D,70=C,80=B,90=A,100=A+ Example 2: F,60=D,70=C,80=B,90=A,100=A+ (first bucket is default for anything below second bucket's value) Example 3: Cold,68=Perfect,75=Warm Example 4: 65=Cold,68=Perfect,74=Perfect,75=Warm,76=Warm
Convert Integer to Text | Converts an integer value to text. If no match, value is empty. Argument format: Comma separated list of statements. Format of each statement: #=text Example: 0=Zero,1=One,2=Two,3=Oops
Convert Text to Integer | Converts text to an integer value. Matching is case insensitive. Argument format: Comma separated list of statements. Format of each statement: text=# Use * to match any letters. Example 1: Off=0,On=1 -or- off=0 -or- OFF=0 Example 2: a*=1,b*=2 a=1 -or- APPLE=1 B=2 -or- Book=2
Convert to Color | Attempts to convert an ALC color value (0 to 15) to a color for a color map.
Color Gradient | Converts a defined minimum and maximum number each to a color. It then maps numbers between minimum and maximum to colors to form a gradient. Format: min,max,color1,color2 Example 1: 1, 10, red, blue Example 2: 1, 10, #FF0000, #0000FF
Date Range Start | Formats the START date/time of a Date Range. Examples: yyyy/MM/dd hh:mm = 2017/07/04 11:30 hh:mm:ss = 08:35:16 For more information, search the Internet for "customizing date time formats with java".
Date Range End | Formats the END date /time of a Date Range. Examples: yyyy/MM/dd hh:mm = 2017/07/04 11:30 hh:mm:ss = 08:35:16 For more information, search the Internet for "customizing date time formats with java".
Ordinal Value | Converts a text enumeration to its integer value when possible.
Regular Expression | Finds a piece of text from a larger text body. Example: Finds a piece of text in a modstat. For more information, search the Internet for "regular expression patterns with java 8".
###### Operations On the Report Editor's Columns tab, you can specify that a column's data comes from trend data. You can then specify one of the following operations be performed on the trend data.
This operation... | Shows the following for the specified time range
--- | ---
Average Value | The average value.
Count All Trend Records | Number of trend records collected (includes items such as time changes and enabling/disabling the trend log).
Count Trend Samples Only | Number of times the trend value was read.
First Value w/Time | The first trend sample and the time it was read.
Last Value w/Time | The last trend sample and the time it was read.
Maximum Value w/Time | The largest value and the time it was read.
Minimum Value w/Time | The smallest value and the time it was read.
Aggregate Consumption | Total consumption for meter trend data. This operation makes appropriate calculations for meters that reset to 0.
Sum of Values | The total of all trend values.
% Time in Range | You can enter 3 types of arguments to determine the percentage of time that the trend value was: One or more single values. Format: A comma separated list of values Example: Enter 1,2,3,4 to get the percentage of time that the trend value was 1, 2, 3, or 4. Between two values Format: A single statement or a comma separate list of statements Example 1: Enter the statement 65:75 to get the percentage of time that the trend value was 65 to 75. Example 2: Enter the statement 28:30,38:40,48:50 to get the percentage of time that the trend value was 28 to 30, 38 to 40, or 48 to 50. Not a specified value or between two values Format: !(value) Example 1: Enter !10 to get the percentage of time that the trend value was not 10. Example 2: Enter !28:30,38:40 to get the percentage of time that the trend value was not 28 to 30 or 38 to 40.
###### Icons You can design a report to show icons to indicate certain conditions. You can use the icons included with your system or create custom icons. On the Report Editor's Columns tab: Set Column data is from field to Expression. Set Render data as field to Icon. Enter an Expression that contains the icon's file name. See the table below for the file names of icons included with your system, or see "Custom icons" below. Included icons
Color | On | Off | Animated .gif that flashes on and off
--- | --- | --- | ---
Red | light_on_red.png | light_off_red.png | light_alarm_red.gif
Blue | light_on_blue.png | light_off_blue.png | light_alarm_blue.gif
Light blue | light_on_ltblue.png | light_off_ltblue.png | light_alarm_ltblue.gif
Green | light_on_green.png | light_off_green.png | light_alarm_green.gif
Yellow | light_on_yellow.png | light_off_yellow.png | light_alarm_yellow.gif
Magenta | light_on_magenta.png | light_off_magenta.png | light_alarm_magenta.gif
Orange | light_on_orange.png | light_off_orange.png | light_alarm_orange.gif
White | light_on_white.png | light_off_white.png | light_alarm_white.gif
Custom icons If you choose to use a custom icon, put the icon in one of the following places: In WebCTRLX.X\webroot\<system name>\tables. Put only the icon's file name in the expression. Anywhere under the webroot folder. Put the full path from the webroot folder in the expression. Example: /_common/lvl5/skin/graphics/type/area.gif.