***Common calculated fields

Linda Moyer
Updated

See also: Calculated Fields Overview

For vw_events_lite, vw_events, vw_events_and_inquiries

Iif([is_lost]==1, 'Lost',
	Iif([is_canceled]==1, 'Cancelled',
		Iif([is_tentative]==1, 'Tentative',
			Iif([is_definite]==1, 'Definite',  
			    Iif([is_prospect]==1,'Prospect' ,'Other' )
			)
		)
	)
)

For events_by_venue, vw_events_by_room

Iif([is_lost]==1, 'Lost',	
	Iif([is_canceled]==1, 'Cancelled',
		Iif([event_is_tentative]==1, 'Tentative',
			Iif([event_is_definite]==1, 'Definite',  
			    Iif([event_is_prospect]==1,'Prospect' ,'Other' )
			)
		)
	)
)

Checkbox for completed tasks

Generally name this calculated field "IsComplete"

Iif([is_completed]=true,'☑','☐')

Status - Confirmed, Prospect, Hold Position # 

booked_status_id:

  • 0 - Tentative
  • 1 - Definite
  • 2 - Prospect
Iif([booked_status_id] = 1, 'Confirmed', 
    Iif([booked_status_id] = 2, 'Prospect', 
        FormatString('Hold {0}', [option_number])) )

Inline Account Address

FormatString('{0}{1}{2}{3}{4}',Iif(IsNullOrEmpty([address_street]),'',FormatString('{0}',[address_street])),Iif(IsNullOrEmpty([address_unit]),'',FormatString(', {0}',[address_unit])),Iif(IsNullOrEmpty([address_locality]),'',FormatString(', {0}',[address_locality])),Iif(IsNullOrEmpty([address_region]),'',FormatString(', {0}',[address_region])),Iif(IsNullOrEmpty([address_postal_code]),'',FormatString(' {0}',[address_postal_code])),Iif(IsNullOrEmpty([address_country]),'',FormatString(', {0}',[address_country])))

Or

FormatString('{0}, {1}, {2}, {3} {4}',[account_address_street],[account_address_unit],[account_address_locality],[account_address_region],[account_address_postal_code])

Single date if Start/End are the same

Show only a single date, if the start and end dates are the same, or a date range, if the start and end dates are different. The date format can be adjusted within each FormatString (e.g. M/d/yyyy for US clients, d/M/yyyy for European).

Iif(GetDate([start_date_time])=GetDate([end_date_time]),
	FormatString('{0:MM/dd/yyyy}',[start_date_time]),
		FormatString('{0:MM/dd/yyyy} - {1:MM/dd/yyyy}',[start_date_time],[end_date_time]))

Date Range

Iif(GetDate([start_date_time])==GetDate([end_date_time]), 
FormatString('{0:MMMM dd, yyyy}',[start_date_time]), 

Iif(GetMonth([start_date_time])==GetMonth([end_date_time]) && GetYear([start_date_time])==GetYear([end_date_time]), 
FormatString('{0:MMMM dd}-{1:dd, yyyy}',[start_date_time],[end_date_time]), 

Iif(GetMonth([start_date_time])!=GetMonth([end_date_time]) && GetYear([start_date_time])==GetYear([end_date_time]), 
FormatString('{0:MMMM dd} - {1:MMMM dd, yyyy}',[start_date_time],[end_date_time]), 
FormatString('{0:MMMM dd, yyyy} - {1:MMMM dd, yyyy}',[start_date_time],[end_date_time]))))
<br>

Current Fiscal Year Date Range (Parameter)

July-June

Start Date
Iif(GetMonth(Today())>=7, AddMonths(LocalDateTimeThisYear(),6 ),AddMonths(LocalDateTimeThisYear(),-6))

End Date
Iif(GetMonth(Today())>=7, AddSeconds(AddMonths(LocalDateTimeNextYear(),6 ),-1),AddSeconds(AddMonths(LocalDateTimeThisYear(),6),-1))


Oct-Sept
Start Date
Iif(GetMonth(Today())>=10, AddMonths(LocalDateTimeThisYear(),9 ),AddMonths(LocalDateTimeThisYear(),-3))

End Date
Iif(GetMonth(Today())>=10, AddSeconds(AddMonths(LocalDateTimeNextYear(),9 ),-1),AddSeconds(AddMonths(LocalDateTimeThisYear(),9),-1))

Today

FormatString('{0:MMMM dd, yyyy}',AddHours(LocalDateTimeNow(),-4))<br>

Sort By Header Text

