Create AR Aging Report & AR Aging Detail Report using Saved Search

Formula for Current Open Balance:

• Case When substr({amount},1,1) = ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) < 1 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) < 1 then {amountremaining} else 0 end

Formula for Open Balance = 30 days:

• Case When substr({amount},1,1) = ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 1 and 30 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 1 and 30 then {amountremaining} else 0 end

Formula for Open Balance = 60 days

• Case When substr({amount},1,1) = ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 31 and 60 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 31 and 60 then {amountremaining} else 0 end

Formula for Open Balance = 90 days

• Case When substr({amount},1,1) = ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 61 and 90 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 61 and 90 then {amountremaining} else 0 end

Formula for Open Balance > 90 days

• Case When substr({amount},1,1) = ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) > 90 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) > 90 then {amountremaining} else 0 end

Formula for Total Open Balance

• (Case When substr({amount},1,1) = ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) < 1 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) < 1 then {amountremaining} else 0 end)+ (Case When substr({amount},1,1) = ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 1 and 30 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 1 and 30 then {amountremaining} else 0 end) + (Case When substr({amount},1,1) = ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 31 and 60 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 31 and 60 then {amountremaining} else 0 end) + (Case When substr({amount},1,1) = ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 61 and 90 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 61 and 90 then {amountremaining} else 0 end) + (Case When substr({amount},1,1) = ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) > 90 then ({amountremaining}*-1) When substr({amount},1,1) <> ‘-‘ and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) > 90 then {amountremaining} else 0 end)