Column Name = "HIDDENRecordLastCancelled". Column Type/Question ID = "Record Last Cancelled". Tick Exclude on Report
Column Name = "HIDDENRecordLastCancelledDate". Column Type = "Formula (No Format)". Formula = "[VAR_Row].GetDate("HIDDENRecordLastCancelled").ToString("dd/MM/yyyy")" Tick Exclude on Report
Column Name = "HIDDENRecordLastOnCover". Column Type/Question ID = "Record Last On Cover". Tick Exclude on Report
Column Name = "HIDDENRecordLastOnCoverDate". Column Type = "Formula (No Format)". Formula = "[VAR_Row].GetDate("HIDDENRecordLastOnCover").ToString("dd/MM/yyyy")" Tick Exclude on Report
Column Name = "Transaction Date". Column Type = "Formula (No Format)". Formula = "IF(VAR_Row.GetValue("HIDDENRecordStatus")="Cancelled",(VAR_Row.GetValue("HIDDENRecordLastCancelledDate")), (VAR_Row.GetValue("HIDDENRecordLastOnCoverDate")))"
|
Display Cancel Date or Last On Cover Date without the time (dd/MM/yyyy)
Need to create 5 columns (see Column A)
Will also need a column called "HIDDENRecordStatus" to pull in Record Status
|
| [Record Expiry Date].AddDays(1).ToString("dd/MM/yyyy") |
Use this to show renewal dates in reports. |
| [RiskAddressP1_AddressLine1_Value] + IF([RiskAddressP1_AddressLine2_Value].Replace(' ','').Length > 0,', ' + [RiskAddressP1_AddressLine2_Value], '') + IF([RiskAddressP1_TownCity_Value].Replace(' ','').Length > 0,', ' + [RiskAddressP1_TownCity_Value], '') + IF([RiskAddressP1_County_Value].Replace(' ','').Length > 0,', ' + [RiskAddressP1_County_Value], '') |
How to put address on report without spaces and postcode |
| [RiskAddress_AddressLine1_Value] + IF([RiskAddress_AddressLine2_Value].Replace(' ','').Length > 0,', ' + [RiskAddress_AddressLine2_Value], '') + IF([RiskAddress_TownCity_Value].Replace(' ','').Length > 0,', ' + [RiskAddress_TownCity_Value], '') + IF([RiskAddress_County_Value].Replace(' ','').Length > 0,', ' + [RiskAddress_County_Value], '')+ ', ' + [RiskAddress_Postcode_Value] |
How to put address on report without spaces and including postcode |
| IF([CollectionDate_Value].Length=3,[CollectionDate_Value].SubString(0,1),[CollectionDate_Value].SubString(0,2)) |
The first result will show the 1st left hand character of a string, the second result will show the first 2 left hand characters of a string |
| IF(VAR_Row.GetValue("Employer Reference Number")="/Exempt","No","Yes") |
VAR_Row.GetValue("Column Name*")="Value" *Column must be before this question to work |
| [IPT_Rate].ToString("0.00") |
Display the IPT Rate to 2 decimal places in reports (e.g. 12.00%) |
| ([IPT_Rate].ToString("0.00"))+"%" |
To display the IPT rate to 2 decimal places, and include the % symbol after the value |
| IF([TableNameID_RowCount] >0, [TableNameID_DateQuestionID_Row#0_DateValue].ToString("dd/MM/yyyy"), '') |
Pulls through the answer to a Date Question from the 1st row of a Table where the number of table rows is at least 1. Date in format dd/MM/yyyy |
IF([TableNameID_RowCount] >1, [TableNameID_DateQuestionID_Row#1_DateValue].ToString("dd/MM/yyyy"), '')
|
Pulls through the answer to a Date Question from the 2nd row of a Table where the number of table rows is at least 2. Date in format dd/MM/yyyy |
| IF([Record Type] = 'First Premium', 'New Business', [Record Type]) |
Displays as "New Business" in Reports (instead of showing First Premium) if record type is First Premium, otherwise it will pull through as "Adjustment" or "Renewal" |
| IF([Record Status] = 'Cancelled', 'Cancellation', 'MTA') |
Displays as "Cancellation" in Reports (instead of Cancelled) if record status is Cancelled, otherwise it will pull through as MTA |
| [CalcQuestionID_Cover] + [CalcQuestionID_CoverBefore] |
Pull through the correct answer to a calculation question for Adjustments in Reports
e.g. Contents Sum Insured at New Business is £100000, MTA to increase to £200,000. Formula will show Contents Sum Insured as £200,000 in Adjustment Report
NOTE If there are no additional IF conditions in the formula, and the record being reported on is a renewal, the previous record's value will be added to the current record's value - see the next two rows to see how to address this
|
| IF([Record Status]="Cancelled",0,(IF([Record Type] = "Adjustment", ([CalcQuestionID_CoverBefore]), 0) + ([CalcQuestionID_Cover]))) |
This format will set a value of 0 if the record is cancelled, if the record is not cancelled but is an MTA it will add the _CoverBefore value to the _Cover value, and if it is not cancelled and not an MTA (i.e. it is an FP or renewal record) it will just reflect the _Cover value |
| IF([Record Type] = "Adjustment", ([CalcQuestionID_CoverBefore] + [CalcQuestionID_Cover]),[CalcQuestionID_Cover]) |
This formula can be used without the condition that looks at the record status of cancelled - if an admin user has correctly cancelled a policy and the sums insured are zero'd out, using this formula should reflect the correct sum insured of zero when a record has been cancelled, but sometimes user zeroes out the rate applied to a sum insured rather than the sum insured itself, so this value may not always be accurate. Using the formula above will ensure cancelled policies reflect a sum insured of zero |
| [DateQuestion_DateValue].ToString("dd/MM/yyyy") |
Pulls through the answer of date question in the format dd/MM/yyyy
Use with Formula(no format)
|
| [TableNameID_RowCount] |
Pulls through the number of rows in a table question in Reports |
| [Record Expiry Date].Month - [Record Inception Date].Month + (([Record Expiry Date].Year - [Record Inception Date].Year )*12) |
Pulls through the remaining duration (in months) of the policy from most recent inception date until expiry date |
| if([Record Type]='First Premium','New Business',if([Record Type]='Adjustment',IF([Record Status]='Cancelled','Cancellation','Mid Term Adjustment'),'Renewal')) |
Use this as a Formula in reports to customise the wordings displayed for [Record Type] |
| [Client First Name].Substring(0,1) + [Client Surname].Substring(0,1) |
Use this in reports to add the 1st character of one string to the 1st character of another string.
In this example the clients initials will pull though e.g. John Smith would show as JS..
|
| [EmployeeReferenceNumber_ctlTaxOfficeNumber_Value] + "/" + [EmployeeReferenceNumber_ctlERN_Value] |
Use this in a report if the usual system ID for the ERN value isnt working as it should |
IF([StartDate_DateValue] < #2016-08-31#, '038338/01/2015', '038338/01/2016')
OR
IF([Policy Inception]<#2019-04-01#,'038338/01/2015', '038338/01/2016')
|
FOR REPORTS ONLY - use this in a column if a particular reference (in this case an insurer binder ref) needs pulling through which is dependent on the inception date of the risk. Date format must be #YYYY-MM-DD# |
| [CommissionFromInsurer_3665_Rate].ToString("N0") + '%' |
To pull through the commission rate (to no decimal places) with the % symbol |
| IF([ContractWorksCoverRequired_Value]='checked','Covered','Not covered') |
For use with checkbox style question - note, 'checked' must be in lower case, it's case sensitive |
| (VAR_Row.GetDecimal("Column Name A")) + (VAR_Row.GetDecimal("Column Name B")) |
To calculate two column totals using VAR you must use .GetDecimal as opposed to .GetValue |
| Using _xxxBefore values i.e. _CoverBefore |
These values can only be used on calculation questions - these are the only style question where the previous values are saved.
NOTE: if you backdate a test case and then change the previous values to see the impact on a later record, the _xxxBefore value will remain the same as the original entered - any changes you make will not pull through, only the original value
|
| [XXX_CoverBefore] |
As above, this suffix on a system ID will only populate a value to a report if the system ID is a calculation question.
When needing to use this suffix to find a value on an MP scheme the _CoverBefore suffix must be applied to the charge row system ID found in the matrix - using it with a sum insured field on the MP page itslef wil only ever result in a value of 0 being populated to the report
|
[Basic_XXXX_Object].InsurerFullName
[Basic_XXXX_Object].InsurerID
|
To pull through the insurer name for a particular risk group OR
to pull through the insurer ID for a particular risk group
Replace the XXXX with the risk group ID
THIS DOES NOT WORK WHEN THERE ARE MULTIPLE SCHEMES (OR NO SCHEMES AT ALL IF TRYING TO REPORT ON ALL SCHEMES ON A SITE) SET IN THE REPORT CONDITIONS - ONLY ONE SCHEME CAN BE SELECTED
There is no token that can be used to populate a column with the previous year's insurer
|
| IF([Basic_XXXX_Object].InsurerFullName = 'Insurer Full Name','True','False') |
Similar to above, but using a risk group insurer FULL name in a formula
Again, replace the XXXX with the risk group ID and make sure to use the insurer full name and not the short name
|
IF([VAR_Row].GetDate("Record Last On Cover").Year > 1, [VAR_Row].GetDate("Record Last On Cover").ToString("dd/MM/yyyy"), " ")
with the hidden column named "Record Last On Cover"
|
To set the format of the date Record Last On Cover to e.g. 01/01/2018
Please note using the normal "formula" or "formula no format" column without the hidden column will error everytime there is a record never gone on Cover in the period the report is run for
|
| MAX(VAR_Row.GetDecimal('MD Excess'), [FloodExcess_Cover]) |
To set the higher of two values - 'MD Excess' is pulled through from a question, if it is higher than the value in the 'FloodExcess' field then it should be set |
| (IF([Insured_Addresses_Table_RowCount]=1,[Insured_Addresses_Table_Postcode_Row#0_Value],''))+(IF([Insured_Addresses_Table_RowCount]=2,([Insured_Addresses_Table_Postcode_Row#0_Value] + ' / ' + [Insured_Addresses_Table_Postcode_Row#1_Value] ),''))+(IF([Insured_Addresses_Table_RowCount]=3,([Insured_Addresses_Table_Postcode_Row#0_Value] + ' / ' + [Insured_Addresses_Table_Postcode_Row#1_Value] + ' / ' +[Insured_Addresses_Table_Postcode_Row#2_Value]),'')) |
To pull through multiple fields from a column in a table to one column in a report separated by / but without looking untidy |
| IF([Record Type]="First Premium",[Policy Inception].ToString("yyyy"),"")+(IF([Record Type]="Adjustment",[Policy Inception (Relative to MTA)].ToString("yyyy"),""))+(IF([Record Type]="Renewal",[Record Inception Date].ToString("yyyy"),"")) |
Use with 'Formula (No Format)' to pull through the 'Year of Account' value to insurer bordereau |
Use in Formula (no format)
Can be used with a date where using the normal system ID pulls through the time as well as the date - for example [DocumentsSentDate]
You will need to add _DateValue to the formula
[DocumentsSentDate_DateValue].ToString("dd.MM.yy")
|
Allows you to show date or not covered depending on an answer.
.ToString("dd MMMM yyyy") will display 01 January 2018
.ToString("dd MMM yyyy") will display 01 Jan 2018
.ToString("dd MMM yy") will display 01 Jan 18
.ToString("dd.MM.yy") will display 01.01.18
.ToString("yyyy-MM-dd") will display 2018-01-01
.ToString("dd/MM/yyyy") will display 01/01/2018
.ToString("yyyyMMdd") will display 20180101
.ToString('dd-MMM-yyyy') will display 01-Jan-2018
.ToString("MMM dd yy") will display Jan 01 18
If you need to add the time the format is:
.ToString("dd/MM/yyyy HH:mm:ss")
|
| IF([PurchaseDate_DateValue].ToString('dd-MMM-yyyy')='01-Jan-0001','',[PurchaseDate_DateValue].ToString('dd-MMM-yyyy')) |
If a date field value is blank and that system ID is used as a column header the entry will be blank. If you need to format any date results using the above .ToString formatting, the column will reflect a value of 01-Jan-0001 when a date value is blank.
This formula will populate the column with a blank if there is no date value in the field in question, and if there is a date it will populate it in the format you require. The .ToString format that you require the date to be displayed must be mirrored in the conditional element of the formula
|
([Record Last Cancelled]-[Record Inception Date]).Days
|
Use this in Formula (No Format) to show the number of days difference when a policy was cancelled after it incepted |
| ([Record Last NTU Date]-[Record Inception Date]).Days |
Use this in Formula (No Format) to show the number of days difference when a policy was NTUd (whether manually or by the scheme settings kicking in) after it incepted |
| IF([VesselUse]<>"Skipper Charter",[VesselUse],"") |
Not equals a value i.e if VesselUse does not equal Skipper Charter, print all the uses except Skipper Charter |
| [VAR_Row].GetDate("Client DOB").ToString("dd/MM/yyyy") |
You'll need an excluded column above this column in the report to store the Client DOB, then use this formula in the next column to display the DOB into format dd/MM/yyyy (or whatever other format you need) |
Previously:
IF([ProfWorkSplitsDC_WorkType]="Civil Engineering" OR [ProfWorkSplitsDC_WorkType]="Flooring"
OR [ProfWorkSplitsDC_WorkType]="Cladding","True, "False")
Can now use:
IF([ProfWorkSplitsDC_WorkType] IN ("Civil Engineering","Flooring", "Cladding") ,"True","False")
|
If you have an expression that uses multiple answers to populate a value you can instead use the IN expression which will do the same amount of work without needing to type so much
|
| [Referral Cleared Date And Time] |
Can be used in a referral report in conjuction with a column using [Record Created Date And Time] to track how quickly referrals are cleared on a case |
| [Referral Date] |
When used purely as a standard column header will return the date only in the format 19-05-2022
When used within a Formula or Formula (non format) it will return the date in the format 19/05/2022 08:12:18
|
Existing fields:
[Agent Commission (All Years)]
[Policy Total Premium Net (All Years)]
[Premium Total £ (100 Year)]
New fields added 04/2020:
Insurer Commission (All Years)
IPT (All Years)
Policy Fee (All Years)
New field added 02/2022
Premium Total £ (1 Year)
When used in a column header the value will be reflected without being rounded to 2dp - this can be addressed by changing the column type to 'Formula' and using the following:
[Premium Total £ (1 Year)].ToString('N2')
|
Existing:
[Agent Commission (All Years)] - total of broker commission paid on a case across the life span of the policy from the original FP record to date
[Policy Total Premium Net (All Years)] - total net premium (pre-IPT with no commission deducted) across the life span of the policy from the original FP record to date
[Premium Total £ (100 Year)] - total premium incl IPT and any policy fees across the life span of the policy from the original FP record to date
New fields added 04/2020:
[Total Insurer Commission (All Years)] - total of insurer commission paid on a case across the life span of the policy from the original FP record to date
[IPT (All Years)] - total IPT on a case across the life span of the policy from the original FP record to date
[Policy Fee (All Years)] - total policy fees added to a case across the life span of the policy from the original FP record to date
New field added 02/2022:
[Premium Total £ (1 Year)] - total premium incl IPT and any policy fees within the period of cover selected (accounting for any MTA premium). This is helpful in a report where the broker doesnt need to see rows for any MTAs but needs to know the total premium across the record as a whole i.e. a renewals due report, or a snapshot of cases on cover in any given period.
NOTE this field cannot be used in a 'Cases Within Period' type report - it will cause the report to error out.
|
(NOW - [ClientDOB_DateValue]).Days/365
A further column can then be added to round the age up to the next birthday:
(CEILING((VAR_Row.GetDecimal("Client age")/1.0)*1)).ToString("N0")
|
Use with Formula (no format) to calculate the client's age using their date of birth, where [ClientDOB] is the date of birth question
Where this column is headed "Client age", a Formula (no format) column can then round the client's age up to their next birthday
|
IF(VAR_Row.GetValue('RecordStatus')='Cancelled',VAR_Row.GetDate('RecordLastCancelled').ToString('dd/MM/yyyy'),'')
|
[Record Last Cancelled] will populate with the date and time that the MTA status itself was changed to 'Cancelled' - if the report should reflect the effective date of the cancellation then use [Record Inception Date]
This format is needed as a report will error out if the selected date range does not report on any records with a cancelled status but a column header of Record Last Cancelled is used - the only report where this system ID can be used on a standalone basis is a 'By Cancellation Date' type report.
Add two hidden columns - one named 'RecordStatus' where the column header selected is Record Status, and 'RecordLastCancelled' where the column header selected is Record Last Cancelled.
Add a Formula (no format) column using this formula - in this example the date will be formatted as 01/11/2021
|
| [Report Start Date].ToString("MMMM") |
Use with Formula (No format) to detail the bdx month in question |
| [Claim Settled Date].ToString("yyyy") |
Use with Formula (No format) to detail the month in which a claim is settled |
| [MP_RowCount] |
If using this column header you must elect the 'copy row' option to copy it down per row of the MP question if the value is to be used in a calculation. All other MP_ columns headers copy down automatically; this one doesnt |
| [Excess_1_All risks away from premises] |
This format must be used in a formula type column – it wont be found as a standard column header. The spelling of the claim type must match that listed in the ‘Claim types and default excesses’ area of the admin menu.
The insurer name must be specified in the report conditions for the report to be able to pull through the excesses noted specific to the scheme. This does mean separate insurer reports are required.
|
| [Proposal_NoBlanks] |
Some brokers have asked for a report that is effectively a data dump of all questions - we dont have a report that does this, but this system ID can be used in a doc template to pull through all the visible questions and answers that a client / broker has answered in a record |
| |
Where multiple ERNs are present with individual system IDs but the client only wants them reported in one column separated by | like this:
asd/123123 | 123/123456 | 789/999999
In this example the scheme has four questions to capture any ERN numbers. Hidden columns need creating using the system IDs of the questions followed by _ctlTaxOfficeNumber_Value to capture the tax office element of the ERN number, and _ctlERN_Value to capture the long part of the ERN number. The values in these hidden columns are then used to meet the conditions necessary to populate a formula in a final visible column to pull in ERNs where present in the hidden columns
Assuming the system IDs were set as follows and you just used them in one formula as [ERN1_Value] + ' | ' + [ERN2_Value] + ' | ' + [ERN3_Value], but only one ERN number is captured in the record, it would look like this because it would still drop in the /:
ABC/123456 | / |
|
If([Record Status]="On Cover",[Record Last On Cover].ToString("dd/MM/yyyy"),"")
|
If using [Record Last On Cover] in a report where one of the conditions is set to 'Status is On Cover, Cancelled' this format must be used - if not, and if there is an MTA record with a status of 'Cancelled' in the reporting date range, the report will error out because an MTA record that is set to cancelled will never have had an on cover date (cancellation MTAs are either set to pending or cancelled immediately when using the cancel policy function) |
IF(VAR_Row.GetValue('RecordEndorsements').Contains('734'),'Flood cover excluded','')
IF(VAR_Row.GetValue('RecordEndorsements').Contains('734') OR VAR_Row.GetValue('RecordEndorsements').Contains('334') OR VAR_Row.GetValue('RecordEndorsements').Contains('337') OR VAR_Row.GetValue('RecordConditions').Contains('Excluding Flood'),'Flood cover excluded','')
|
Where a flood exclusion endorsement identifier is 734 and the report needs to indicate if flood is not covered because of this exclusion
Create a hidden column using the [Record Endorsements] column header and name it [RecordEndorsements] and a hidden column using the [Record Conditions] column header and name it [RecordConditions], then create a visible column to drop in a formula to look at the endorsement identifiers and populate with a narrative if floood is excluded
Example one is where only one flood exclusion endorsement is found in the library, the second example is where there are multiple flood exclusion endorsements and flood conditions
|
| [Record Created Date] |
To use the creation date of a record in a formula in a report |
This formula errors out because a record didnt have a contents sum insured value entered:
(RoundUp(([SI_Landlords_Contents_ResultBefore]/([SI_Landlords_Contents_CoverBefore]/100))*(1+([RenewalRateLoad]/100)),3))
Changed it to include an action of setting a value of zero if either of the two system IDs used in the division formula had values values of zero:
IF([SI_Landlords_Contents_ResultBefore]=0 OR [SI_Landlords_Contents_CoverBefore]=0,0,(RoundUp(([SI_Landlords_Contents_ResultBefore]/([SI_Landlords_Contents_CoverBefore]/100))*(1+([RenewalRateLoad]/100)),3)))
|
If a formula is structured to divide one value by another you must take into account that one of those values may sometimes be zero - if this is not factored into the formula an error will occur when one or both values are zero so you need to add an IF condition to the formula to account for this possible occurrence and carry out another action. You would use the same format in both reports and rating files if you have any column formula that use the divide function. |
| [InsuredName_Value].ToUpper() |
To convert a field value to all upper case (in this case where the client name field system ID is [InsuredName] |
| Report type 'Cases Within Period' |
To report on all records that are currently on cover i.e. within their period of cover
|
| VAR_Row.GetDate('Invoice Date').AddDays(60).ToString('dd/MM/yyyy') |
To add a particular number of days to a date value found in a previous column (use with 'Formula (no format)' columns
For example, if a column is needed to reflect that the premium is due 60 days after the record incepted
|
[Basic_3563_Cover]
[Basic_3563_CoverBefore]
|
It is possible to reflect the cover total of a cover group using the value seen to the bottom left of a cover group in the matrix, providing the 'This is coverage' option is checked on all sum insured calculation questions that are allocated within that cover group. It is not immediately obvious by using the 'inspect' option on the field you want to pull through - in this example the system ID looks as if it is [Cover_3563]
|
[PreviousAnswer_xxx_Result]
[PreviousAnswer_xxx_Rate]
[PreviousAnswer_xxx_Cover]
|
It can be used in rules, rating and docs - the only place where it can't be used is reports. |
| IF(VAR_Row.GetDecimal("PostcodeLength")=7,[TradingAddress_Postcode_Value].Substring(3,4),[TradingAddress_Postcode_Value].Substring(4,4)) |
To pull through the last 3 digit of the postcode - see screenshot attached |
| (NOW - [Record Created Date]).Days |
Use with Formula (no format) to calculate the number of days before today that, in this case, a record was created
Can be used with other 'date' system IDs
|
[Record Inception Date].ToString("dd/MM/yyyy")
[Record Expiry Date].ToString("dd/MM/yyyy")
|
How to format the Record Inception Date and Expiry Date in the format dd/MM/yyyy eg. 01/11/2022 |
| IF([Has Active Schedule]='True','True','False') |
Use within a formula column to identify if a policy has an active payment schedule attached
NOTE: If a record has more than one payment schedule attached to a record there will be a row of data displayed for the number of payment schedules. The solution is to either cancel the duplicate payment schedule so only one active schedule is present within each case, or remove the 'Has Active Schedule' field from the report to prevent multiple copies of records being included
|
| [Insured_Address_Postcode].Substring(0,2) |
If a single page scheme doesnt have a hidden field that populates with only the first two characters of the postcode but the report requires it create a formula column using this formula, it's not as complex as for an MP scheme |
| Policy Import Ref or Inception Date |
Add one of these columns to an Aged Debts report, they will prevent the report sometimes erroring out when running - it doesnt happen all the time but this should stop it happening. The exclude form export box can be checked if the client doesnt need to see it |
| [Record Last On Cover] |
This column header cannot be used in a Transaction type report, it will cause it to error out. If used within a formula column the report won't error out but the column value will be blank |
| ##([TotalCommissionFromInsurer_Result]-[TotalCommission_IndexLevel0_Result]).ToString("C2")## |
Formula to work out the commission retained by the Broker after deduction of the Agent commission |
| BadBroker |
Reference to BadBroker can be found in the Agent Export report - not in any reports in the report area of a site. The column headed 'BadBroker' indicates if an agent is enabled / disabled - false means it's active, true means it's inactive |
When building a report, we try and use a template that is similar to what we are looking for. You can copy them and edit the copy. This isn´t always possible for reports like a bordereau unless you have already built a similar one for a previous scheme.
.png)