Make a calculated field "SortByHeaderText" to display in the report header (based on the sort by option selected. Note: ?SortBy and [Parameters.SortBy] are equivalent. This example is from the "Event List (Sort by: Event Date, Date Created, Status, Probability, Est. Revenue) - SUMMARY - Version #1" template report

Iif(?SortBy=='EventDate', 'Event Date',
    Iif(?SortBy=='Status', 'Status',
        Iif(?SortBy=='Probability',  'Probability',
            Iif(?SortBy=='TotalRevenue', 'Total Revenue',
                Iif(?SortBy=='DateCreated','Date Created', 
                    'Est. Rental Revenue'
                )
            )
        )
    )
)

Header text expression for parameter selections

Uses the above "SortByHeaderText" calculated field

FormatString('Events from {0:MM/dd/yyyy} to {1:MM/dd/yyyy}{2}{3}{4}, Sort by {5}', 
    ?StartDate, 
    ?EndDate, 
    Iif(?IncludeDefinites,', Include Definites',''),
    Iif(?IncludeTentatives,', Include Tentatives',''),
    Iif(?IncludeProspects,', Include Prospects',''),
    [SortByHeaderText]
)

Get Month Name (for non-English region accounts)

Iif(IsJanuary([start_date_time]),'January',
	Iif(IsFebruary([start_date_time]),'February',
		Iif(IsMarch([start_date_time]),'March',
			Iif(IsApril([start_date_time]),'April',
				Iif(IsMay([start_date_time]),'May',
					Iif(IsJune([start_date_time]),'June',
						Iif(IsJuly([start_date_time]),'July',
							Iif(IsAugust([start_date_time]),'August',
								Iif(IsSeptember([start_date_time]),'September',
									Iif(IsOctober([start_date_time]),'October',
										Iif(IsNovember([start_date_time]),'November',
											Iif(IsDecember([start_date_time]),'December',
											'')
											)
										)
									)
								)
							)
						)
					)
				)
			)
		)
	)

Split an Item note by string matching

This example is for a note that will have some text, then a line that says "Responsible: " The second line will be split out into it's own calculated field.

To Get the text for the first line:

Iif(<br>   CharIndex('Responsible', [note]) == -1,<br>   [note],<br>   Trim(Substring([note], 0, CharIndex('Responsible', [note])))

To get the text after "Responsible: "

Iif(<br>   CharIndex('Responsible:', [note]) == -1,<br>   '',<br>   Trim(Substring([note], CharIndex('Responsible:', [note])+13)))

Fiscal Year Start Date Calculation Expressions

Create a report parameter named FiscalYearStartMonthParam of type Number (16 bit integer). Set it's value to the appropriate Fiscal Year Start Month number (for example, 1=Jan, 7=July). Also, uncheck the "Visible" checkbox, since it will seldom change and will not need to be shown to the VenueOps Insights user.

Example 1: Based on current date (today) - used by Te Pae Christchurch - Forward Business Report

Note: Assumes a fiscal year that begins on the 1st day of the month, regardless of today's day of the month. 

Iif( GetMonth(Today()) >= ?FiscalYearStartMonthParam, 
AddMonths(LocalDateTimeThisYear(), ?FiscalYearStartMonthParam-1), 
AddMonths(LocalDateTimeThisYear(),-1*( 13-?FiscalYearStartMonthParam)))
Example 2: Based on a user-entered date (example: StartDate parameter) - used by Te Pae Christchurch - KPI Report

Note: Assumes the Start Date day specified will be the fiscal year day.  
Example 1: if startDate of 1-Jan-2019 is specified, and FiscalYearStartMonthParam  is 7, calculation will return 1-Jul-2018.  
Example 2: if startDate of 15-Jan-2019 is specified, and FiscalYearStartMonthParam  is 7, calculation will return 15-Jul-2018. 

Iif( GetMonth(?StartDate) >= ?FiscalYearStartMonthParam, 
AddMonths(?StartDate, -1 * (GetMonth(?StartDate) - ?FiscalYearStartMonthParam) ), 
AddMonths(?StartDate, (?FiscalYearStartMonthParam - (GetMonth(?StartDate)) - 12 )))
Example 3: Not using a Fiscal Year Parameter

Note: This is for the ?StartDate and ?EndDate  expressions within the Date Range parameter (Fiscal Year of October 2022 - September 2023)

?StartDate:
Iif(GetMonth(Today())>=10, AddMonths(LocalDateTimeThisYear(),9 ),AddMonths(LocalDateTimeThisYear(),-3 ))

?EndDate:
Iif(GetMonth(Today())>=10, AddSeconds(AddMonths(LocalDateTimeNextYear(),9 ),-1),AddSeconds(AddMonths(LocalDateTimeNextYear(),-3),-1))

Format Start Time and End Time fields in a Dashboard to remove 1/1/1970

Start Time Only

Iif(substring([start_time],11,5) == '00:00', 'All Day',concat(Iif(substring([start_time],11,2) >= 1 and substring([start_time],11,2)<=11,
        concat(substring([start_time],11,2),':',
            substring([start_time],14,2),' AM'),
                Iif(substring([start_time],11,2) >= 13 and substring([start_time],11,2)<=23,
                concat(substring([start_time],11,2)-12,':',
                substring([start_time],14,2),' PM'),
                    Iif(substring([start_time],11,2) == 12,
                    concat(substring([start_time],11,5),' PM'),'')
                    )
        )
    ))<br>

End Time Only

Iif(substring([end_time],11,2) >= 1 and substring([end_time],11,2)<=11,
        concat(substring([end_time],11,2),':',
            substring([end_time],14,2),' AM'),
                Iif(substring([end_time],11,2) >= 13 and substring([end_time],11,2)<=23,
                concat(substring([end_time],11,2)-12,':',
                substring([end_time],14,2),' PM'),
                    Iif(substring([end_time],11,2) == 12,
                    concat(substring([end_time],11,5),' PM'),'')
                    )
        )

Combined Start and End Time with 'All Day'

Iif(substring([start_time],11,5) == '00:00', 'All Day',concat(Iif(substring([start_time],11,2) >= 1 and substring([start_time],11,2)<=11,
        concat(substring([start_time],11,2),':',
            substring([start_time],14,2),' AM'),
                Iif(substring([start_time],11,2) >= 13 and substring([start_time],11,2)<=23,
                concat(substring([start_time],11,2)-12,':',
                substring([start_time],14,2),' PM'),
                    Iif(substring([start_time],11,2) == 12,
                    concat(substring([start_time],11,5),' PM'),'')
                    )
        )
    ,'-',Iif(substring([end_time],11,2) >= 1 and substring([end_time],11,2)<=11,
        concat(substring([end_time],11,2),':',
            substring([end_time],14,2),' AM'),
                Iif(substring([end_time],11,2) >= 13 and substring([end_time],11,2)<=23,
                concat(substring([end_time],11,2)-12,':',
                substring([end_time],14,2),' PM'),
                    Iif(substring([end_time],11,2) == 12,
                    concat(substring([end_time],11,5),' PM'),'')
                    )
        )
    ))

vw_events_lite Calculated Fields

Long Date Format for Event Dates that may Span Months or Years
 

Iif(GetDate([start_date_time])==GetDate([end_date_time]), FormatString('{0:MMMM dd, yyyy}',[start_date_time]), Iif(GetMonth([start_date_time])==GetMonth([end_date_time]) && GetYear([start_date_time])==GetYear([end_date_time]), FormatString('{0:MMMM dd}-{1:dd, yyyy}',[start_date_time],[end_date_time]), Iif(GetMonth([start_date_time])!=GetMonth([end_date_time]) && GetYear([start_date_time])==GetYear([end_date_time]), FormatString('{0:MMMM dd} - {1:MMMM dd, yyyy}',[start_date_time],[end_date_time]), FormatString('{0:MMMM dd, yyyy} - {1:MMMM dd, yyyy}',[start_date_time],[end_date_time]))))<br>

Calculated Field in order to pull the First Name only
 

Substring([name], 0, CharIndex(' ', [name]))

Account Address Formatted

FormatString('{0}{1}{2}{3}, {4} {5}',
    [account_address_street],
    Iif(IsNullOrEmpty([account_address_unit])==1,'',FormatString(', {0}', [account_address_unit])),
    NewLine(),
    [account_address_locality],
    [account_address_region],
    [account_address_postal_code]
    )<br>

Account Address Formatted with No Breaks

FormatString('{0}{1}, {2}, {3} {4}',
[account_address_street],
Iif(IsNullOrEmpty([account_address_unit])==1,'',
FormatString(', {0}', [account_address_unit])),
[account_address_locality], 
[account_address_region],  
[account_address_postal_code])<br>

City State Zip

FormatString('{0} {1}, {2}',   
[account_address_locality],    
[account_address_region],     
[account_address_postal_code])<br>

Created on Formatted Date

FormatString('{0} day of {1:MMMM, yyyy}',
Iif(FormatString('{0:dd}',[created_on])IN('01'),
FormatString('{0}st',Substring(FormatString('{0:dd}',[created_on]),1,1)),
Iif(FormatString('{0:dd}',[created_on])IN('21','31'),FormatString('{0:dd}st',[created_on]),
Iif(FormatString('{0:dd}',[created_on])IN('02'),FormatString('{0}nd',Substring(FormatString('{0:dd}',[created_on]),1,1)),
Iif(FormatString('{0:dd}',[created_on])IN('22'),FormatString('{0:dd}nd',[created_on]),
Iif(FormatString('{0:dd}',[created_on])IN('03'),FormatString('{0}rd',Substring(FormatString('{0:dd}',[created_on]),1,1)),
Iif(FormatString('{0:dd}',[created_on])IN('23'),FormatString('{0:dd}rd',[created_on]),
Iif(FormatString('{0:dd}',[created_on])IN('04','05','06','07','08','09'),FormatString('{0}th',Substring(FormatString('{0:dd}',[created_on]),1,1)),FormatString('{0:dd}th',[created_on]))))))))
,[created_on])<br>

vw_functions Calculated Fields

Function Time Range

Iif([is_all_day]==1,
    'All Day',
    Iif([start_time]==[end_time],
        FormatString('{0:h:mmt}',[start_time]),
        FormatString('{0:h:mmt}-{1:h:mmt}',[start_time],[end_time])))<br>

vw_booked_spaces Calculated Fields

All Rental Discount Total

[][].sum([discount_total])

All Rental Service Charge Total

[][].sum([service_charge_amount])

All Rental Subtotal

[][].sum([subtotal])

All Rental Tax Total

[][].sum([tax])

All Rental Total

[][].sum([total])

Booked Space Time Formatted

Iif([is_all_day]==1,'All Day',   
Iif(GetTimeOfDay([start_time])==GetTimeOfDay([end_time]), 
FormatString('{0:hh:mm tt}',[start_time]),    
FormatString('{0:hh:mm tt} - {1:hh:mm tt}',[start_time],[end_time])))<br>

Booked Space Usage or Description

Iif([description]==[event_name] Or IsNullOrEmpty([description])==1,
Iif(IsNullOrEmpty([space_usage_name]),'', [space_usage_name]), [description])<br>

vw_event_item_allocations_and_instructions Calculated Fields

Category Name or Package Category Name

Iif(IsNullOrEmpty([package_id]),[category_name],[package_category])<br>

Department Name or Package Department Name

Iif(IsNullOrEmpty([package_id]),[department_name],[package_department])<br>

Event Wide Data Sort Order

This calculated field should be used to sort Event Wide Instructions in a detail band

Iif([is_event_wide]==1,1,2)<br>

Event Wide Instruction or Room Name

Iif([is_event_wide]==1,0,[function_room_name])<br>

Function Date

GetDate([function_start_date_time])

Function Time Range

Iif([function_is_all_day]==1,'All Day'
,Iif([function_start_date_time]==[function_end_date_time],
FormatString('{0:h:mmt}',[function_start_date_time]),
FormatString('{0:h:mmt}-{1:h:mmt}',[function_start_date_time],[function_end_date_time])))<br>

General Instruction or Department Name

Iif([is_general_instruction]==1,'General Instructions',[department_name])<br>

General Instruction or Department Name Sort Order

This calculated field should be used to sort by General Instructions or Department Name in a detail band

Iif([is_general_instruction]==1,0,FormatString('1{1}',[sort_order]))<br>

Item Name or Instruction Title

Iif(IsNullOrEmpty([item_name]),[title],[item_name])<br>

Item or Instruction Pricing Sort Order

This calculated field should be used to sort by the Package Name or Instructions in a detail band

Iif(IsNullOrEmpty([package_name])==0,'AAA',
Iif(IsNullOrEmpty([event_details_instruction_id]),'BBB','CCC'))<br>

Item Note or Instruction Description

Iif(IsNullOrEmpty([item_name])==1,[description],[note])<br>

Optional Item Date Range

Iif(GetDate([start_date_time])==GetDate([end_date_time]),
FormatString('{0:d}',[start_date_time]),
FormatString('{0:d} - {1:d}',[start_date_time],[end_date_time]))<br>

Optional Item Time Range

Iif([start_time]==[end_time],
    FormatString('{0:h:mmt}',[start_time]),
    FormatString('{0:h:mmt} - {1:h:mmt}',[start_time],[end_time]))<br>

Package Function Time

Iif([package_function_start]==[package_function_end],    
FormatString('{0:h:mmt}',[package_function_start]), 
FormatString('{0:h:mmt} - {1:h:mmt}',[package_function_start],[package_function_end]))<br>

Item Quantity Formatted

Iif([is_time_bound]==1,
    FormatString('{0:n0} {1} * {2:n0} {3} @ {4:c2}/{5}',[quantity],[unit_of_measure],[time_quantity],
    Iif([time_quantity]>1,FormatString('{0}s',[unit_name]),
[unit_name]),[price],[unit_name]),
FormatString('{0:n0} {1} @ {2:c2}/{3}',[quantity],[unit_of_measure],[price],[unit_name]))<br>

Sort by Room Name or Event Wide

Iif([is_event_wide]==1,'AAA',[function_room_name])<br>

Related articles

Comments

0 comments

Article is closed for comments.