Formula operator: Extras

Create a column which assigns consecutive numbers to the rows or uses them in a formula.

rowNumber

XIH.RowNumber

Counting begins with 0, unless you use rowNumber + 1

Define the result column as Integer.

Very useful to search for errors.

Create a column which shows the total number of rows or uses it in a formula.

rows

XIH.Rows

Create a column which shows the total number of columns or uses it in a formula.

XIH.Columns

Starts with 1

Create a column which shows the project name as text.

projectName

Alternative: XIH.ProjectName

Create a column which shows the folder name as text.

folderName

Alternative: XIH.FolderName

Create a column which shows the node name as text.

nodeName

Alternative: XIH.NodeName

Create a column which shows the user name as text.

XIH.UserName

Create a column which shows the client name as text.

XIH.ClientName

Create a column which shows the name of the data base and the data base server as text.

XIH.DatabaseInfo

If the value in column Amount is greater than 3.0, a red signal light, if not, a green signal light is displayed.

#Amount# > 3.0 ? 
"<img src=images/Tree/Icons/bullet_ball_glass_red.gif>" 
: 
"<img src=images/Tree/Icons/bullet_ball_glass_green.gif>"

A check shall be carried out to determine whether there is a Null value (i.e. a missing value) at a certain place. In case the value is missing you can program a determined step in the calculation. Example: If there is no value in column 4 (Attention: column numbers start with 0, the fourth column is actually number 3), the value A is used, otherwise value B is used.

up to TIS 5.4:

dataRow.Table.Rows[rowNumber][3] == System.DBNull.Value ? #Value A#: #Value B#

TIS 5.5 and up:

dataRow.Parent[rowNumber][6] == null ? #A# : #B#

• If you want to replace null values, this can be done more easily with the operator Replacing null values

• You have to directly access the contents via the dataRow variable. because in #<field name>#-Syntax a conversion is executed automatically.

More complex numeration (e.g. continue numeration only if certain criteria are fulfilled): Create a specific counting variable, to which the formula is then applied. The result column type is decimal number.

Example for a variable VAR number with initial value 0:

additionalParameters["VAR"] = (double) additionalParameters["VAR"] + 1

The variable assignment must be left (as usual). The type (in this case double) must be indicated because array is not typified. Everything behind the = goes to the Record. Everything on the left goes to the variable.

A column shall be created which assigns consecutive numbers to row groups (e.g. for different lines in a graphic). Create a specific counting variable, to which the formula is then applied. The result column type is decimal number.

Example for a variable VAR number with initial value 0:

additionalParameters["VAR"] = (double) additionalParameters["VAR"]

+ (#What# =="From" ? 1 : 0)

The variable assignment must be left (as usual). The type (in this case double) must be indicated because array is not typified. Everything behind the = goes to the record. Everything on the left goes to the variable.

In this example, the records were sorted in such a way that the first record of a group always had the value “From” in the Variable WHAT.

A column shall be created which sums up another one. Create a specific counting variable with initial value 0 to which the formula is then applied. The result column type is decimal number.

Example for a variable VAR number with initial value 0:

additionalParameters["VAR"] = (double) additionalParameters["VAR"] + #Column#

The variable assignment must be left (as usual). The type (in this case double) must be indicated because array is not typified. Everything behind the = goes to the record. Everything on the left goes to the variable.

The following command inserts the value from #Day# if there is a value in the initial column, the value of the preceding row if not.

 additionalParameters["VAR"] = (#Day#.Length == 0 ? (string) additionalParameters["VAR"]

+ (string) #Day#)

The result needs to be set to text.

Number of rows in the database.

rows

Double value of the fourth column of the current row. Column indexation starts with 0! This kind of conversion is necessary because dataRow[i] always yields the data type object.

System.Convert.ToDouble(dataRow[3])

Double value of the first column of the current row. This kind of conversion is necessary because dataRow[i] always yields the data type object. 

System.Convert.ToDouble(dataRow.Table.Rows[0][0])

Yields the number of days for date/time value in first column #From# and data sorted in descending order.

(System.Convert.ToDateTime(dataRow.Table.Rows[0][0]) - #From#).Days