FrontAccounting forum — Monthly Clientwise Summary Report

Re: Monthly Clientwise Summary Report

2017-06-22T08:23:09Z

In FA 2.3.x it would be like:

SELECT MID(t.tran_date,1,7) AS Period, 
    t.debtor_no, 
    SUM(IF(t.type = 10 OR (t.type = 0 AND t.ov_amount>0),
         ABS(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), 0)) AS charges,
         SUM(IF(t.type != 10 AND NOT(t.type = 0 AND t.ov_amount>0),
         ABS(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1, 0)) AS credits,
         SUM(IF(t.type != 10 AND NOT(t.type = 0 AND t.ov_amount>0), t.alloc * -1, t.alloc)) AS Allocated,
         SUM(IF(t.type = 10, 1, -1) *
             (ABS(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) - ABS(t.alloc))) AS OutStanding
FROM 0_debtor_trans t
WHERE  t.type <> 13
GROUP BY t.debtor_no, MID(t.tran_date,1,7);

The t.type values alluded to above are taken from includes/types.inc file.

Re: Monthly Clientwise Summary Report

2017-06-22T08:04:00Z

FA follows the principle of allocation of payments received towards invoices/charges. There may be other payments from the customer that will not find a place in the payments towards invoices.

reporting/rep101.php is the report you should modify to suit your needs - pivot the reporting across multiple periods

Monthly Clientwise Summary Report

2017-04-12T15:01:11Z

Like,

Jan | Feb | March | ..........................................................................| Dec
ABC Receivable Balance 10 | 15 | 17 | ...............................................................................| 27
Total Payments 5 | 10 | 15 | ...............................................................................| 20
Balance 5 | 5 | 2 | ................................................................................| 7

XYZ Receivable Balance 10 | 15 | 17 | ...............................................................................| 27
Total Payments 5 | 10 | 15 | ...............................................................................| 20
Balance 5 | 5 | 2 | ................................................................................| 7

Where ABC & XYZ are customers, If anybody has developed this sort of reports or know how to do this, kindly help