XpandedReports Computed Columns Tips & Hints

Combine 2 text columns

SUBSTRING: Get first character

SUBSTRING: Get last character

IF: Display warning message

IF: Avoid divide by zero error

DATEDIF: Get days since transaction date

Split text with special characters

Combine 2 text columns

 (Top of Page)
Combine columns of text into a single columns.

Sample: 3 approaches to combining Address columns into a single address column.

1. CONCAT([Bill to Addr 1]," ",[Bill to City]," ",[Bill to State]," ",[Bill to Zip])
2. CONCAT_WS(" ", [Bill to Addr 1], [Bill to City], [Bill to State], [Bill to Zip])
3. [Bill to Addr 1]+" "+[Bill to City]+" "+[Bill to State]+" "+[Bill to Zip]
Note: Quotes are added to provide space between entries.
 

SUBSTRING: Get first character

 (Top of Page) 
SUBSTRING([Type],1,1)
 

SUBSTRING: Get last character

 (Top of Page) 
SUBSTRING([Type],LEN([Type]),1)
 

IF: Display warning message

 (Top of Page)

Sample: Display 'Balance Issue' text 400pt [Total Balance] is greater than 500

IF([Total Balance]>500.00, "Balance Issue", "")
Note: Need to have quotes for the text type output.
 

IF: Avoid divide by zero error

 (Top of Page) 
IF([Qty]!=0, [Amount]/[Qty], 0)
 

DATEDIF: Get days since transaction date

 (Top of Page) 
DATEDIF_XR([Date],NOW_XR(),"D")
Notes:
  • _XR functions are designed for dates in XR.
  • The second parameter can be another date on the report.
  • The third parameter can be changed to get months or years instead of days.
 

Split text with special characters

 (Top of Page)

Example: Split the following data separated by ':'

Interior:Door kit

Get the first part: Interior
    SUBSTRING([Item],0,POSITION(":",[Item])-1)

Get the second part: Door kit
    SUBSTRING([Item],POSITION(":",[Item])+1)