sumithnangunuri Posted July 5, 2018 Share Posted July 5, 2018 hi, I have an requirement where my report should be generate the total attendance of the class by selecting start date and end date.I have created the report where till now it was working fine to generate my report.But,I had got strucked in performing the calculations for total number of presents,absents and over all persentage.My query is as shown below.SELECTAD_COLUMN_IDENTIFIER('enroll_studentdetails1',atd.enroll_studentdetails1_id,'en_US') AS Studentname,AD_COLUMN_IDENTIFIER('hr_employee',atd.hr_employee_id,'en_US') AS Employee,AD_COLUMN_IDENTIFIER('setup_course',atd.setup_course_id,'en_US') AS course,AD_COLUMN_IDENTIFIER('curr_class_date',atd.curr_class_date_id,'en_US') AS classDateID,atd.curr_attnd_roster_name,atd.curr_attnd_roster_attendance, CASE WHEN atd.curr_attnd_roster_attendance = 'Present' THEN 'P' WHEN atd.curr_attnd_roster_attendance = 'Absent' THEN 'A' ELSE 'unknown' END AS Attendance,atd.curr_attnd_roster_reason,CAST(atd.curr_attnd_roster_classdate as DATE) as atdclassdate,CAST(atd.curr_attnd_roster_starttime as DATE) as starttime,std.enroll_rollno,std.enroll_first_name,CAST(cdate.curr_classdate as DATE) as classdate,atd.setup_course_idfrom curr_attnd_roster atd,enroll_studentdetails1 std,setup_course co,curr_class_date cdatewhere std.enroll_studentdetails1_id=atd.enroll_studentdetails1_id and co.setup_course_id=atd.setup_course_idand cdate.curr_class_date_id=atd.curr_class_date_id and $P{Course_ID}=atd.setup_course_idAnd my preview should be displayed as shown below,Rollno StudentName 30/05/18 01/06/18 02/06/18 03/06/18 Presents Absents Persentage1122 Ram P A P P 1123 Shyam P P P P1124 Srinu A A A P....................................(NOTE:Dates are displayed by selecting classdate from my query.)I have created the report till the above data as shown,I am unable to perform the calculation for presnt,absent and persentage.I had created the report using crosstab.I will be very glad to you if you will be helpfull to get resolve my issue.Thank's in advance. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now