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: