Useful Formulas

Finding multiple records by a field:

DECODE( {internalid}, 1001, 1, 1002, 1, 1003, 1, 0)

Have this in a Formula(Numeric) field with the result set to “Equal to 1”
This will find records with those internal ids ( 1001, 1002, 1003 ) This can be very useful when you just want to see dozens of records from a spreadsheet for example, and can be used with any field with “strings” instead of numeric internal ids.

Finding Strings:

CASE WHEN {jobmain.jobname} LIKE ‘%whatever%’ AND {item} LIKE ‘%abcitem%’ THEN ‘whatever contains abcitem’ END

Month to Date:

case when {trandate} >= trunc({today},’MONTH’)  then ({quantity}) end

Last Month:

case when {trandate} BETWEEN add_months(trunc({today},’MONTH’),-1) AND trunc({today},’MONTH’) then ({quantity}) end

Six Month Ago

case when {trandate} BETWEEN add_months(trunc({today},’MONTH’),-6) AND add_months(trunc({today},’MONTH’),-5)-1 then ({quantity}) end

Six Month Average

case when {trandate} BETWEEN add_months(trunc({today},’MONTH’),-6) AND trunc({today},’MONTH’) then round(({quantity})/6,2) end