[av_one_full first min_height=\’\’ vertical_alignment=\’av-align-top\’ space=\’\’ row_boxshadow_color=\’\’ row_boxshadow_width=\’10\’ custom_margin=\’aviaTBcustom_margin\’ margin=\’0px\’ margin_sync=\’true\’ mobile_breaking=\’\’ border=\’\’ border_color=\’\’ radius=\’0px\’ radius_sync=\’true\’ padding=\’0px\’ padding_sync=\’true\’ column_boxshadow_color=\’\’ column_boxshadow_width=\’10\’ background=\’bg_color\’ background_color=\’\’ background_gradient_color1=\’\’ background_gradient_color2=\’\’ background_gradient_direction=\’vertical\’ src=\’\’ attachment=\’\’ attachment_size=\’\’ background_position=\’top left\’ background_repeat=\’no-repeat\’ highlight_size=\’1.1\’ animation=\’\’ link=\’\’ linktarget=\’\’ link_hover=\’\’ title_attr=\’\’ alt_attr=\’\’ mobile_display=\’\’ id=\’\’ custom_class=\’\’ aria_label=\’\’ av_uid=\’av-1vrra\’]

[av_heading heading=\’Expressions\’ tag=\’h1\’ style=\’blockquote modern-quote\’ subheading_active=\’\’ show_icon=\’\’ icon=\’ue800\’ font=\’entypo-fontello\’ size=\’\’ av-medium-font-size-title=\’\’ av-small-font-size-title=\’\’ av-mini-font-size-title=\’\’ subheading_size=\’\’ av-medium-font-size=\’\’ av-small-font-size=\’\’ av-mini-font-size=\’\’ icon_size=\’\’ av-medium-font-size-1=\’\’ av-small-font-size-1=\’\’ av-mini-font-size-1=\’\’ color=\’\’ custom_font=\’\’ subheading_color=\’\’ seperator_color=\’\’ icon_color=\’\’ margin=\’5px\’ margin_sync=\’true\’ padding=\’10\’ icon_padding=\’10\’ headline_padding=\’\’ headline_padding_sync=\’true\’ link=\’\’ link_target=\’\’ id=\’\’ custom_class=\’\’ template_class=\’\’ element_template=\’\’ one_element_template=\’\’ av_uid=\’av-ka025un2\’ sc_version=\’1.0\’ admin_preview_bg=\’\’][/av_heading]

[av_hr class=\’custom\’ icon_select=\’no\’ icon=\’ue808\’ font=\’entypo-fontello\’ position=\’center\’ shadow=\’no-shadow\’ height=\’50\’ custom_border=\’av-border-thin\’ custom_width=\’100%\’ custom_margin_top=\’10px\’ custom_margin_bottom=\’10px\’ custom_border_color=\’\’ custom_icon_color=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-3fbm\’ admin_preview_bg=\’\’]

[av_textblock size=\’\’ av-medium-font-size=\’\’ av-small-font-size=\’\’ av-mini-font-size=\’\’ font_color=\’\’ color=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-ka00b6ed\’ admin_preview_bg=\’\’]
Conditional Expressions allow you to create more complex reports, and are useful in certain scenarios.
[/av_textblock]

[av_textblock size=\’\’ av-medium-font-size=\’\’ av-small-font-size=\’\’ av-mini-font-size=\’\’ font_color=\’\’ color=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-h3rsn\’ admin_preview_bg=\’\’]
It is important to note, expressions need to be enclosed in ‘Braces’ { } (sometimes called squiggly brackets), for instance:
[/av_textblock]

[av_codeblock wrapper_element=\’\’ wrapper_element_attributes=\’\’ codeblock_type=\’snippet\’ alb_description=\’\’ id=\’\’ custom_class=\’\’ template_class=\’\’ av_uid=\’av-k9d11x3y\’ sc_version=\’1.0\’]
{ReportData.Value1 * ReportData.Value2}
[/av_codeblock]

