Berikut adalah cara untuk menampilkan umur piutang pada report Customer Statement:
- Pada tampilan report, klik Modify lalu keluarkan satu kolom Calculated Field 7 atau diatasnya dari Available Columns.
- Dari tab Selected Columns & Filters, ceklis kolom tsb.
- Buka Designer, di tab “Data” double click IBX “First_Query”
- 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
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')]
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)