Summary

A user-defined formula can be entered in C# syntax. This formula is then applied to each row of the input table sequentially starting at the first row. A new column is created containing the results of each row's calculation. As the operator processes the rows in a sequentially, it can refer to the calculation results of the previous rows.

Examples: Calculating a sum of values from two columns, re-formatting data, writing "if ... then ... else ..." scripts, creating "signal lights", etc.


Formula operator

Date and Time

Text

General Mathematics

Conversion and Formatting

Extras

XIH Helper Functions

Example: Add values from two columns

Situation

Step 1

Edit function in the Operator settings

Step 2

 Enter formula and click "Apply"

Please note: Variables have to be enclosed by # to be recognized.

#Length#+#Width# means, that the values of the columns "Length" are added to the values of column "Width".

Result

TIS Project

Formula operator (row-by-row) (4.0) = Formeloperator

Want to learn more?

A user-defined formula can be entered in C# syntax. This formula is then applied to each row of the input table sequentially starting at the first row. A new column is created containing the results of each row's calculation. As the operator processes the rows sequentially, it can refer to the calculation results of the previous rows.

Parameter



Using C# in TIS

C# Syntax:

  • Formulas and more information under MSDN
  • C# is case sensitive. Be careful with upper and lower case letters!
  •  Write the constant with decimal places --> 7.0 if an operation is supposed to yield a number with decimal places and you are working with constants.
  • class names must be fully qualified, e.g. System.DataTime.Now

Specific in TIS:

  • Variables have to be enclosed by # to be recognized. #Length#+#Width# means that the values of the columns "Length" are added to the values of column "Width".
  • Additional functions, such as the Ximes Helper (XIH) Functions and built-in variables such as rownumber provide additional functionality. Please refer to the sections below.
  • XIH Functions do NOT allow # in the notation of variables. Example: write "Identifier" instead of "#Identifier#".

Variables can be

  • columns
  • TIS parameters
  • manual entries

NOTE: manual entries can only be named at creation time. Click into the combo box with text "Manual Entry..." and edit the text.

NOTE: manual entries must not contain spaces in their names.

Good editors for formula operator: Easier editing in the Formula Operator

Examples and Formulas

First Steps


Example 1: Multiplying two values

Situation

Operation setting

Please note: Variables have to be enclosed by # to be recognized. #Length#*#Width# means, that the values of the column "Length" are multiplied by the values of column "Width".

Result

 

Example 2: Calculation with integral numbers or decimals

  • 5 / 2 equals 2 (integral number, rounded down). Without conversion to Double, the C# Compiler automatically converts to integral numbers.
  • 5.0 / 2.0 equals 2.5 (decimal number).
  • 5 / 2.0 equals 2.5 (decimal number), because at least 1 number is a decimal.
  • (double) #Integral number column# converts an integral number to a decimal.

Example 3: If then else

Description

Formula

If value in the column is greater than 3, then write 1, else write 0 into result column.

#Column# > 3.0 ? 1.0 : 0.0

If the date of the column lies between 2003/01/01 and 2003/09/30, then write 1, else write 0 into result column.

#Date#.Year == 2003 && #Date#.Month <= 9 ? 1 : 0

Example of a complex If:

#timefrom# >1000.0 ? "N" : (#timefrom# < 100.0 ? "Free" : "F")

 

Example 4: Write set values into a column

Description

Formula

Write the same text into each row of a column: The text must always be written between inverted commas. Additionally, the result column type must be set to Text.

"Same text"

 

Example 5: Two columns shall be merged to one identifier

Description

Formula

Extends the functionality of Combine columns, e.g., yields a text separated by_  

(Example: 01.01.2007 00:00:00_Department1)

#dateColumn#.ToString() + "_" + #textColumn#


 

Syntax

Symbol

Description

==

equals

%

modulo

||

logical or

&&

logical and

 

Snippets

Key word

Background

Requirements

Code

Effect

Avoid empty rows

It is often practical to avoid empty rows, e.g. because calculations based on these rows would not function with empty rows, or users would wonder why nothing appears.

  • In column “Keep” it is coded with 1 or 0 if the row should be kept or not.
  • This column needs to be named “Helper” for the new formula
  • The columns “Value” and “Description” need to be renamed accordingly if values in these columns should be deleted.