[av_textblock size=\’\’ av-medium-font-size=\’\’ av-small-font-size=\’\’ av-mini-font-size=\’\’ font_color=\’\’ color=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-h3rsn\’ admin_preview_bg=\’\’]

[/av_textblock]

[av_hr class=\’custom\’ icon_select=\’no\’ icon=\’ue808\’ font=\’entypo-fontello\’ position=\’center\’ shadow=\’no-shadow\’ height=\’50\’ custom_border=\’av-border-thin\’ custom_width=\’100%\’ custom_margin_top=\’30px\’ custom_margin_bottom=\’30px\’ custom_border_color=\’\’ custom_icon_color=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-kb4rvxet\’ admin_preview_bg=\’\’]

[av_heading heading=\’Text / Strings\’ tag=\’h3\’ link=\’\’ link_target=\’\’ style=\’blockquote modern-quote\’ size=\’\’ subheading_active=\’\’ subheading_size=\’\’ margin=\’\’ padding=\’10\’ color=\’\’ custom_font=\’\’ custom_class=\’\’ id=\’\’ admin_preview_bg=\’\’ av-desktop-hide=\’\’ av-medium-hide=\’\’ av-small-hide=\’\’ av-mini-hide=\’\’ av-medium-font-size-title=\’\’ av-small-font-size-title=\’\’ av-mini-font-size-title=\’\’ av-medium-font-size=\’\’ av-small-font-size=\’\’ av-mini-font-size=\’\’ av_uid=\’av-kfv3j\’][/av_heading]

[av_toggle_container faq_markup=\’\’ initial=\’0\’ mode=\’toggle\’ sort=\’true\’ styling=\’\’ colors=\’\’ font_color=\’\’ background_color=\’\’ border_color=\’\’ toggle_icon_color=\’\’ colors_current=\’\’ font_color_current=\’\’ toggle_icon_color_current=\’\’ background_current=\’\’ background_color_current=\’\’ background_gradient_current_direction=\’vertical\’ background_gradient_current_color1=\’\’ background_gradient_current_color2=\’\’ background_gradient_current_color3=\’\’ hover_colors=\’\’ hover_font_color=\’\’ hover_background_color=\’\’ hover_toggle_icon_color=\’\’ alb_description=\’\’ id=\’\’ custom_class=\’\’ template_class=\’\’ element_template=\’\’ one_element_template=\’\’ av_uid=\’av-kuokj6uu\’ sc_version=\’1.0\’ admin_preview_bg=\’\’]
[av_toggle title=\’String Left {Left(String,value)}\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-1ew5x3\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’]
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)}
[/av_toggle]
[av_toggle title=\’String Right {Right(String,value)}\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-182yrb\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’]
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)}
[/av_toggle]
[av_toggle title=\’String using Left & Right\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-12vwyn\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’]
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)}
[/av_toggle]
[av_toggle title=\’String – Mid {Mid(String, Start, Length)}\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-z8ttr\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’]
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.
[/av_toggle]
[av_toggle title=\’String Contains\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-raltb\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’]
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”,””)}
[/av_toggle]
[av_toggle title=\’Split Strings\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-k7z5b\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’]
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.
[/av_toggle]
[av_toggle title=\’Convert String to Integer {int.Parse(ReportData.FieldName)}\’ tags=\’\’ custom_id=\’\’ element_template=\’\’ one_element_template=\’\’ av_uid=\’av-de7xb\’ sc_version=\’1.0\’]
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)}
[/av_toggle]
[av_toggle title=\’Convert String to Decimal {decimal.Parse(ReportData.FieldName)}\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-aemgv\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’]
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)}
[/av_toggle]
[/av_toggle_container]

[av_hr class=\’custom\’ icon_select=\’no\’ icon=\’ue808\’ font=\’entypo-fontello\’ position=\’center\’ shadow=\’no-shadow\’ height=\’50\’ custom_border=\’av-border-thin\’ custom_width=\’100%\’ custom_margin_top=\’30px\’ custom_margin_bottom=\’30px\’ custom_border_color=\’\’ custom_icon_color=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-kb4rvxet\’ admin_preview_bg=\’\’]

[av_heading heading=\’Date & Time\’ tag=\’h3\’ link=\’\’ link_target=\’\’ style=\’blockquote modern-quote\’ size=\’\’ subheading_active=\’\’ subheading_size=\’\’ margin=\’\’ padding=\’10\’ color=\’\’ custom_font=\’\’ custom_class=\’\’ id=\’\’ admin_preview_bg=\’\’ av-desktop-hide=\’\’ av-medium-hide=\’\’ av-small-hide=\’\’ av-mini-hide=\’\’ av-medium-font-size-title=\’\’ av-small-font-size-title=\’\’ av-mini-font-size-title=\’\’ av-medium-font-size=\’\’ av-small-font-size=\’\’ av-mini-font-size=\’\’ av_uid=\’av-it3cv\’][/av_heading]

[av_toggle_container faq_markup=\’\’ initial=\’0\’ mode=\’accordion\’ sort=\’\’ styling=\’\’ colors=\’\’ font_color=\’\’ background_color=\’\’ border_color=\’\’ colors_current=\’\’ font_color_current=\’\’ background_current=\’\’ background_color_current=\’\’ background_gradient_current_direction=\’vertical\’ background_gradient_current_color1=\’\’ background_gradient_current_color2=\’\’ background_gradient_current_color3=\’\’ hover_colors=\’\’ hover_font_color=\’\’ hover_background_color=\’\’ hover_toggle_icon_color=\’\’ alb_description=\’\’ id=\’\’ custom_class=\’\’ template_class=\’\’ element_template=\’\’ one_element_template=\’\’ av_uid=\’av-ku2ohfdi\’ sc_version=\’1.0\’]
[av_toggle title=\’DateDiff\’ tags=\’\’ custom_id=\’\’ element_template=\’\’ one_element_template=\’\’ av_uid=\’av-12qhqv\’ sc_version=\’1.0\’]
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).
[/av_toggle]
[av_toggle title=\’DateDiff – Exclude Weekends\’ tags=\’\’ custom_id=\’\’ element_template=\’\’ one_element_template=\’\’ av_uid=\’av-ku2ohenp\’ sc_version=\’1.0\’]
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.
[/av_toggle]
[av_toggle title=\’Null Date\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-v2dxj\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’]
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”])}
[/av_toggle]
[av_toggle title=\’Sum Time\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-ope3r\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’]
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.
[/av_toggle]
[av_toggle title=\’Day Of Week {ReportData.Field.DayOfWeek}\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-kn1l3\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’]
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\’
[/av_toggle]
[av_toggle title=\’Minimum Date\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-g10qf\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’]
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.
[/av_toggle]
[av_toggle title=\’Maximum Date\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-bajdz\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’]
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.
[/av_toggle]
[/av_toggle_container]

[av_hr class=\’custom\’ icon_select=\’no\’ icon=\’ue808\’ font=\’entypo-fontello\’ position=\’center\’ shadow=\’no-shadow\’ height=\’50\’ custom_border=\’av-border-thin\’ custom_width=\’100%\’ custom_margin_top=\’30px\’ custom_margin_bottom=\’30px\’ custom_border_color=\’\’ custom_icon_color=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-kb4rvxet\’ admin_preview_bg=\’\’]

[av_heading heading=\’Number & Currency\’ tag=\’h3\’ link=\’\’ link_target=\’\’ style=\’blockquote modern-quote\’ size=\’\’ subheading_active=\’\’ subheading_size=\’\’ margin=\’\’ padding=\’10\’ icon_padding=\’10\’ color=\’\’ custom_font=\’\’ icon_color=\’\’ show_icon=\’\’ icon=\’\’ font=\’\’ icon_size=\’\’ custom_class=\’\’ id=\’\’ admin_preview_bg=\’\’ av-desktop-hide=\’\’ av-medium-hide=\’\’ av-small-hide=\’\’ av-mini-hide=\’\’ av-medium-font-size-title=\’\’ av-small-font-size-title=\’\’ av-mini-font-size-title=\’\’ av-medium-font-size=\’\’ av-small-font-size=\’\’ av-mini-font-size=\’\’ av-medium-font-size-1=\’\’ av-small-font-size-1=\’\’ av-mini-font-size-1=\’\’ av_uid=\’av-dkucn\’][/av_heading]

