• From: India
  • Registered: 11/25/2016
  • Posts: 8

Topic: Monthly Clientwise Summary Report

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

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Monthly Clientwise Summary Report

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

apmuthu's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Monthly Clientwise Summary Report

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.

apmuthu's Website