Why Choose eduTrac ERP: Custom Reports

Another very important feature of a college management system is reporting. The good news is that a majority of college management systems include reports and statistics. The bad news is that with most of them, that’s all you have. However, higher ed institutions need more flexibility than that.

With eduTrac ERP, you can generate advanced custom reports. Granted, you have to learn Structured Query Language (SQL) but learning SQL is very easy. Furthermore, knowing SQL will give you a great advantage in the education realm. More and more colleges are looking for someone who has some knowledge of SQL.

You can generate custom reports by using eduTrac ERP’s SQL Interface. It is an interface that interacts with your database, so that you can extract your data without limitations. For example, most colleges need to run a student balance report. One may generate such a report in order to see who is eligible to register. Below is a sample query one would use in order to generate a student balance report.

SELECT bill.termCode AS Term,bill.stuID AS 'Student ID',
CONCAT(person.lname, ', ', person.fname) AS 'Student Name',
CourseFees AS 'Course Fees',
StuFees AS 'Other Fees',
COALESCE(SUM(CourseFees),0)+COALESCE(SUM(StuFees),0) AS 'Beginning Balance',
Payments,
COALESCE(SUM(CourseFees),0)+COALESCE(SUM(StuFees),0)+COALESCE(SUM(Payments),0) AS 'Ending Balance',
bill.comment AS Note,
LastPaid.LastPayment AS 'Last Payment',
LastPaid.PaymentDate AS 'Payment Date'
FROM bill LEFT JOIN
(SELECT COALESCE(SUM(y.courseFee+y.labFee+y.materialFee),0)*-1 AS 'CourseFees',x.stuID,x.termCode
 FROM stu_course_sec x LEFT JOIN course_sec y ON x.termCode = y.termCode AND x.courseSecCode = y.courseSecCode
 GROUP BY x.stuID,x.termCode) stu_course_sec 
 ON bill.stuID = stu_course_sec.stuID AND bill.termCode = stu_course_sec.termCode
 LEFT JOIN
 (SELECT COALESCE(SUM(b.amount),0)*-1 AS 'StuFees',a.stuID,a.termCode
 FROM student_fee a
 LEFT JOIN billing_table b ON a.feeID = b.ID
 GROUP BY a.stuID, a.termCode) student_fee
 ON bill.stuID = student_fee.stuID AND bill.termCode = student_fee.termCode
 LEFT JOIN
 (SELECT COALESCE(SUM(amount),0) As Payments,stuID,termCode,amount,dateTime
 FROM payment 
 GROUP BY stuID,termCode) payment 
 ON bill.stuID = payment.stuID AND bill.termCode = payment.termCode
 LEFT JOIN person ON bill.stuID = person.personID
 LEFT JOIN
 (SELECT amount AS LastPayment,stuID,termCode,dateTime AS PaymentDate
 FROM payment AS LastPaid
 WHERE dateTime = ANY (SELECT MAX(dateTime) FROM payment GROUP BY stuID,termCode)
 GROUP BY stuID,termCode) LastPaid 
 ON bill.stuID = LastPaid.stuID AND bill.termCode = LastPaid.termCode
GROUP BY bill.stuID,bill.termCode
HAVING COALESCE(SUM(CourseFees),0)+COALESCE(SUM(StuFees),0)+COALESCE(SUM(Payments),0) < '0'

The above query will generate a beautifully detailed report as seen in the screenshot below:

ERP-Stu-Account-Balance