Reporting Function: TR Function (Returns Account Data)

TR - Transaction

The TR Function is used to return Account Transaction Amount totals.

PARAMETERS:

TR(<pcWhat>,<pcFor>,<piKey>,<pdStart>,<pdEnd>,<pcTerm>,<pcFilter>)

1. <pcWhat> This is the first parameter. This parameter asks which Account information you would like to return. Each code must be entered in quotes. Your options are:

CG - Charges & adjustments/Grand total (Amount+Tax)

CA - Charges & adjustments/Amount total without tax

CT - Charges & adjustments/Tax only

PG - Payments & refunds/Grand total (Amount+Tax)

PA - Payments & refunds/Amount total without tax

PT - Payments & refunds/Tax only

BG - Balance (Charges & adjustments less Payments & refunds)/Grand total (Amount+Tax)

BA - Balance (Charges & adjustments less Payments & refunds)/Amount total without tax

BT - Balance (Charges & adjustments less Payments & refunds)/Tax only

E: - Expression - Add capability to return an expression value

EXAMPLE:
Returns Invoice Fk for a student.

Limits to dAcctTran records in 2004 AND oService=4 AND iATranOacctFk = the ClassEnro Pk AND has an Invoice:

TR("E:iATranInvFk","ST",Pers_iPersonPk,CTOD("01/01/2004"),CTOD("12/31/2004"),"", 'dAcctTran.iATranOservice=4 AND dAcctTran.iATranOacctFk='+ALLTRIM(STR(ClassEnro_iStudClassPk))+

AND NOT EMPTY(dAcctTran.iATranInvFk)' )

2. <pcFor> This parameter is asking for which Service you want to return a Transaction Total. The Codes are listed below and should be entered using "quotes".

ST = Student or Staff

CL = Class

WO = Workplace

FU = Fund

CE = Class Enrollment

Only includes transactions that are related to the passed Class Enrollment Primary Key (ClassEnro_iStudClassPk) plus any transactions that are part of the same invoice.

3. <piKey> This parameter asks for the Primary Key (PK) of the Service item that you have selected in parameter 2. For example, if you have chosen "ST" in parameter 2, then you will need to use the Pers_iPersonPK data item for this parameter.

4. <pdStart> This parameter asks for the date that you want to begin with in including transactions. The default start date is 01/01/1900, if you chose to leave this parameter empty. You would chose to leave this parameter empty if you wanted all transactions from the beginning of the Services account.

5. <pdEnd> This is the last date that you want to include in your selection of transactions. The default is set to 12/31/2100 so that you can include future account postings.

6. <pcTerm> If you only want to include transactions that have taken place within a certain School Term, then you can leave parameters 4 and 5 empty and fill in this parameter with the Term Code you desire. Remember though, this only works for transactions that have taken place within a School Term, not just for a specific Term. Be sure to place this Term Code in "quotes". You should leave this field empty if you have selected dates in parameters 4 and 5.

7. <pcFilter> If you need to filter your transactions using key words in a transaction record, you can do so with this parameter. If you need to list transactions that were only done with Cash, then you could use the "AcctTran" table to create an expression where "Cash" is in the transaction description.

EXAMPLES:

TR('BG','ST',Pers_iPersonPk)

This example simply returns the account balance for a Student.

TR('CG','ST',Pers_iPersonPk,{02/01/2004},{02/28/2004})

This example returns all account charges and adjustments for a Student between a specific date range.

TR('CG','ST', ClassEnro_iStuPersonFk,,,'CQ1')

-TR("PG","ST",Pers_iPersonPk,CTOD("01/01/2004"),CTOD("12/31/2004"),"", '("(Cash)"$dAcctTran.cATranDesc OR "(Check)"$dAcctTran.cATranDesc OR "(Credit Card)"$dAcctTran.cATranDesc ) ANDdAcctTran.cATRantype="PAY" ' )

TR('CG','ST',PERS_iPersonPk,,,'','')

TR("E:iATranInvFk","ST",Pers_iPersonPk,CTOD("01/01/2004"),CTOD("12/31/2004"),"", 'dAcctTran.iATranOservice=4 AND dAcctTran.iATranOacctFk='+ALLTRIM(STR(ClassEnro_iStudClassPk))+' AND NOT EMPTY(dAcctTran.iATranInvFk)' )

-TR("PG","ST",Pers_iPersonPk,,,Term_cTermCode, 'dAcctTran.cATRantype="PAY" AND "(FUND(TELS"$ UPPER(dAcctTran.cATranDesc)' )

TR("E:iATranInvFk","ST",Pers_iPersonPk,,,Term_cTermCode, 'dAcctTran.iATranOservice=4 AND dAcctTran.iATranOacctFk='+ALLTRIM(STR(ClassEnro_iStudClassPk))+' AND NOT EMPTY(dAcctTran.iATranInvFk)' )