question

Hi all, I have this query Select a.TERM, a.STUDENT_ID, a.AR_TYPE, Sum(a.INVOICE_AMT) - Sum(a.PAYMENT_AMT) As TERM_BALANCE, STUDENT_CURRENT_TYPE_VIEW.STUDENT_CURRENT_TYPE, b.STA_CLASS, PREFERRED_MAIL_VIEW.[First Name], PREFERRED_MAIL_VIEW.[Last Name], PREFERRED_MAIL_VIEW.PERSON_STATUS, PREFERRED_MAIL_VIEW.[Address Line 1], PREFERRED_MAIL_VIEW.[Address Line 2], PREFERRED_MAIL_VIEW.[Address Line 3], PREFERRED_MAIL_VIEW.City, PREFERRED_MAIL_VIEW.State, PREFERRED_MAIL_VIEW.Zip, PREFERRED_MAIL_VIEW.Country, PREFERRED_MAIL_VIEW.[Preferred Email], TERMS.TERM_START_DATE From (Select AR_INVOICES.INV_TERM As TERM, AR_INVOICES.INV_PERSON_ID As STUDENT_ID, AR_INVOICES.INV_AR_TYPE As AR_TYPE, 0 As PAYMENT_AMT, IsNull(AR_INVOICE_ITEMS.INVI_EXT_CHARGE_AMT, 0) - IsNull(AR_INVOICE_ITEMS.INVI_EXT_CR_AMT, 0) As INVOICE_AMT From AR_INVOICE_ITEMS Inner Join AR_INVOICES On AR_INVOICE_ITEMS.INVI_INVOICE = AR_INVOICES.AR_INVOICES_ID Where AR_INVOICES.INV_TERM Is Not Null Union All Select IsNull(AR_PAYMENTS.ARP_TERM, AR_INVOICES.INV_TERM) As TERM, AR_PAYMENTS.ARP_PERSON_ID, AR_PAYMENTS.ARP_AR_TYPE, IsNull(AR_PAYMENT_ITEMS.ARPI_AMT, 0) - IsNull(AR_PAYMENT_ITEMS.ARPI_REVERSAL_AMT, 0) As PAYMENT_AMT, 0 As INVOICE_AMT From AR_PAYMENT_ITEMS Inner Join AR_PAYMENTS On AR_PAYMENT_ITEMS.ARPI_PAYMENT = AR_PAYMENTS.AR_PAYMENTS_ID Left Outer Join AR_INVOICE_ITEMS On AR_PAYMENT_ITEMS.ARPI_INVOICE_ITEM = AR_INVOICE_ITEMS.AR_INVOICE_ITEMS_ID Left Outer Join AR_INVOICES On AR_INVOICE_ITEMS.INVI_INVOICE = AR_INVOICES.AR_INVOICES_ID Where IsNull(AR_PAYMENTS.ARP_TERM, AR_INVOICES.INV_TERM) Is Not Null) As a Left Join STUDENT_CURRENT_TYPE_VIEW On a.STUDENT_ID = STUDENT_CURRENT_TYPE_VIEW.STUDENTS_ID Left Join (Select * From STUDENT_ACAD_LEVELS_VIEW Where STUDENT_ACAD_LEVELS_VIEW.STA_ACAD_LEVEL = 'FP') As b On a.STUDENT_ID = b.STA_STUDENT Left Join PREFERRED_MAIL_VIEW On a.STUDENT_ID = PREFERRED_MAIL_VIEW.ID Left Join TERMS On TERMS.TERMS_ID = a.TERM Group By a.TERM, a.STUDENT_ID, a.AR_TYPE, STUDENT_CURRENT_TYPE_VIEW.STUDENT_CURRENT_TYPE, b.STA_CLASS, PREFERRED_MAIL_VIEW.[First Name], PREFERRED_MAIL_VIEW.[Last Name], PREFERRED_MAIL_VIEW.PERSON_STATUS, PREFERRED_MAIL_VIEW.[Address Line 1], PREFERRED_MAIL_VIEW.[Address Line 2], PREFERRED_MAIL_VIEW.[Address Line 3], PREFERRED_MAIL_VIEW.City, PREFERRED_MAIL_VIEW.State, PREFERRED_MAIL_VIEW.Zip, PREFERRED_MAIL_VIEW.Country, PREFERRED_MAIL_VIEW.[Preferred Email], TERMS.TERM_START_DATE Having a.STUDENT_ID Is Not Null Order By a.STUDENT_ID And basically what I need to add is a total balance for all terms for a student prior to the current term. I am doing this in SSRS so I was thinking of creating a parameter of current term start date and calculating the total balance prior to the specified current term start date. So a balance for terms