[av_toggle_container initial=\’0\’ mode=\’accordion\’ sort=\’\’ styling=\’\’ colors=\’\’ font_color=\’\’ background_color=\’\’ border_color=\’\’ colors_current=\’\’ font_color_current=\’\’ background_current=\’\’ background_color_current=\’\’ background_gradient_current_color1=\’\’ background_gradient_current_color2=\’\’ background_gradient_current_direction=\’vertical\’ hover_colors=\’\’ hover_background_color=\’\’ hover_font_color=\’\’ alb_description=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-kb2fy9qs\’]
[av_toggle title=\’Hide 0 unless value entered\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-7ovy7\’]
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.
[/av_toggle]
[av_toggle title=\’Implicitly defining field as a number\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-bj413\’]
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
[/av_toggle]
[/av_toggle_container]

[av_hr class=\’custom\’ icon_select=\’no\’ icon=\’ue808\’ font=\’entypo-fontello\’ position=\’center\’ shadow=\’no-shadow\’ height=\’50\’ custom_border=\’av-border-thin\’ custom_width=\’100%\’ custom_margin_top=\’30px\’ custom_margin_bottom=\’30px\’ custom_border_color=\’\’ custom_icon_color=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-kb4rvxet\’ admin_preview_bg=\’\’]

[av_heading heading=\’Calculations\’ tag=\’h3\’ style=\’blockquote modern-quote\’ subheading_active=\’\’ size=\’\’ av-medium-font-size-title=\’\’ av-small-font-size-title=\’\’ av-mini-font-size-title=\’\’ subheading_size=\’\’ av-medium-font-size=\’\’ av-small-font-size=\’\’ av-mini-font-size=\’\’ color=\’\’ custom_font=\’\’ margin=\’\’ margin_sync=\’true\’ padding=\’10\’ link=\’\’ link_target=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-kb4s3rhp\’ admin_preview_bg=\’\’][/av_heading]

[av_toggle_container initial=\’0\’ mode=\’accordion\’ sort=\’\’ styling=\’\’ colors=\’\’ font_color=\’\’ background_color=\’\’ border_color=\’\’ colors_current=\’\’ font_color_current=\’\’ background_current=\’\’ background_color_current=\’\’ background_gradient_current_color1=\’\’ background_gradient_current_color2=\’\’ background_gradient_current_direction=\’vertical\’ hover_colors=\’\’ hover_background_color=\’\’ hover_font_color=\’\’ alb_description=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-kb2fy9qs\’]
[av_toggle title=\’Adding Field Values {ReportData.Field1 + ReportData.Field2}\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-124svr\’]
You can add the values of ‘Numeric’ and/or ‘Currency’ values together using the following syntax:

{ReportData.Field1 + ReportData.Field2}
[/av_toggle]
[av_toggle title=\’Dividing Field Values {ReportData.Field1 / ReportData.Field2}\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-vuxpj\’]
You can divide the values of ‘Numeric’ and/or ‘Currency’ values together using the following syntax:

{ReportData.Field1 / ReportData.Field2}
[/av_toggle]
[av_toggle title=\’Subtracting Field Values {ReportData.Field1 – ReportData.Field2}\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-rdr8n\’]
You can subtract the values of ‘Numeric’ and/or ‘Currency’ values using the following syntax:

