Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Extras

Special characters

For example, (char)34 puts " into a text

 

...


Additional functions

The formula operator offers several internal functions:

Description

Formula

Please note

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
 

Please find additional XIH functions below.

 

...


Creating a signal light with formula

...

Important: The result column type must be text.

Description

Formula

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>"

 


Interception of Null values

Description

Formula

Please note

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.

...

Calculation of Min, Max, Percentiles, ... a column

...

new System.Random((int)System.DateTime.Now.Ticks + (int)#Satznummer#).Next(100)

...

More complex numeration

Description

Formula

Please note

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.

...

Create row groups

Description

Formula

Please note

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.

...

Calculating current totals

Description

Formula

Please note

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.

...

Direct access to table values via the object dataRow

Description

Formula

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

...

Calculating HMACSHA256

(warning) Note that you need additional namespaces in configuration CCXAllowedInstanceTypes: System.Text;System.BitConverter;System.Double;System.Security.Cryptography

Image Added

Converting Unix Timestamp to DateTime

(warning) Note that #Timestamp# is of type double

Image Added