Expressions

Conditional Expressions allow you to create more complex reports, and are useful in certain scenarios.

It is important to note, expressions need to be enclosed in ‘Braces’ { } (sometimes called squiggly brackets), for instance:

{ReportData.Value1 * ReportData.Value2}

Expressions can be complex. If you require assistance with a form and want to use Conditional Expressions, please log a support request, and we will be happy to help you.

Text / Strings

String Left {Left(String,value)}

If you wish to return only a certain amount of characters to the left of the string, use the following expression.
{Left(String,value)}
The String is the field name, e.g ReportData.Location_LocationDetails
The value, is the number of characters to be shown from the left of the string.
Example:
{Left(ReportData.Location_LocationDetails,20)}

String Right {Right(String,value)}

If you wish to return only a certain amount of characters to the right of the string, use the following expression.
{Right(String,value)}
The String is the field name, e.g ReportData.Location_LocationDetails
The value, is the number of characters to be shown from the right of the string.
Example:
{Right(ReportData.Location_LocationDetails,15)}

String using Left & Right

You can combine string ‘methods’, for instance, ‘Left’ and ‘Right’ in an expression:
In the example below, from a signature detail field, we can extract the ‘date & time’ element from the string by using the following expression:
{Left(Right(ReportData.Location_LocationDetails,19),10)}

String – Mid {Mid(String, Start, Length)}

If you wish to return characters midway through the string, use the following expression.
{Mid(String, Start, Length)}
The String is the field name, e.g ‘ReportData.landline_no’
The start, is the number of characters to start the string, the length is the number of characters required from the start.
Example: Assuming you have a field to capture landline numbers – in this example 0208 123456, you don’t want to show the dialling code, you can use the ‘Mid’ expression as follows:
{Mid(ReportData.landline_no,5,6)}
This would return ‘123456’ only. Note: Spaces count as a character.

String Contains

If you wish to return part of a string from a field, you can use the ‘Contains’ expression.
This is useful where the string is not either Left or Right within the string.
For example, assume the ‘Expense Type’ option is ‘Diesel, Petrol or Oil’, and you want to return a value of ‘Unleaded Petrol’ if ‘Expense Type’ contains ‘Petrol’. You can use the following expression:
{IIF(ReportData.ExpenseType.Contains(“Petrol”),”Unleaded Petrol”,””)}

Split Strings

You can split a string by using the ‘Split’ expression.
For instance, if you had a field called ‘Project Details’ and the string is ‘123456 | 9876543 | 5 Lower Street London | Repair’, then you could split the string at the pipe ‘|’ character, as below:
{Trim(ReportData.n1__Project_Details.Split(‘|’)[2])}
This splits the string at the position 2 (Note: positions starts at 0, not 1) and would return ‘5 Lower Street London’
Therefore:
{Trim(ReportData.n1__Project_Details.Split(‘|’)[0])} – Returns ‘123456’
{Trim(ReportData.n1__Project_Details.Split(‘|’)[1])} – Returns ‘9876543’
{Trim(ReportData.n1__Project_Details.Split(‘|’)[2])} – Returns ‘5 Lower Street London’
{Trim(ReportData.n1__Project_Details.Split(‘|’)[3])} – Returns ‘Repair’
By using ‘Trim’ in the expression, this removes blank spaces.

Convert String to Integer {int.Parse(ReportData.FieldName)}

There are occasions where a numeric value is captured in a Text field, and you need to convert the value to an integer for calculation purposes.
To convert a text string to an integer, use the ‘int.Parse’ expression. For instance: {int.Parse(ReportData.Score_1) + int.Parse(ReportData.Score_1)}

Convert String to Decimal {decimal.Parse(ReportData.FieldName)}

There are occasions where a numeric value is captured in a Text field, and you need to convert the value to a Decimal for calculation purposes.
To convert a text string to a decimal, use the ‘decimal.Parse’ expression. For instance: {decimal.Parse(ReportData.Score_1) + decimal.Parse(ReportData.Score_1)}

