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

Method: Create a text column containing an HTML-Code, pointing to icons on the server.

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

The aim is to conduct several calculations across the values of one column. To accomplish this, the node is loaded into the persistence and re-imported with the Operation Importing data from SQL sources 3.1. It is possible to calculate many different values, e.g.:

select C12 "FIL Filial Nr", 
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY C17 DESC) "1.Quartil" ,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY C17 DESC) "3.Quartil" ,
max(C17) from <Kennnummer der Tabelle, zB XT6........>
group by C12

Colouring of week days

This function colours alternating grey and blue.

Expected are #Tag# 1 Mo, 2 Di ...

(#Tag#.Substring(0,1) == "2" 
|| #Tag#.Substring(0,1) == "4"
|| #Tag#.Substring(0,1) == "6"
?
(XIH.SetString("Tag", 0, " " + #Tag# + " ") ? 0:- 99)
: (XIH.SetString("Tag", 0, " " + #Tag# + " ") ? 0:- 99))

Generation of random numbers

A number in each row is needed (e.g., week number, row number, or record number). With each calculation, a new random number is generated between 0 and 100 (record number and current system time are used for initialization).

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