Menampilkan Age of Due pada Report Customer Statement

Berikut adalah cara untuk menampilkan umur piutang pada report Customer Statement:

  1. Pada tampilan report, klik Modify lalu keluarkan satu kolom Calculated Field 7 atau diatasnya dari Available Columns.
  2. Dari tab Selected Columns & Filters, ceklis kolom tsb.
  3. Buka Designer, di tab “Data” double click IBX “First_Query”
  4. Muncul kotak SQL, lalu ganti semua menjadi:
SELECT
CUSTOMER.PERSONNO COLUMNVALUE_1,
CUSTOMER.ID PRIMARYKEYVALUE_10001,
CUSTOMER.NAME COLUMNVALUE_2,
CUSTOMER_CURRENCYID.CURRENCYNAME COLUMNVALUE_3,
CUSTOMER_CURRENCYID.CURRENCYID PRIMARYKEYVALUE_49,
CUSTOMER.ADDRESSLINE1 COLUMNVALUE_4,
CUSTOMER.ADDRESSLINE2 COLUMNVALUE_5,
CUSTOMER.ZIPCODE COLUMNVALUE_6,
CUSTOMER.CONTACT COLUMNVALUE_7,
CUSTOMER.PHONE COLUMNVALUE_8,
CUSTOMER.FAX COLUMNVALUE_9,
CUSTOMER.EMAIL COLUMNVALUE_10,
PERSON_STATEMENTSUM.INVOICENO COLUMNVALUE_11,
PERSON_STATEMENTSUM.INVOICEID PRIMARYKEYVALUE_30001,
PERSON_STATEMENTSUM.SOURCENO COLUMNVALUE_12,
PERSON_STATEMENTSUM.TRANSDATE COLUMNVALUE_13,
PERSON_STATEMENTSUM.DESCRIPTION COLUMNVALUE_14,
PERSON_STATEMENTSUM.DPRIMEAMOUNT COLUMNVALUE_15,
PERSON_STATEMENTSUM.CPRIMEAMOUNT COLUMNVALUE_16,
GET_REPORTCUSTOMFIELD.CUSTOMFIELD1 COLUMNVALUE_17,
GET_REPORTCUSTOMFIELD.PKFIELD PRIMARYKEYVALUE_40001,
GET_REPORTCUSTOMFIELD.CUSTOMFIELD2 COLUMNVALUE_18,
GET_REPORTCUSTOMFIELD.CUSTOMFIELD3 COLUMNVALUE_19,
GET_REPORTCUSTOMFIELD.CUSTOMFIELD4 COLUMNVALUE_20,
GET_REPORTCUSTOMFIELD.CUSTOMFIELD5 COLUMNVALUE_21,
GET_REPORTCUSTOMFIELD.CUSTOMFIELD6 COLUMNVALUE_22,
PERSON_PRIMEANDBASE.BASECURR COLUMNVALUE_23,
PERSON_PRIMEANDBASE.ID PRIMARYKEYVALUE_20001,
PERSON_PRIMEANDBASE.BBAMOUNT COLUMNVALUE_24,
PERSON_PRIMEANDBASE.BBAMOUNTDC COLUMNVALUE_25,
PERSON_PRIMEANDBASE.EBAMOUNT COLUMNVALUE_26,
PERSON_PRIMEANDBASE.EBAMOUNTDC COLUMNVALUE_27,
DATA_COUNT.DATACOUNT COLUMNVALUE_28,
DATA_COUNT.DATACOUNT PRIMARYKEYVALUE_50001,
PERSON_STATEMENTSUM.SOURCETRANS COLUMNVALUE_29,
GET_REPORTCUSTOMFIELD.CUSTOMFIELD7 COLUMNVALUE_30
,GET_TERMSDUE.AGEFRDUE    
FROM
PERSONDATA CUSTOMER
LEFT OUTER JOIN PERSON_PRIMEANDBASE (CUSTOMER.ID, cast(:PARAMDATE1 as date) -1, :PARAMDATE2) ON CUSTOMER.ID=PERSON_PRIMEANDBASE.ID
LEFT OUTER JOIN PERSON_STATEMENTSUM (CUSTOMER.ID, CUSTOMER.CURRENCYID, :PARAMDATE1, :PARAMDATE2) ON PERSON_STATEMENTSUM.PPERSONID=CUSTOMER.ID AND  PERSON_STATEMENTSUM.BASECURR=PERSON_PRIMEANDBASE.BASECURR
LEFT OUTER JOIN GET_REPORTCUSTOMFIELD ON 1=1
LEFT OUTER JOIN DATA_COUNT ON 1=1
LEFT OUTER JOIN CURRENCY CUSTOMER_CURRENCYID ON CUSTOMER.CURRENCYID=CUSTOMER_CURRENCYID.CURRENCYID
LEFT OUTER JOIN ARINV ON PERSON_STATEMENTSUM.INVOICEID=ARINV.ARINVOICEID                                                                                               
LEFT OUTER JOIN GET_TERMSDUE (PERSON_STATEMENTSUM.INVOICEID, PERSON_STATEMENTSUM.TRANSDATE, CURRENT_DATE, ARINV.TERMSID) ON GET_TERMSDUE.TERMID=ARINV.TERMSID    
WHERE 1=1 
AND (CUSTOMER.PERSONTYPE = 0) AND ((PERSON_PRIMEANDBASE.EBAMOUNT <> 0) OR (PERSON_PRIMEANDBASE.EBAMOUNTDC <> 0)) AND PERSON_STATEMENTSUM.INVOICEID IS NOT NULL AND (CUSTOMER.PRINTSTATEMENT = 1)
ORDER BY
CUSTOMER.PERSONNO ASC,
PERSON_PRIMEANDBASE.BASECURR ASC,
PERSON_STATEMENTSUM.TRANSDATE ASC,
PERSON_STATEMENTSUM.SOURCETRANS ASC

Untitled1

Jika sudah, klik tanda ceklis.

5. Pindah ke tab “Report Page”
6. Pada baris “Master Data” di kolom Calculated Field tadi, klik dan tambahkan Memo

[IIF(<First_Query."AGEFRDUE">=null,'0 Days',IntToStr(<First_Query."AGEFRDUE">) + ' Days')]

Untitled2

7. Selesai, dan simpan report dengan format FR3

CATATAN : Nilai Age From Due dihitung dari tanggal sistem saat membuka report (tidak seperti report Outstanding Invoices yang dihitung dari parameter tanggal report).

(Available for V4 & V5)