Date & Time

DateDiff

Using DateDiff allows you to show the difference between 2 dates/times. For instance, if you wanted to show the time between a ‘Start’ time and an ‘End Time’, you can use the DateDiff to calculate the time span:
{DateDiff(ReportData.TimeEnd, ReportData.TimeStart)}
If the TimeStart is 09:00 and the TimeEnd is 12:00, the DateDiff would return 03:00 (being 3 hours).

DateDiff – Exclude Weekends

If you require the difference between 2 dates/times but exclude weekends, you can use the following expression:
{(1 + (int)((ReportData.End – ReportData.Start).TotalDays) – 2 * (((1 + (int)((ReportData.End – ReportData.Start).TotalDays)) + (int)(ReportData.Start.DayOfWeek)) / 7) – (ReportData.Start.DayOfWeek == DayOfWeek.Sunday ? 1 : 0) + (ReportData.End.DayOfWeek == DayOfWeek.Saturday ? 1 : 0)) – 1}
Replace ‘ReportData.Start’ and ‘ReportData.End’ with the field names used in your report.

Null Date

If you want to check if a date has not been entered, and return a string (e.g ‘No Date Entered’), you can easily do this by using the DBNull expression, as below:
{IIF(ReportData[“StartDate”] == DBNull.Value,”No Date Entered”,ReportData[“StartDate”])}

Sum Time

If you are using Header / Footer Groups, you can sum the difference between 2 dates, and will return a total for all the times in your DataBand.
{SumTime(DateDiff(ReportData.TimeEnd, ReportData.TimeStart))}
This would need to be entered in the Group Footer Band.

Day Of Week {ReportData.Field.DayOfWeek}

If you want to return the day of the week from a ‘Date’ field, you can use the ‘.DayOfWeek’ expression.
For example, if you your field name is ‘Date’, using {ReportData.DateDayOfWeek} would return the day, e.g ‘Tuesday’

Minimum Date

If you want to return the minimum date, you can use the ‘MinDate’ expression.
For example, if you your field name is ‘Date’, using {MinDate(ReportData.DateDayOfWeek)} would return the earliest date.

Maximum Date

If you want to return the maximum date, you can use the ‘MaxDate’ expression.
For example, if you your field name is ‘Date’, using {MaxDate(ReportData.DateDayOfWeek)} would return the last date.

Number & Currency

Hide 0 unless value entered

A Number or Currency field will report ‘0’ in Report Builder if no value is entered in the field.
There are occasions you may need to report if a zero was input in a field. You can do this will the expression below:
{ReportData[“Number”] == null || ReportData[“Number”] == DBNull.Value ? “” : ReportData. Number.ToString()}
Note: Replace ‘Number’ with the field name
This expressions checks the value input in the database and will only show a zero if it was input in a Number or Currency field by the user.

Implicitly defining field as a number

If you are calculating field(s) and receive an error message saying there is an invalid field type match, you may need to define the field(s).
For instance:
{(int)ReportData.Value)} – define field as an integer
{(decimal)ReportData.Value)} – define field as a decimal
{(float)ReportData.Value)} – define field as a float

Calculations

Adding Field Values {ReportData.Field1 + ReportData.Field2}

You can add the values of ‘Numeric’ and/or ‘Currency’ values together using the following syntax:
{ReportData.Field1 + ReportData.Field2}

Dividing Field Values {ReportData.Field1 / ReportData.Field2}

You can divide the values of ‘Numeric’ and/or ‘Currency’ values together using the following syntax:
{ReportData.Field1 / ReportData.Field2}

Subtracting Field Values {ReportData.Field1 – ReportData.Field2}

You can subtract the values of ‘Numeric’ and/or ‘Currency’ values using the following syntax:
{ReportData.Field1 – ReportData.Field2}

Multiplying Field Values {ReportData.Field1 * ReportData.Field2}