{ReportData.Field1 – ReportData.Field2}
[/av_toggle]
[av_toggle title=\’Multiplying Field Values {ReportData.Field1 * ReportData.Field2}\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-l32qv\’]
You can multiply the values of ‘Numeric’ and/or ‘Currency’ values together using the following syntax:

{ReportData.Field1 * ReportData.Field2}
[/av_toggle]
[av_toggle title=\’Combining arithmetic operators {ReportData.Field1 + ReportData.Field2 * 2}\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-d76u7\’]
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.
[/av_toggle]
[av_toggle title=\’Sum {Sum(ReportData.Field)}\’ tags=\’\’ custom_id=\’\’ av_uid=\’av-ut5j\’]
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)}
[/av_toggle]
[/av_toggle_container]

[av_hr class=\’custom\’ icon_select=\’no\’ icon=\’ue808\’ font=\’entypo-fontello\’ position=\’center\’ shadow=\’no-shadow\’ height=\’50\’ custom_border=\’av-border-thin\’ custom_width=\’100%\’ custom_margin_top=\’30px\’ custom_margin_bottom=\’30px\’ custom_border_color=\’\’ custom_icon_color=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-kb4rvxet\’ admin_preview_bg=\’\’]

[av_heading heading=\’IIF Statements\’ tag=\’h3\’ style=\’blockquote modern-quote\’ subheading_active=\’\’ size=\’\’ av-medium-font-size-title=\’\’ av-small-font-size-title=\’\’ av-mini-font-size-title=\’\’ subheading_size=\’\’ av-medium-font-size=\’\’ av-small-font-size=\’\’ av-mini-font-size=\’\’ color=\’\’ custom_font=\’\’ margin=\’\’ margin_sync=\’true\’ padding=\’10\’ link=\’\’ link_target=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-kb4s217r\’ admin_preview_bg=\’\’][/av_heading]

[av_toggle_container faq_markup=\’\’ initial=\’0\’ mode=\’accordion\’ sort=\’\’ styling=\’\’ colors=\’\’ font_color=\’\’ background_color=\’\’ border_color=\’\’ toggle_icon_color=\’\’ colors_current=\’\’ font_color_current=\’\’ toggle_icon_color_current=\’\’ background_current=\’\’ background_color_current=\’\’ background_gradient_current_direction=\’vertical\’ background_gradient_current_color1=\’\’ background_gradient_current_color2=\’\’ background_gradient_current_color3=\’\’ hover_colors=\’\’ hover_font_color=\’\’ hover_background_color=\’\’ hover_toggle_icon_color=\’\’ alb_description=\’\’ id=\’\’ custom_class=\’\’ template_class=\’\’ element_template=\’\’ one_element_template=\’\’ av_uid=\’av-kuhdn1fz\’ sc_version=\’1.0\’ admin_preview_bg=\’\’]
[av_toggle title=\’About IF Statements \’ tags=\’\’ custom_id=\’\’ av_uid=\’av-a309b\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’]
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.
[/av_toggle]
[av_toggle title=\’Using IIF Statements\’ tags=\’\’ custom_id=\’\’ element_template=\’\’ one_element_template=\’\’ av_uid=\’av-kuhdn07j\’ sc_version=\’1.0\’]
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)}
[/av_toggle]
[av_toggle title=\’Summing IIF Stataments\’ tags=\’\’ custom_id=\’\’ element_template=\’\’ one_element_template=\’\’ av_uid=\’av-kuhdmqvi\’ sc_version=\’1.0\’]
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.
[/av_toggle]
[/av_toggle_container]

[av_hr class=\’custom\’ icon_select=\’no\’ icon=\’ue808\’ font=\’entypo-fontello\’ position=\’center\’ shadow=\’no-shadow\’ height=\’50\’ custom_border=\’av-border-thin\’ custom_width=\’100%\’ custom_margin_top=\’30px\’ custom_margin_bottom=\’30px\’ custom_border_color=\’\’ custom_icon_color=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-kb4rvxet\’ admin_preview_bg=\’\’]

