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