You can multiply the values of ‘Numeric’ and/or ‘Currency’ values together using the following syntax:
{ReportData.Field1 * ReportData.Field2}

Combining arithmetic operators {ReportData.Field1 + ReportData.Field2 * 2}

You can combine arithmetic operators in your calculations, for example:
{ReportData.Field1 + ReportData.Field2 * 2}
Replace ‘Field 1’ and ‘Field 2’ with the actual field name.

Sum {Sum(ReportData.Field)}

Sum(band, expression)
You can use the SUM expression to return the total value of a numeric or currency field. You would need to use Group Header and Group Footer Bands for this calculation to work.
Example 1:
{Sum(ReportData.Mileage)}
Example 2:
{Sum(ReportData.Total + ReportData.VAT)}

IIF Statements

About IF Statements

IF statement is defined as a function which “checks whether a condition is met, returns one value if True and another value if False”.
In plain simple English IF function is an instruction that checks any condition, if the condition is found to be TRUE then it returns a predefined value however if the condition is False, it returns a different predefined value.
In Form Builder, we use the syntax ‘IIF’ – for instance, {IIF(ReportData.ExpenseType == “Petrol”, “Fuel”, “”}
In the above example, if the returned data is ‘Petrol’, then the text is shown as ‘Fuel’ in the form.

Using IIF Statements

You can use the IIF expression to return a value in a field based on the value of the data:
{IIF(Condition, Value1, Value2)}
Using IIF, you can check a Condition, and if it is true then return a value (value1), otherwise return a different value (value2).
In the example below, the ‘Total_inc_VAT’ value is returned where the ‘Expense_Type’ value returns ‘Parking’, otherwise it returns a nil value.
{IIF(ReportData.Expense_Type == “Parking”,ReportData.Total_inc_VAT,null)}

Summing IIF Stataments

SumIf(band, expression, condition)
You can use the SUMIF expression to return the total value of a numeric or currency field, based on the value of the data. You would need to use Group Header and Group Footer Bands for this calculation to work.
Example:
{SumIf(ReportData.Total_inc_VAT, ReportData.Expense_Type == “Parking” )}
In the above example, the ‘Total_inc_VAT’ value is returned where the ‘Expense_Type’ value returns ‘Parking’, otherwise it returns a nil value.

Tips & Tricks

Field Length

You can test to see if the field length has a certain amount of characters in the input, and return a string. In the example below, we check the ‘Assumptions’ field is zero (‘0’) and then return ‘No Assumptions noted’ if it has a zero length:
{IIF(ReportData.Assumptions.Length == 0, “No Assumptions noted”, ReportData.Assumptions)}

Remove Date/Time from GPS coordinates string

If you want to remove the Date/Time from the GPS coordinates String, you can use the ‘Remove’ expression.
The example below removes the 21 characters (e.g Date/time) from the ‘LocationDetails’ string:
{ReportData.n1__Click_on_site_arrival_Job_1_LocationDetails.Remove(ReportData.n1__Click_on_site_arrival_Job_1_LocationDetails.Length – 21)}

Separating ‘Multiple Select’ string

Using the ‘Multiple Select’ field in Data Collector allows the user to select multiple options. For instance:
Coffee
Tea
Milk
Sugar
If a user selects Coffee, Milk and Sugar, the data is returned as a string: ‘Coffee, Milk, Sugar’
In Form Builder, you may prefer to show the returned string on different lines in your form, e.g.:
Coffee
Milk
Tea
This is achievable by using the ‘Replace’ expression:
{ReportData.Drinks.Replace(“, “, “\r\r\n”)}
This expression will replace the comma with a carriage return and line break. These are represented by \r\r\n.

Multiplying Field Values {ReportData.Field1 * ReportData.Field2}

You can multiply the values of ‘Numeric’ and/or ‘Currency’ values together using the following syntax:
{ReportData.Field1 * ReportData.Field2}

Test a ‘Date’ or ‘Time’ or ‘Date/Time’ field for no user input

You can test a ‘Date’ or ‘Time’ or ‘Date/Time’ Field Type to see if a user has input a value, and if not, return another string in the report.
For instance:
Assume you have a field called ‘Start Date’, and in the report you want to return the Date / Time / Date/Time if the user entered some data, or some default text if the user did not enter any date.
You could write an expression like this:
{IIF(ReportData[“StartDate”] == DBNull.Value,”No Date Entered”,ReportData[“StartDate”])}

Test a ‘Text’, ‘Note’, ‘Select’ or ‘Multiple Select’ field for no user input

You can test a ‘Text’, ‘Note’, ‘Select’ or ‘Multiple Select’ Field Type to see if a user has input a value, and if not, return another string in the report.
For instance:
Assume you have a field called ‘Assumptions’ as a ‘Text or ‘Note’ Field Type, and in the report you want to return the text/note if the user entered some data, or some default text if the user did not enter any text.
You could write an expression like this:
{IIF(ReportData.Assumptions.Length == 0, “No Assumptions noted”, ReportData.Assumptions)}
This would show ‘No Assumptions noted’ in the field if the user didn’t enter any text in the field, or would show the text they entered if it was input by the user.

Using a checkmarks (tick – ✓) in forms

If you wish to use a checkmark (tick – ✓) in forms, you can easily do this using the ‘Wingdings’ font.
The example below checks the ‘Audit Result’ field returns a ‘N/A’, and if so, returns a checkmark if true and a cross (x) if false. You will need to specify “ü” to return the checkmark and “û” for a x.
{IIF(ReportData.Audit_Result.Contains(“N/A”),”ü”,”û”)}
Hint: If the checkmark is showing when previewing the report, but not printing on a PDF, check the font size used. If the font size is too big, although it will preview fine, it may not show on a printed PDF. If this is the case, reduce the font size.

{Count(DataBand1)}

In a footer, you can count the number of records within a DataBand.
You can use the expression: {Count(DataBand1)}
Note: Replace ‘DataBand1’ with the DataBand name if different.

!IsFirstPass – hiding pages

If you need to hide a page in your report that contains formulas, disabling the page will also disable any calculations.
However, you can set a Condition on the page, with 2 ‘Highlight Conditions’ which will enable the formulas to calculate, but prevent the page from being visible:
The first condition should have an expressions with ‘!IsFirstPass’ and the ‘Component Is Enabled’ unchecked.
The second condition should have an expressions with ‘IsFirstPass’ and the ‘Component Is Enabled’ checked.

Note: You must set the ‘Number of Pass’ to be ‘Double Pass’ in the global report setting for the above to work.

Filter records by Date

You can filter records by current date, or + / – current date using the following expression:
ReportData.Date_of_Completion == Today.AddDays(-1)
In this example, we are filtering the records where ‘Date of Completion field’ is yesterday.
This is entered as a Filter condition, as per the example below.

Convert value to local currency (in Conditions)

If you are assigning a value in a Condition and need the value to show in local currency you can use .ToString(“C”)
For example: ReportData.TotalPaid.ToString(“C”)

Here are some useful tips & tricks you can use in your reports.

HTML

You can use HTML (Hypertext markup language) for text formatting.

Some examples of formatting are shown below, however, you can refer to more HTML Text Formatting here: https://www.w3schools.com/html/html_formatting.asp

<b>Bold Text</b>
<i>Italic Text</i>
<u>Underline Text</u>
<sub>Subscript Text</sub>

You can use HTML (Hypertext markup language) for text formatting.

Some examples of formatting are shown below, however, you can refer to more HTML Text Formatting here: https://www.w3schools.com/html/html_formatting.asp

<b>Bold Text</b>
<i>Italic Text</i>
<u>Underline Text</u>
<sub>Subscript Text</sub>
<font size="10">This is font size 10</font>
<br> = Break