Calc Field Expressions

The Calc section on the Data Tab of the report writer is used to create values that are not already contained within the datapipe.  This can include calculating a number, such as the net invoice amount on a customer billing, or creating a new text field by combining multiple text fields such as the combination of the street address, city, state and zip of a vendor.  Listed below are SQL expressions that can be used in the Calc field along with a small example of calculations that you can use as a guide to creating your own calculations.  Bear in mind that field names differ from table to table so be sure to reference the field names that are appropriate in the tables that you are using.

Useful SQL Expressions for Calc Field Expressions

(+) - add numbers or string fields

(-) – subtract numbers

(*) – multiply numbers

(/) – divide numbers

isnull(fieldname,defaultvalue) – test for Null Field

convert(fieldname,sql_char) – convert data type to character

rtrim(fieldname) – trim trailing spaces

round(fieldname,decimalplaces) – round a number field

right(fieldname,charactercount) – rightmost number of characters

substring(fieldname,startingpos,length) – get portion of string field

iif(conditiontest,trueresult,falseresult)

Hint 1:  If the field name contains spaces, then the field name must be listed with quotes around the field name.  (ie.  "total contract amount")

Hint 2:  If the same field name is contained in more than one table within the same report, then be sure to reference the table name along with the field name.  (ie.  na_no  vs.  clientaddresslist.na_no)

•EXTRACTING THE DECIMAL FROM A GLACCOUNT

round(ABS(glaccount)-FLOOR(ABS(glaccount)),2)

  Result:  If the glaccount is 52999.45 then the result is .45

•EXAMPLE OF TESTING FOR DIVISOR IS NULL - An error will occur if the divisor equals zero or is null therefore you should always include the test in your formula

iif(isnull(fieldname2,0)=0,0,round(fieldname/fieldname2,2))

iif(isnull("total contract amount",0)=0,0,round("total cost"/"total contract amount",2))

Result:  If the total contract amount equals zero, then zero, otherwise divide the total cost by the total contract amount.

•EXAMPLE OF ‘ISNULL’ – USE THIS WHEN A FIELD IS BLANK

Workers comp description

iif(isnull(comp,'')='','N/A - Exempt','Comp Code: '+rtrim(convert(comp,sql_char))+' '+rtrim(comp_desc)+'     Rate: '+iif(isnull(comp_rate,0)=0,'$0 per $100','$'+rtrim(convert(comp_rate,sql_char))+' per $100'))

Result:  If the workers comp code is blank, then the description 'N/A - Exempt' will be displayed, otherwise the w/c code, description and rate will be displayed.

•CHECK FORMAT - VENDOR ADDRESS - Add these formulas to the check format and replace VendorAddress2 with AddrLine2; replace VendorCity, State and Zip with AddrLine3.

AddrLine2:      iif(trim(isnull(vendoraddress2,''))='',trim(vendorcity)+', '+trim(VendorState)+' '+trim(vendorzip),vendoraddress2)

AddrLine3:      iif(trim(isnull(vendoraddress2,''))<>'',trim(vendorcity)+', '+trim(VendorState)+' '+trim(vendorzip),'')

•MEMO FIELDS - If the memo field isn’t printing properly, try limiting the number of characters that can be displayed in the field.

substring(trim(notes),1,250)

•RTRIM - Rtrim removes the blank spaces that may appear at the end of a field.

rtrim(convert(truncate(glaccount,0),SQL_CHAR))+'.'+Right(rtrim(convert(truncate(glaccount*100,0),sql_char)),2)+ ' - '+gldesc

rtrim(isnull(e_last,''))+', '+rtrim(isnull(e_first,''))+' '+rtrim(isNull(e_middle,''))

rtrim(prcertifiedtable.e_last)+', '+rtrim(prcertifiedtable.e_first)+’ ‘+rtrim(isnull(prcertifiedtable.e_middle,''))

•LAST FOUR DIGITS OF SOCIAL SECURITY NUMBER

substring(soc_sec,8,4)