(XIH.EqualsPrev("ID") == false ? #Keep# :
(XIH.EqualsNext("ID") ?
 (XIH.GetInt("Helper", -1) + #Keep#)
 : ( XIH.GetInt("Helper", -1) == 0 && #Keep# == 0?
      (XIH.SetInt("Keep", 1) &&
      XIH.SetDouble("Value", 0) &&
      XIH.SetString("Description", "no matching data found")
         ? 0:-999) : 0)))

Or without ID

(rowNumber < (rows - 1) ?
(XIH.GetInt("Helper", -1) + #Keep#)
: ( XIH.GetInt("Helper", -1) == 0 && #Keep# == 0?
     (XIH.SetInt("Keep", 1) &&
     XIH.SetDouble("Value", 0) &&
     XIH.SetString("Description", "no matching data found")
        ? 0:-999) : 0))

The last sentence remains. Values are being deleted.

Sort weekdays


See also above "Date and Time"

If weekdays are displayed as Mo, Di … they will be sorted incorrectly.

  • German names with at least two letters
  • Variable #WoTag# 
0
+ (#WoTag#.Contains("Mo") ? 1 : 0)
+ (#WoTag#.Contains("Di") ? 2 : 0)
+ (#WoTag#.Contains("Mi") ? 3 : 0)
+ (#WoTag#.Contains("Do") ? 4 : 0)
+ (#WoTag#.Contains("Fr") ? 5 : 0)
+ (#WoTag#.Contains("Sa") ? 6 : 0)
+ (#WoTag#.Contains("So") ? 7 : 0)

The result is an Integer which can be sorted.

Create HTML tables

With nice formatting.

Example: Adapt columns and formatting

Structure in formula operator: column HTML – data type Text

/* Fix Prefix*/
(rowNumber == 0 ?
( "<tableX border='1'" +
" bordercolor='#E8E8E8' cellpadding='5' cellspacing='0' width='100%'>" +
/*adapt column number*/
"<colgroupX> <col width='30%' align='left'>  <col width='20%' align='right'>" +
"<col width='20%' align='left'> <col width='30%' align='left'>  </colgroupX>" +
"<thead bgcolor='#C0C0C0'><tr>" +
/*Adapt column headings */
"<th>Name input value</th><th align='right'>Wert</th><th>Unit</th><th>Comments</th></tr></thead>") : "") +
"<trX bgcolor='#FFFFFF'>" +
/* ROW STRUCTURE */
"<trX>" +
"<tdX>" + #Input# + "" +  "</tdX>" +
"<tdX align='right'>" + #Value# + "" +  "</tdX>" +
"<tdX>" + #Unit# + "" +  "</tdX>" +
"<tdX>" + (XIH.IsNull("Comment") || #Comment# =="" ? "-" : #Comment#) +
"" + "</tdX>" +
 /* ROW ENDING */
(rowNumber == rows-1 ? ("</tableX>") : "")

Afterwards concatenate the single rows to one cell with Spalteninhalt verketten.
In the end replace the Steuerungszeichen with 2nd formula operator (result type Boolean)

XIH.SetString("HTML", #HTML#.Replace("tableX", "table")) &&
XIH.SetString("HTML", #HTML#.Replace("trX", "tr")) &&
XIH.SetString("HTML", #HTML#.Replace("tdX", "td"))&&
XIH.SetString("HTML", #HTML#.Replace("colgroupX", "colgroup"))

and delete unnecessary columns except column HTML.

Good to use with HTML Portlet

Format HTML Portlets

Make sure that fonts etc. fit.

HTML Portlet

Begin

"<html><head></head><body style='font-size: 12px; font-family: tahoma,arial,helvetica,sans-serif;'>"

End

"</body><html>"

 

Choose Holiday or normal

Select each using Parameter

all Types 

(#XI.TISPar("Distinguish normal days from  holidays")# == false) 
OR (#XI.TISPar("caclulate holidays instead of normal days")# == false AND  BeginsWith(@Day type@, "Normal")) 
OR (#XI.TISPar("caclulate holidays instead of normal days")# == true AND  BeginsWith(@Day type@, "Holiday"))
IN GERMAN: 
(#XI.TISPar("Feiertage von normalen Tagen unterscheiden")# == false) 
OR (#XI.TISPar("Feiertage statt normale Tage auswerten")# == false AND  BeginsWith(@Tagestyp@, "Normal"))
OR (#XI.TISPar("Feiertage statt normale Tage auswerten")# == true AND  BeginsWith(@Tagestyp@, "Feiertag"))

 

Structure FROM

For Access via SQL

Text

  XIH.IsNull("Path") == false  && XIH.IsNull("Table") == false  ?
  (" FROM " + XIH.TISTable(#Path#,#Table#)  + " ") :
  (XIH.EqualsPrev("dataset") &&
    XIH.EqualsPrev("date") ?
      XIH.GetString("FROM",-1) : "")

 

Nice Table

for Help

Text

<html>
<table border='1' bordercolor='#E8E8E8' cellpadding='5' cellspacing='0' width='100%'> 
<colgroup> 
<col width='30%' align='left'> 
<col width='20%' align='right'>
</colgroup> 
<thead bgcolor='#C0C0C0'>
<tr><th>Name Eingabewert</th><th align='right'>Wert</th></thead> <tr bgcolor='#FFFFFF'> 
<tr><td>Eingabe</td>
<td align='right'>Wer</td></td> <tr><td>Eingabe</td><td align='right'>Wer</td></td> </table></html>

 

Calculate with times  

E.g., for calculations with a formula if the operator General Calendar is not applicable

Text

  Code for weekday:
  ""
  + (((int) #Von#.DayOfWeek) == 1 ? "1 Mo" : "" )
  + (((int) #Von#.DayOfWeek) == 2 ? "2 Di" : "" )
  + (((int) #Von#.DayOfWeek) == 3 ? "3 Mi" : "" )
  + (((int) #Von#.DayOfWeek) == 4 ? "4 Do" : "" )
  + (((int) #Von#.DayOfWeek) == 5 ? "5 Fr" : "" )
  + (((int) #Von#.DayOfWeek) == 6 ? "6 Sa" : "" )
  + (((int) #Von#.DayOfWeek) == 0 ? "7 So" : "" )

  FOR TIME: #FROM#.Hour.ToString("00") + ":" + #FROM#.Minute.ToString("00") 

  FOR YEAR: (#FROM#.Year).ToString("0000")  FOR Quarter: "Quarter, " + (#FROM#.Year).ToString("0000") +

"/" + System.Convert.ToString(System.Math.Floor((#FROM#.Month - 1.0)/3.0)+1.0)   

 
CODE FOR CALENCAR WEEK:

  (#FROM#.Year).ToString("0000") + ", " +
  (System.Math.Ceiling(
  ((System.Convert.ToDouble(System.DateTime.ParseExact((#FROM#).Year.ToString("0000") 
+ "0101","yyyyMMdd",null).DayOfWeek)) + #FROM#.DayOfYear - 1) / 7.0)).ToString("00")

 

 

Troubleshooting

General

Problem

Frequent Cause

Solutions

Formula operator stops

There is some value that cannot be dealt with, e.g., a wrong date format

See How to find a row that causes the Formula Operator to stop

Calculation is not working at all

  1. Common cause: The formula operator is linked to a column header which no longer exists (the column was deleted or renamed) or a column was added but not passed over several formula operators.
  2. Less common cause: The formula operator is linked to a column which uses „ in the column heading
  1. Open the formula operator and the select "edit" where you see the list of column names. The list is updated and this often solves the problem.
  2. Rename column(s).

Column name is not accepted

Instead of one blank there are several blanks 

Rename column

Calculation yields wrong result (produces integers instead of decimal numbers)

 

The C# Compiler work as follows: Integer divided by integer produces integer.
Example: 5 / 2 = 2 (rounded down).

If you want a decimal as result, the data must be converted to Double.
Example: 5.0/2.0 =2.5

(double) #Integer column# converts an integer to a decimal.

Calculation yields a much too high result

Numbers are concatenated as text, then reconverted to numbers.

Example:

#DayNo# was exported as text, e.g., 19 from General calendar, but in the formula operator: 

120.00 + #DayNo# equals 12,019.00; 

120.00 + System Convert.ToInt32(#DayNo#) equals 139.00.

Cause:

  • The type of DayNo is string, so 120.00 is first converted to text,
  • this text is then combined with a + (string concat!) and – since the result type is presumably a number – reconverted to a number.
  • With System.Convert.ToInt32 the formula operator is explicitly informed prior to the summation, that the +-operator must be used for numbers, because all data types involved are numbers.

For 120.00 it would be good to use System.Convert.ToDouble for conversion, so that the formula operator does not use the +-operator for integers.

Error Message Input string not in correct format

Presumably, the formula yields a text result. BUT the result format in the operator is not set to text, but, e.g., to Integer.

Set the result format to Text.

Operator outputs error message „invalid cast from int32 to DateTime”

Presumably, values in a data column are missing and a „missing value“ is interpreted as 0.

 

Operator outputs error message “invalid cast ...”

In a column there are values of a type that is not expected (e.g., a number where a text is expected).

 

Columns disappear

This is often due to wrong settings of the columns which shall be transferred.

Change column settings (see above).

Unclear error messages

Possible causes

  1. A point (dot) in the column name
  2. Double space (two blanks) in a column name
  3. Missing values (null values) in rows. E.g., when calculating a value of a preceding record.
  4. Number with decimal places has been written in German (e.g., 1,8) and not English spelling (1.8).
  5. A comment field in the formula operator /* text */ contains three dots in a row (“…”).

May be try How to find a row that causes the Formula Operator to stop

 

Editing

Problem

Frequent Cause

Solutions

Formula gets very long

 

Measures against long formulas

  1. Add comments /*This is an example comment*/
  2. Use breaks and blank spaces for formatting, as in the following example

Input of decimal numbers

 

Numbers with decimal places need to be written with decimal points (e.g., 0.5).

Apart from that, fractions with decimal numbers need to be spelled in the language used in the TIS system, e.g., 0,5 in German TIS. Otherwise, 0.5 is interpreted as 5, since in the German culture there are numbers using points (e.g., 1.333.222,12) where the points need to be ignored during the reading process.

 

Using Parameters in the Formula Operator

please see General Parameters in the "Formula Operator v04"

Related topics

C# functions (link)