[av_heading heading=\’Tips & Tricks\’ tag=\’h3\’ link=\’\’ link_target=\’\’ style=\’blockquote modern-quote\’ size=\’\’ subheading_active=\’\’ subheading_size=\’\’ margin=\’\’ padding=\’10\’ icon_padding=\’10\’ color=\’\’ custom_font=\’\’ icon_color=\’\’ show_icon=\’\’ icon=\’\’ font=\’\’ icon_size=\’\’ custom_class=\’\’ id=\’\’ admin_preview_bg=\’\’ av-desktop-hide=\’\’ av-medium-hide=\’\’ av-small-hide=\’\’ av-mini-hide=\’\’ av-medium-font-size-title=\’\’ av-small-font-size-title=\’\’ av-mini-font-size-title=\’\’ av-medium-font-size=\’\’ av-small-font-size=\’\’ av-mini-font-size=\’\’ av-medium-font-size-1=\’\’ av-small-font-size-1=\’\’ av-mini-font-size-1=\’\’ av_uid=\’av-1gi0n\’][/av_heading]

[av_textblock size=\’\’ av-medium-font-size=\’\’ av-small-font-size=\’\’ av-mini-font-size=\’\’ font_color=\’\’ color=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-kb4savbf\’ admin_preview_bg=\’\’]
Here are some useful tips & tricks you can use in your reports.
[/av_textblock]

[av_toggle_container faq_markup=\’\’ initial=\’0\’ mode=\’accordion\’ sort=\’\’ styling=\’\’ colors=\’\’ font_color=\’\’ background_color=\’\’ border_color=\’\’ toggle_icon_color=\’\’ colors_current=\’\’ font_color_current=\’\’ toggle_icon_color_current=\’\’ background_current=\’\’ background_color_current=\’\’ background_gradient_current_direction=\’vertical\’ background_gradient_current_color1=\’\’ background_gradient_current_color2=\’\’ background_gradient_current_color3=\’\’ hover_colors=\’\’ hover_font_color=\’\’ hover_background_color=\’\’ hover_toggle_icon_color=\’\’ size-toggle=\’\’ av-desktop-font-size-toggle=\’\’ av-medium-font-size-toggle=\’\’ av-small-font-size-toggle=\’\’ av-mini-font-size-toggle=\’\’ size-content=\’\’ av-desktop-font-size-content=\’\’ av-medium-font-size-content=\’\’ av-small-font-size-content=\’\’ av-mini-font-size-content=\’\’ heading_tag=\’\’ heading_class=\’\’ alb_description=\’\’ id=\’\’ custom_class=\’\’ template_class=\’\’ element_template=\’\’ one_element_template=\’\’ av_uid=\’av-lfy5qyiu\’ sc_version=\’1.0\’ admin_preview_bg=\’\’]
[av_toggle title=\’Field Length\’ tags=\’\’ custom_id=\’\’ av_uid=\’\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’ ]
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)}
[/av_toggle]
[av_toggle title=\’Remove Date/Time from GPS coordinates string\’ tags=\’\’ custom_id=\’\’ av_uid=\’\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’ ]
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)}
[/av_toggle]
[av_toggle title=\’Separating ‘Multiple Select’ string\’ tags=\’\’ custom_id=\’\’ element_template=\’\’ one_element_template=\’\’ av_uid=\’av-lfy5qx7i\’ sc_version=\’1.0\’]
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 rn.
[/av_toggle]
[av_toggle title=\’Test a ‘Date’ or ‘Time’ or ‘Date/Time’ field for no user input\’ tags=\’\’ custom_id=\’\’ av_uid=\’\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’ ]
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”])}
[/av_toggle]
[av_toggle title=\’Test a ‘Text’, ‘Note’, ‘Select’ or ‘Multiple Select’ field for no user input\’ tags=\’\’ custom_id=\’\’ av_uid=\’\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’ ]
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.
[/av_toggle]
[av_toggle title=\’Using a checkmarks (tick – ✓) in forms\’ tags=\’\’ custom_id=\’\’ av_uid=\’\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’ ]
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.
[/av_toggle]
[av_toggle title=\'{Count(DataBand1)}\’ tags=\’\’ custom_id=\’\’ av_uid=\’\’ element_template=\’\’ one_element_template=\’\’ sc_version=\’1.0\’ ]
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.
[/av_toggle]
[av_toggle title=\’!IsFirstPass – hiding pages\’ tags=\’\’ custom_id=\’\’ element_template=\’\’ one_element_template=\’\’ av_uid=\’\’ sc_version=\’1.0\’ ]
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.
[/av_toggle]
[av_toggle title=\’Field Length\’ tags=\’\’ custom_id=\’\’ element_template=\’\’ one_element_template=\’\’ av_uid=\’\’ sc_version=\’1.0\’ title__locked=\’\’ content__locked=\’\’ tags__locked=\’\’ ]
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)}
[/av_toggle]
[av_toggle title=\’Filter records by Date\’ tags=\’\’ custom_id=\’\’ element_template=\’\’ one_element_template=\’\’ av_uid=\’\’ sc_version=\’1.0\’ ]
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.

