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.
SELECT
AD_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_id
from curr_attnd_roster atd,enroll_studentdetails1 std,setup_course co,curr_class_date cdate
where std.enroll_studentdetails1_id=atd.enroll_studentdetails1_id and co.setup_course_id=atd.setup_course_id
and cdate.curr_class_date_id=atd.curr_class_date_id and $P{Course_ID}=atd.setup_course_id
And my preview should be displayed as shown below,
Rollno StudentName 30/05/18 01/06/18 02/06/18 03/06/18 Presents Absents Persentage
1122 Ram P A P P
1123 Shyam P P P P
1124 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.