[/av_toggle]
[av_toggle title=\’Convert value to local currency (in Conditions)\’ tags=\’\’ custom_id=\’\’ element_template=\’\’ one_element_template=\’\’ av_uid=\’\’ sc_version=\’1.0\’ ]
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\”)
[/av_toggle]
[/av_toggle_container]

[av_hr class=\’custom\’ icon_select=\’no\’ icon=\’ue808\’ font=\’entypo-fontello\’ position=\’center\’ shadow=\’no-shadow\’ height=\’50\’ custom_border=\’av-border-thin\’ custom_width=\’100%\’ custom_margin_top=\’30px\’ custom_margin_bottom=\’30px\’ custom_border_color=\’\’ custom_icon_color=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-kb4rvxet\’ admin_preview_bg=\’\’]

[av_heading heading=\’HTML\’ tag=\’h3\’ link=\’\’ link_target=\’\’ style=\’blockquote modern-quote\’ size=\’\’ subheading_active=\’\’ subheading_size=\’\’ margin=\’\’ padding=\’10\’ icon_padding=\’10\’ color=\’\’ custom_font=\’\’ icon_color=\’\’ show_icon=\’\’ icon=\’\’ font=\’\’ icon_size=\’\’ custom_class=\’\’ id=\’\’ admin_preview_bg=\’\’ av-desktop-hide=\’\’ av-medium-hide=\’\’ av-small-hide=\’\’ av-mini-hide=\’\’ av-medium-font-size-title=\’\’ av-small-font-size-title=\’\’ av-mini-font-size-title=\’\’ av-medium-font-size=\’\’ av-small-font-size=\’\’ av-mini-font-size=\’\’ av-medium-font-size-1=\’\’ av-small-font-size-1=\’\’ av-mini-font-size-1=\’\’ av_uid=\’av-a3mmv\’][/av_heading]

[av_textblock size=\’\’ av-medium-font-size=\’\’ av-small-font-size=\’\’ av-mini-font-size=\’\’ font_color=\’\’ color=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-kb4savbf\’ admin_preview_bg=\’\’]
You can use HTML (Hypertext markup language) for text formatting.
[/av_textblock]

[av_textblock size=\’\’ av-medium-font-size=\’\’ av-small-font-size=\’\’ av-mini-font-size=\’\’ font_color=\’\’ color=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-kb4savbf\’ admin_preview_bg=\’\’]
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
[/av_textblock]

[av_codeblock wrapper_element=\’\’ wrapper_element_attributes=\’\’ codeblock_type=\’snippet\’ alb_description=\’\’ id=\’\’ custom_class=\’\’ av_uid=\’av-kb5m9gml\’]
Bold Text
Italic Text
Underline Text
_{Subscript Text}
This is font size 10

= Break
[/av_codeblock]

[/av_one_full]